arraysexcelvbamultidimensional-arrayvariant

Storing Array vs. Single Value Comparison


Original code stores A6:A50 of dynamic values into an array and when they change compares their differences and prints their differences diff to Sheet2 along with some other values pulled from the Sheet1 (Dashboard). It works great when monitoring the recalculation of A6:A50 and storing that column as a 2D variant array.

Goal is to monitor a single cell F3 on Sheet 1 (Dashboard) instead of A6:A50. Excel crashes now with my edited code.

Code in Sheet1 (Dashboard)

Private Sub ToggleButton1_Click()

End Sub

    Private Sub Worksheet_Calculate()
    
        Dim keyCells As Range
        Dim i As Long
        Dim diff As Range
        Dim cKey As Range 
        Dim ValueArray As Variant
    
        If Worksheets("Dashboard").ToggleButton1.Value = True Then
            On Error GoTo SafeExit
                Application.Calculation = xlCalculationManual: Application.EnableEvents = False: Application.ScreenUpdating = False
    
            Set keyCells = Me.Range("F3").Value
                For i = 1 To UBound(myArr)
                    Set cKey = keyCells(i, 1)
    
                    If cKey.Value <> myArr(i, 1) Then
                        diff = (cKey.Value - myArr(i, 1))
                        NextRow = Sheet1.Cells(Sheet1.Rows.Count, "A").End(xlUp).Row + 1
    
                        ValueArray = Array(Me.Cells(i + 2, "A").Value, Me.Cells(i + 2, "B").Value, diff, Me.Cells(i + 2, "C").Value, _
                                Me.Cells(i + 2, "D").Value, Me.Cells(i + 2, "E").Value, Me.Cells(i + 5, "F").Value, _
                                Me.Cells(i + 2, "G").Value)
    
                        With Sheet2.Cells(NextRow, "A").Resize(, UBound(ValueArray) + 1)
                            .Value = ValueArray
                        End With
                        NextRow = NextRow + 1
                    End If
                Next i
    End If
    SafeExit:
        Application.Calculation = xlCalculationAutomatic: Application.EnableEvents = True: Application.ScreenUpdating = True
    
        Call PopulateBA: 
    End Sub

Code in ThisWorkbook

Private Sub Workbook_Open()
PopulateBA
End Sub

Code in Module1

Public myArr()
Public Sub PopulateBA()
    Dim myArr As Variant
    myArr = Sheet1.Range("F3").Value
End Sub

Solution

  • If you assign single value to a Variant array you should ReDim it first.

    Public myArr() 
    Public Sub PopulateBA()
        ReDim myArr(1 To 1, 1 To 1)
        myArr(1, 1) = Sheet1.Range("F3").Value 
    End Sub
    

    And you shouldn't use Value when you assign to object variable:

    Set keyCells = Me.Range("F3")