I have a long macro that converts data to a format that I would like the data to be in. The macro performs as expected, but as soon as I save the code as an add-inn, the macro throws an error and terminates at the:
EnableEvents = False statement with this error message: Compile error: Variable not defined
if I remove this line, there is a requirement to declare the array
Compile error: Expected array
If I remove the EnableEventsline and declare LastRow and declare the array with Dim inarr() As Single or Dim inarr() As Double
the macro runs, but the data is all wiped out and there is no output
Here is an extract with the impacted code:
Note, that when I run the code as a macro, I don't declare Lastrow or inarr
I have never encountered code that would run in a macro but not when in a add-inn, am I missing something?
I have checked all my security settings, and everything is set as open as possible.
Any help would be appreciated.
EnableEvents = False statement with this error message: Compile error: Variable not defined
if I remove this line, there is a requirement to declare the array
Compile error: Expected array
If I remove the EnableEventsline and declare LastRow and declare the array with Dim inarr() As Single or Dim inarr() As Double
the macro runs, but the data is all wiped out and there is no output
Here is an extract with the impacted code:
VBA Code:
Sub FPO4STEP1(control As IRibbonControl)
Dim lr As Long, lc As Long, i As Long, j As Long
Dim a As Variant, b As Variant
Dim rng As Range
Dim Lrow As Long
Dim dic As Object
Application.ScreenUpdating = False
EnableEvents = False
Application.Calculation = xlCalculationManual
On Error Resume Next
Lastrow = Cells(Rows.Count, "B").End(xlUp).Row
inarr = Range(Cells(1, 2), Cells(Lastrow, 3))
For i = 1 To Lastrow
For j = 1 To 2
inarr(i, j) = Replace(inarr(i, j), Chr(160), "")
inarr(i, j) = Replace(inarr(i, j), Chr(32), "")
If Right(inarr(i, j), 1) = "-" Then
inarr(i, j) = "-" & Left(inarr(i, j), Len(inarr(i, j)) - 1)
End If
Next j
Next i
Range(Cells(1, 2), Cells(Lastrow, 3)) = inarr
Range("A1").Select
Application.Calculation = xlCalculationAutomatic
EnableEvents = True
Application.ScreenUpdating = True
End Sub
Note, that when I run the code as a macro, I don't declare Lastrow or inarr
I have never encountered code that would run in a macro but not when in a add-inn, am I missing something?
I have checked all my security settings, and everything is set as open as possible.
Any help would be appreciated.
Last edited: