I successfully brought my main function which contains more than 2500 lines of codes into the VBA Compiler. It was able to run eventually and produce similar results compared to what the original VBA codes did. Yay!!!
However, there is an issue - code performance. The compiled code is really sluggish, and it fails with a bit larger data set. Note that the same inputs works fast and perfectly with my original VBA code.
I suspect the culprit for the poor performance of the compiled code could be the DYNAMIC ARRAY, then I conducted a simple test.
The test results show that one of the culprit for the poor performance is indeed the DYNAMIC ARRAY. Not sure if there is any other one or not, but this one is really bad. See the results and codes below.
What do I need to do to improve the Dynamic ARRAY’s performance for use in the VBA compiler?
RESULTS
Array size Running time in seconds
Original VBA Compiled VBA
100 0.109 1.68
200 0.375 15.66
300 1.167 54.26
400 2.77 FAILED
500 5.69 FAILED
1000 FAILED FAILED
'================== Original VBA Code======================
Sub TEST_VBA()
Dim Array_Test() As Double
Dim n As Long
Dim i, j, k As Long
Dim StartTime As Double
Dim ElapsedTime As Double
n = InputBox(“Enter size of three dimentional array”)
StartTime = Timer()
ReDim Array_Test(n, n, n)
i = 1
Do Until i = n
j = 1
Do Until j = n
k = 1
Do Until k = n
Array_Test(i, j, k) = 123.456
k = k + 1
Loop
j = j + 1
Loop
i = i + 1
Loop
MsgBox (“This code ran successfully in " & (Timer() - StartTime) & " seconds”)
Erase Array_Test
End Sub
’================== Original VBA Code======================
'=================== Compiled VBA ==============================================
Sub TEST_XP(Param1)
Dim Array_Test As Double
Dim n As Long
Dim i, j, k As Long
Dim StartTime As Double
Dim ElapsedTime As Double
n = Application.InputBox("Enter size of three dimentional array")
StartTime = Timer()
Array_Test = VarArrayCreate([1,n, 1,n, 1,n],12)
i = 1
Do Until i = n
j = 1
Do Until j = n
k = 1
Do Until k = n
Array_Test[i, j, k] = 123.456
k = k + 1
Loop
j = j + 1
Loop
i = i + 1
Loop
MsgBox ("This code ran successfully in " & (Timer() - StartTime) & " seconds")
End Sub
’=================== Compiled VBA ==============================================