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
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")