MS Excel 2010 - Calculation using Abs() stops working when placed in other VBA

Question

I’ve written a macro that compares this month’s KPI figures with the equivalent figures from last month and then adds a symbol next to each figure to show whether performance is better, worse or the same. Performance is better if the number is closer to 100% and worse if it is further away. The desired result is something like this:

[Last month's figure, This month's figure, Desired symbol, Notes]

Example 1 - [98,99,↑,This month’s figure is closer to 100 so performance improved]

Example 2 - [101,102,↓,Further from 100 so performance is worse]

Example 3 - [98,98,=,The figures are the same so there is no change in performance]

Example 4 - [98,102,±,Performance is no better or worse but last month it was under target and this month it is over target or vice versa if the figures were 102,98]

When the following block of code is run on it's own it works fine:

Sub Test231214()

        Range("A1").Select
        checkCell = Selection.Value
        Range("B1").Select
        newCell = Selection.Value
        'Check whether the active cell is less than, equal to or greater than the corresponding value from last month
        If newCell = checkCell Then
            'Select the cell to the right of the current selection
            Selection.Offset(0, 1).Select
            Selection.Value = "'="
        ElseIf Abs(100 - newCell) < Abs(100 - checkCell) Then
           'Select the cell to the right of the current selection
           Selection.Offset(0, 1).Select
           ActiveCell.Value = ChrW(&H2191)
        ElseIf Abs(100 - newCell) > Abs(100 - checkCell) Then
           'Select the cell to the right of the current selection
           Selection.Offset(0, 1).Select
           ActiveCell.Value = ChrW(&H2193)
        ElseIf Abs(100 - newCell) = Abs(100 - checkCell) Then
           'Select the cell to the right of the current selection
           Selection.Offset(0, 1).Select
           ActiveCell.Value = "±"
        End If

End Sub

but it doesn't when the same code is used as part of the a larger macro:

Sub Populate_KPI_Arrows()
'
' Populate_KPI_Arrows Macro
' Opens dialogue box to select last month's KPI file, compares values and inserts arrows as appropriate.

'
'IF AN ERROR IS GENERATED AT ANY POINT DURING THE EXECUTION OF THIS MACRO THEN GO TO THE ERROR HANDLING CLAUSE
'NB: Disabled for now to make sure it is executing correctly
'On Error GoTo ErrorHandler

'SECTION 1 - CREATE NECESSARY VARIABLES AND SET VALUES

'CREATE VARIABLES FOR THE WORKBOOKS AND SHEETS TO BE COMPARED
Dim b1 As Workbook, b2 As Workbook, b3 As Workbook, w2 As Worksheet, w4 As Worksheet, w6 As Worksheet
'CREATE VARIABLE FOR THE PATH OF b1
Dim strFile As String
'CREATE VARIABLES FOR THE ARRAY OF COLUMNS TO EXAMINE AND THE INDEX OF THE CURRENT ARRAY ITEM
Dim hoursArray As Variant
Dim x As Integer

'SET ARRAYS OF COLUMNS TO EXAMINE
hoursArray = Array("B")

'TURN OFF SCREEN UPDATING TO PREVENT WORKINGS BEING DISPLAYED
Application.ScreenUpdating = False

'SET b1 AS THE WORKBOOK THIS MACRO WAS RUN FROM, strFile AS THE WORKBOOKS FILE PATH w1 AS 'Schemes KPIs' TAB & w2 AS 'Villages KPIs' TAB
Set b1 = ActiveWorkbook
strFile = ActiveWorkbook.FullName
Set w2 = ActiveWorkbook.Sheets("Villages KPIs")

'TEMPORARILY CLOSE THIS MONTHS WORKBOOK
Application.DisplayAlerts = False
b1.Close
Application.DisplayAlerts = True

'OPEN A DIALOG BOX TO SELECT LAST MONTHS FILE
With Application.FileDialog(msoFileDialogFilePicker)
    .AllowMultiSelect = False
    .Title = "Select last month's Combined KPI's file"
    .InitialFileName = "C:\"
    'IF A FILE IS SELECTED THEN OPEN IT
    If .Show = -1 Then
        pubInputFile = .SelectedItems(1)
        txtFile = pubInputFile
        Workbooks.Open (txtFile)
        'SET b2 AS SELECTED FILE, w4 AS 'Villages KPIs' TAB
        Set b2 = ActiveWorkbook
        Set w4 = ActiveWorkbook.Sheets("Villages KPIs")
    'ELSE THE USER PRESSED CANCEL SO EXIT MACRO
    Else
        Exit Sub
    End If
End With

'UNPROTECT 'Villages KPIs' TAB OF LAST MONTHS WORKBOOK
w4.Activate
ActiveSheet.Unprotect Password:="password"

'COPY LAST MONTHS DATA TO A NEW TEMPORARY WORKBOOK, SET NEW WORKBOOK AS b3
w4.Activate
Cells.Select
Selection.Copy
Workbooks.Add
Set b3 = ActiveWorkbook
'SET w6 TO THE SHEET WITH THE DATA FROM w4
w4.Activate
Cells.Select
Selection.Copy
b3.Activate
Sheets.Add After:=Sheets(Sheets.Count)
Set w6 = ActiveSheet
ActiveSheet.Paste

'CLOSE LAST MONTHS WORKBOOK
Application.DisplayAlerts = False
b2.Close SaveChanges:=False
Application.DisplayAlerts = True

'REOPEN THIS MONTHS WORKBOOK
 If InStr(strFile, "\") = 0 Then
     Exit Sub
 End If
 Workbooks.Open Filename:=strFile

'RESET b1, w2 TO THE VALUES THAT THEY WERE BEFORE
Set b1 = ActiveWorkbook
Set w2 = ActiveWorkbook.Sheets("Villages KPIs")
'UNPROTECT 'Schemes KPIs' & 'Villages KPIs' TABS OF THIS MONTHS WORKBOOK
w2.Activate
ActiveSheet.Unprotect Password:="password"

'SECTION 2 - SELECT w2 AND THEN RUN THE FOR LOOP ON EACH COLUMN TO BE EXAMINED

'SELECT COLUMN A OF THE 'Villages KPIs' TAB IN THIS MONTHS WORKBOOK
w2.Activate
Range("A:A").Select

'COUNT THE NUMBER OF LOCATIONS ON THE CURRENT TAB
LastLocation = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row - 15

'LOOP THROUGH ALL ITEMS OF hoursArray
For x = LBound(hoursArray) To UBound(hoursArray)
    'LOOP THROUGH ALL ARROW CELLS FOR CURRENT COLUMN AND INSERT RELEVANT ARROW OR EQUALS SIGN
    For a_counter = 10 To LastLocation + 9
        'SELECT CELL XY WHERE X IS THE CURRENT ARRAY ITEM AND Y IS THE CURRENT VALUE OF a_counter
        w6.Activate
        w6.Range(hoursArray(x) & a_counter).Select
        checkCell = Selection.Value
        w2.Activate
        w2.Range(hoursArray(x) & a_counter).Select
        newCell = Selection.Value
        'Check whether the active cell is less than, equal to or greater than the corresponding value from last month
        If (100 - checkCell) < 0 Then
            checkCell = (checkCell * -1)
        End If
        If (100 - newCell) < 0 Then
            newCell = (newCell * -1)
        End If
        If newCell = checkCell Then
            'Select the cell to the right of the current selection
            Selection.Offset(0, 1).Select
            Selection.Value = "'="
        ElseIf (Abs(100 - newCell)) < (Abs(100 - checkCell)) Then
           'Select the cell to the right of the current selection
           Selection.Offset(0, 1).Select
           ActiveCell.Value = ChrW(&H2191)
        ElseIf (Abs(100 - newCell)) > (Abs(100 - checkCell)) Then
           'Select the cell to the right of the current selection
           Selection.Offset(0, 1).Select
           ActiveCell.Value = ChrW(&H2193)
        ElseIf (Abs(100 - newCell)) = (Abs(100 - checkCell)) Then
           'Select the cell to the right of the current selection
           Selection.Offset(0, 1).Select
           ActiveCell.Value = "±"
        End If
    Next a_counter
    'SELECT CELL XZ WHERE X IS THE CURRENT ARRAY ITEM AND Z IS THE ROW 2 BELOW THE LAST LOCATION
    w6.Activate
    w6.Range(hoursArray(x) & LastLocation + 11).Select
    checkCell = Selection.Value
    w2.Activate
    w2.Range(hoursArray(x) & LastLocation + 11).Select
    newCell = Selection.Value
    'Check whether the active cell is less than, equal to or greater than the corresponding value from last month
    If (100 - checkCell) < 0 Then
        checkCell = (checkCell * -1)
    End If
    If (100 - newCell) < 0 Then
        newCell = (newCell * -1)
    End If
    If newCell = checkCell Then
        'Select the cell to the right of the current selection
        Selection.Offset(0, 1).Select
        Selection.Value = "'="
    ElseIf (Abs(100 - newCell)) < (Abs(100 - checkCell)) Then
        'Select the cell to the right of the current selection
        Selection.Offset(0, 1).Select
        ActiveCell.Value = ChrW(&H2191)
    ElseIf (Abs(100 - newCell)) > (Abs(100 - checkCell)) Then
        'Select the cell to the right of the current selection
        Selection.Offset(0, 1).Select
        ActiveCell.Value = ChrW(&H2193)
    ElseIf (Abs(100 - newCell)) = (Abs(100 - checkCell)) Then
        'Select the cell to the right of the current selection
        Selection.Offset(0, 1).Select
        ActiveCell.Value = "±"
    End If
Next x

'PROTECT 'Villages KPIs' TAB OF THIS MONTHS WORKBOOK
w2.Activate
ActiveSheet.Protect Password:="password", DrawingObjects:=False, Contents:=True, Scenarios:= _
False

'CLOSE TEMPORARY WORKBOOK WITHOUT SAVING
Application.DisplayAlerts = False
b3.Close SaveChanges:=False
Application.DisplayAlerts = True

'TURN SCREEN UPDATING BACK ON SO ARROWS APPEAR
Application.ScreenUpdating = True

Exit Sub

'Error handler
ErrorHandler:
    Resume Next

End Sub

When run as part of the larger macro it is getting the arrows the wrong way round for figures over 100. Any ideas why this is happening or is there a better way of doing it? Any comments on tidying up the code in general would be welcome too.

Additional info: there are other columns in these workbooks where if the number goes up the arrow always points up and a similar block of code that doesn't use Abs() and just compares newCell and checkCell directly works fine for these columns in the larger macro.

Answer 1

Embarrassingly the problem was the "100 -" which should have been "1 -" as the values being compared are stored as decimals formatted as percentages. Thank you for the responses, I'll make sure to observe the 'minimal' aspect of the best practice doc that pnuts shared in future, same point Alex Bell raised so fair play. The guidance on avoiding Select references is also really useful so will take that on board for future too.