Why does excel VBA code runs 2000 times slower on some machines?-CodePudding

I have created an excel VBA macro with does a lot of data intensive processing. I make a lot of use of Collections. On some PCs it runs very fast, and on others it runs very slow. I have isolated the problem in the example code below. Please note that the example code is not an functional program, it is just to isolate the technical problem I encounter in the bigger program.

I have tested the macro on 4 different machines. See below the output with the CPU info, all use “Microsoft® Excel® for Microsoft 365 MSO (Version 2210 Build 16.0.15726.20188) 32-bit”:

Intel(R) Core(TM) i9-10900K CPU @ 3.70GHz, RAM 16.0 GB: Run loop took 0.09 seconds. Clearing memory took 0.04 seconds.

11th Gen Intel(R) Core(TM) i7-1185G7 @ 3.00GHz, RAM 16,0 GB: Run loop took 0,10 seconds. Clearing memory took 0,05 seconds.

Intel(R) Core(TM) i7-7700K CPU @ 4.20GHz, RAM 32,0 GB: Run loop took 162,58 seconds. Clearing memory took 5,48 seconds.

Intel(R) Core(TM) i7-8665U CPU @ 1.90GHz, RAM 16.0 GB: Run loop took 201,03 seconds. Clearing memory took 6,37 seconds.

Code of the VBA procedure:

Option Explicit
    
Sub largeCollection()

    Dim time1 As Single
    Dim time2 As Single
    time1 = Timer


    Dim myCollection As New Collection

    
    Dim I As Long
    Dim aClass1 As Class1
    
    For I = 2 To 50000
        Set aClass1 = New Class1

        aClass1.d1 = I
        aClass1.d2 = I
        aClass1.d3 = I
        aClass1.d4 = I
        aClass1.d5 = I
        aClass1.d6 = I
        aClass1.d7 = I
        aClass1.d8 = I
        aClass1.d9 = I
        aClass1.d10 = I

        aClass1.i1 = I
        aClass1.i2 = I
        aClass1.i3 = I
        aClass1.i4 = I
        aClass1.i5 = I
        aClass1.i6 = I
        aClass1.i7 = I
        aClass1.i8 = I
        aClass1.i9 = I
        aClass1.i10 = I
        
        myCollection.Add aClass1
    Next I
    
    time2 = Timer
    
    Set myCollection = Nothing
    
    'Notify user in seconds
    Debug.Print "Run loop took " & Format((time2 - time1), "0.00") & " seconds. Clearing memory took " & Format((Timer - time2), "0.00") & " seconds."
    
End Sub

Code of the custom class "Class1":

Option Explicit

Public s1 As String
Public s2 As String
Public s3 As String
Public s4 As String
Public s5 As String
Public s6 As String
Public s7 As String
Public s8 As String
Public s9 As String
Public s10 As String
Public s11 As String
Public s12 As String
Public s13 As String
Public s14 As String
Public s15 As String
Public s16 As String
Public s17 As String
Public s18 As String
Public s19 As String
Public s20 As String

Public v1 As Variant
Public v2 As Variant
Public v3 As Variant
Public v4 As Variant
Public v5 As Variant
Public v6 As Variant
Public v7 As Variant
Public v8 As Variant
Public v9 As Variant
Public v10 As Variant

Public i1 As Long
Public i2 As Long
Public i3 As Long
Public i4 As Long
Public i5 As Long
Public i6 As Long
Public i7 As Long
Public i8 As Long
Public i9 As Long
Public i10 As Long

Public d1 As Double
Public d2 As Double
Public d3 As Double
Public d4 As Double
Public d5 As Double
Public d6 As Double
Public d7 As Double
Public d8 As Double
Public d9 As Double
Public d10 As Double

I am running out of options, it would be great if anyone can provide a solution.

CodePudding user response：

We finally have found an answer ourselves, although it is not very comforting... The difference in performance seems to be completely dependent on the trust center macro settings.

If I select the option:

"Disable macros with notification", I have to explicitly "enable content" to enable the macros when I open the workbook. Using this option the macro runs 2000 times slower
"Enable VBA macros (not recommended; potentially dangerous code can run)" the code runs 2000 times faster.

I would expect the 2 options to have the same performance since after I have enabled the content, I have enabled the VBA macros, just as if I would have selected the other option.

This would mean that the only way I can have a performant macro, is to set the option to "Enable VBA macros (not recommended; potentially dangerous code can run)", which would result in all macros for all workbooks being run by default, even if I would open a random excel from a 3rd party, which is a huge security risk.

CodePudding user response：

in Class1 there are a lot of variables, many of them as Variant which requires more memory space. Try to create new collections in the class1 to store the different variables. E.g. Variables v1, v2,…Vn could be a new collection in Class1 with objects from “ClassV”. Also, you are looping from i=2 to i=50000. Before the Next i, you can set set Class1 = Nothing to free memory.