Macro generated by maximum of absolute value of cells

Question

Numeric data is streamed into cells B8, B10, B12, B14, B16 and B18 (see below).

Cell B23 is the maximum of the absolute value of the above cells at any time, so the formula in B23 is : =MAX(ABS($B$8),ABS($B$10),ABS($B$12),ABS($B$14),ABS($B$16),ABS($B$18))

Cell B5 is a user-defined constant, in our case 13.00, and is the threshold value that will trigger one of the macros.

So, in the case below, B23 = 8.00, and because 8.00 < 13.00 no macro is called.

If, however, B5 was 7.50, then since B23 (8.00) >= 7.50, and B14 is a positive value, Macro_7 is to be called. Had B14 been -8.00, then Macro_8 is to be called.

This process is to be started when the user presses the START button, which has macro START assigned to it. Once a macro is called, the process ends till the user restarts it.

I am having trouble coding this in VBA and would appreciate any assistance.

Answer 1

Try this

Sub RunMacro()
    Dim rng As Range
    Dim dThreshold As Double
    Dim i As Long
    Dim dValue As Double
    Dim dRunningMin As Double: dRunningMin = 1E+20
    Dim lIndex As Long
    
    ' Change the sheet name
    With ThisWorkbook.Sheets("Sheet2")
        Set rng = .Range("B8:B18")
        dThreshold = .Range("B5")
        lIndex = 0
        
        For i = 1 To rng.Rows.Count Step 2
            dValue = rng.Cells(i, 1).Value
            If Abs(dValue) >= dThreshold Then
                If Abs(dValue) - dThreshold < dRunningMin Then
                    dRunningMin = Abs(dValue) - dThreshold
                    lIndex = i + IIf(dValue < 0, 1, 0)
                End If
            End If
        Next i
        
        If lIndex > 0 Then
            Application.Run "Macro_" & lIndex
        End If
    End With
    
End Sub

The code above will work out the number whose absolute value is greater than the threshold and is nearest to it. e.g.

Threshold  Macro
13         None
7.5        Macro_7
4          Macro_3 (but not Macro_10)
3.1        Macro_6
3          Macro_11
2          Macro_1

If, however, you want to run all macros for numbers whose absolute values are greater than the threshold then you need something like this:

Sub RunMacros()
    Dim rng As Range
    Dim dThreshold As Double
    Dim i As Long
    Dim dValue As Double
    
    ' Change the sheet name
    With ThisWorkbook.Sheets("Sheet2")
        Set rng = .Range("B8:B18")
        dThreshold = .Range("B5")
        
        For i = 1 To rng.Rows.Count Step 2
            dValue = rng.Cells(i, 1).Value
            If Abs(dValue) >= dThreshold Then
                Application.Run "Macro_" & i + IIf(dValue < 0, 1, 0)
            End If
        Next i
    End With
End Sub

e.g.

Threshold  Macro
13         None
7.5        Macro_7
4          Macro_3, Macro_7 and Macro_10
3.1        Macro_3, Macro_6, Macro_7, Macro_10
3          Macro_3, Macro_6, Macro_7, Macro_10, Macro_11
2          Macro_1, Macro_3, Macro_6, Macro_7, Macro_10, Macro_11

Answer 2

Please try this function.

Function AbsoluteMaximum(RowNum As Long, _
                     Sign As Long) As Double

    Dim AbsMax  As Double                   ' variables range
    Dim Tmp     As Double                   ' temporary value
    Dim R       As Long                     ' row number
    Dim i       As Integer                  ' loop counter: iterations
    
    R = RowNum
    RowNum = 0                              ' return 0 in case of failure
    For i = 1 To 6                          ' number of cells
        Tmp = Cells(R, "B").Value
        If Abs(Tmp) > AbsMax Then
            AbsMax = Abs(Tmp)
            Sign = Sgn(Tmp)
            RowNum = R
        End If
        R = R + 2
    Next i
    AbsoluteMaximum = AbsMax
End Function

It returns 3 values: the absolute maximum, the row number where it was found and its Sign. The Sgn() function returns 1 for a positive number, -1 for a negative number and 0 for zero.

This is how you can call the function from VBA.

Sub Test_AbsMax()
    Dim RowNum      As Long
    Dim Sign        As Long
    Dim AbsMax      As Double
    
    RowNum = 8                      ' start row: change to suit
    AbsMax = AbsoluteMaximum(RowNum, Sign)
    MsgBox "Absolute Max = " & AbsMax & vbCr & _
           "Sign = " & Sign & vbCr & _
           "in row number " & RowNum
End Sub

You can use the Sign variable with code like Clm = Iif(Sign < 0, 3, 1), specifying columns A or C to link to a button.

Observe that RowNum is the first row number for your variables when the function is called but changed by the function to become the row number where the maximum was found. Therefore its value is different before and after the function call.

If this number is below the threshold you would call no further macro. Else you would call a macro determined by RowNum and Sign.