2
\$\begingroup\$

I have a very hard time figuring out how to proceed. I built an Access DataBase which I use to process data in order to build several weekly and monthly reports. I wrote VBA codes in Excel and in Access.

Basically, for now, Access VBA processes data, exports tables on temporary workbooks then runs macros on Excel to compile everything and make nice presentations.

Having to carry over two files (one Access, one Excel, each with its own VBA codes) does not bother me at all, but the end users being my colleagues, and them having to be able to debug those when I am not here, I am starting to consider merging my codes and controlling Excel directly from Access VBA. But when I tried to do so, everything became really slow!

Just so you get what I mean. Here is some useless code to illustrate what happens (the real code being too long).

First, the version in which Access VBA tells Excel to run VBA.

Access VBA:

Option Explicit
Sub SpeedTest_Access_And_Excel()

'Starting time
Dim startTime As Double
startTime = Round(timer)


'Variables
Dim xl As Excel.Application 'Excel
Dim xlBot As Workbook 'The workbook with VBA code to run


'Run Excel
Set xl = New Excel.Application


With xl
    Set xlBot = xl.Workbooks.Open(CurrentProject.Path & "\SpeedTest.xlsm") 'Open Workbook
    .Run "SpeedTest_In_Excel" 'Run Excel code
End With

xlBot.Close False 'Close Excel workbook



'End message
MsgBox (Round(timer) - startTime) \ 60 & "'" & Format((Round(timer) - startTime) Mod 60, "00") & "''"

'Quit Excel
xl.Quit
Set xl = Nothing

End Sub

Excel VBA (in a "SpeedTest.xlsm" file in the same folder as the Access DB):

Option Explicit
Sub SpeedTest_In_Excel()

    'Variables
    Dim wb As Workbook 'A new workbook
    Dim ws As Worksheet 'That new workbook's sheet
    Dim i As Integer, j As Integer 'Lines and columns

    'Excel optimisation
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .EnableEvents = False
    End With

    'Create new workbook
    Set wb = Workbooks.Add

    'Disable auto-calculation
    Application.Calculation = xlCalculationManual

    'Write useless stuff
    Set ws = wb.Sheets(1)
    i = 1
    Do Until i = 500
        j = 1
        Do Until j = 500
            ws.Cells(i, j) = "Useless stuff"
            j = j + 1
        Loop
        i = i + 1
    Loop

    'Close workbook
    wb.Close False

End Sub

Now the Access only version, that does exactly the same thing:

Sub SpeedTest_Access_Only()

'Starting time
Dim startTime As Double
startTime = Round(timer)


'Variables
Dim xl As Excel.Application
Dim wb As Workbook 'A new workbook
Dim ws As Worksheet 'That new workbook's sheet
Dim i As Integer, j As Integer 'Lines and columns

'Run Excel
Set xl = New Excel.Application



With xl
    'Excel optimisation
    .ScreenUpdating = False
    .DisplayAlerts = False
    .EnableEvents = False

    'Create New Workbook
    Set wb = .Workbooks.Add

    'Disable auto-calculation
    .Calculation = xlCalculationManual

End With



'Write useless stuff
Set ws = wb.Sheets(1)
i = 1
Do Until i = 500
    j = 1
    Do Until j = 500
        ws.Cells(i, j) = "Useless stuff"
        j = j + 1
    Loop
    i = i + 1
Loop


'Close workbook
wb.Close False


'End message
MsgBox (Round(timer) - startTime) \ 60 & "'" & Format((Round(timer) - startTime) Mod 60, "00") & "''"

'Quit Excel
xl.Quit
Set xl = Nothing

End Sub

My issue :

  • The 1st version (Access + Excel) takes 0 minutes and 8 seconds
  • The 2nd version (Access only) takes 1 minute and 25 seconds at best (up to 2 minutes and 20 seconds if I do something else at the same time)

Does anybody know anything I could do to speed this up? Is that even possible? Or should I keep my Access+Excel configuration that works quite well beside being a little more complex (and not enabling me to use class variables)?

\$\endgroup\$
  • 2
    \$\begingroup\$ The Excel code already has an Excel.Application instance open when it runs, so you won't get accurate benchmark comparisons if Set xl = New Excel.Application is inside the timed code. That said, since this isn't real code, this question is probably better suited for SO. \$\endgroup\$ – Comintern Jan 3 '17 at 14:57
  • 1
    \$\begingroup\$ FWIW you're crossing process boundaries and using Interop. This is basically always slower than triggering an in-process macro. If you want to optimize for speed, you want to either use your first version or do something completely different to visualize your results. \$\endgroup\$ – Vogel612 Jan 3 '17 at 15:06
  • \$\begingroup\$ @Comintern - It is in both versions actually. \$\endgroup\$ – Bertrand Warot Jan 3 '17 at 15:12
  • \$\begingroup\$ My mistake - I was only looking at the code labeled "Excel VBA". \$\endgroup\$ – Comintern Jan 3 '17 at 15:16
  • \$\begingroup\$ @Vogel612 - I guess you are right. I will stop bothering myself with this issue. Thanks a lot to you both! \$\endgroup\$ – Bertrand Warot Jan 3 '17 at 15:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.