VBA - Speeding up Excel instructions sent by Access

4
votes

To build a report, I compute some data with Access, then use a VBA macro to export several tables, then this macro launches Excel and runs another macro from within Excel to compile everything.

Everything is quite fast already, but the necessity to have an Excel Worksheet with VBA code in it in addition to my Access VBA bothers me. All the more I recently added a lot of new functions to my database, with the option of producing several reports, with a lot of customisation available on Access Forms.

It is getting somewhat complicated for me to navigate between these two files, so I tried to put my Excel code in Access, so my colleagues (who are the end users) would only have to keep 1 file up to date on their computer, and it would make things easier to debug too.

The report was built as expected, but the process was 5 times slower. I tried measuring time taken by each step, and the ratio remains the same (except for the part when a PowerPoint presentation gets built, at the end).

So I decided to try this useless piece of code :

Sub test()

Dim t As Double 'Starting time
t = Round(Timer)

Dim b As Workbook
Dim s As Worksheet
Dim i As Integer, j As Integer 'Loop variables


'Create workbook
Set b = Workbooks.Add
'Get worksheet
Set s = b.Sheets(1)

'Double Loop
For i = 1 To 100
    For j = 1 To 100
        s.Cells(i, j) = "Hi!" 'Write some useless comment
    Next
Next

b.Close False 'Close without saving

'Message
MsgBox (Round(Timer) - t) \ 60 & "'" & Format((Round(Timer) - t) Mod 60, "00") & "''"

End Sub

I didn't put any ScreenUpdating=False, EnableEvents=False, etc. on purpose.

When running the code from Excel, it takes 2 to 3 seconds.

When running the code from Access, it takes from 7 to 10 seconds!

I understand that Access has to send instructions to another application, which may slow the process. So I tried asking Excel to run my script in another instance of Excel (so I created an object "Excel.Application" in which I put my workbook, exactly what Access does in the background), and I got the same performance as if I ran the code from Access.

Is there any way to speed this up ? An instruction to change the way the VB script communicates with the application object it creates maybe ?

Thank you all in advance for your support.

Edit: As requested by ASH, here is the full code I run in Access (I wont put my report code, as it is quite huge with a lot of subs, functions, custom classes, etc.). Comments are in french, but here is a summary:

  • The first script is a function that creates or opens an Excel Workbook, with a few optimisations (Excel invisible, no screen updating, no calculation, etc.)

  • The second script is a sub that closes an Excel Workbook, with options of saving/saving as, and discarding optimisations from the first function

  • The third script is analternative version of the test code I put before.

Now for the code itself:

Option Explicit

Function Ouvrir_Classeur_Excel(Optional Fichier As String, Optional Optimiser As Boolean = False) As Workbook

'*********************************** Descriptif
'Ouvre (ou crée) un classeur Excel en appliquant une optimisation si demandé par l'utilisateur.
'L'optimisation cache et désactive le rafraîchissement visuel d'Excel, les messages d'alerte, les événements et les calculs automatiques.

'------------------ Paramètres
'Fichier : Le chemin du classeur à ouvrir. S'il est vide, un classeur est créé
'Optimiser : Indique si les scripts d'optimisation doivent être exécutés. Par défaut, cette option est désactivée



'*********************************** Exécution
With Excel.Application


    If Optimiser Then
        'Excel invisible
        .Visible = False
        'Désactivation du rafraîchissement
        .ScreenUpdating = False
        'Désactivation des messages d'alerte
        .DisplayAlerts = False
        'Désactivation des événements
        .EnableEvents = False
    End If

    'Ouverture/Création du classeur
    If Fichier <> "" Then Set Ouvrir_Classeur_Excel = Workbooks.Open(Fichier) 'Si on a spécifié un fichier, il est ouvert
    If Fichier = "" Then Set Ouvrir_Classeur_Excel = Workbooks.Add 'Si on n'a pas spécifié de fichier, on en crée un

    'Désactivation des calculs automatiques
    If Optimiser Then .Calculation = xlCalculationManual

End With

End Function




Sub Fermer_Classeur_Excel(Classeur As Workbook, Optional Enregistrer As Boolean = False, _
    Optional Emplacement As String, Optional Fin_Optimisation As Boolean = False)

'*********************************** Descriptif
'Ferme le classeur Excel spécifié.
'Si l'utilisateur le demande, le classeur peut être enregistré, ou enregistré sous.
'Si l'utilisateur le demande, les optimisations appliquées par la fonction Ouvrir_Classeur_Excel peuvent être annulées.

'------------------ Paramètres
'Classeur : Le classeur à fermer
'Enregistrer : Indique si le classeur doit être enregistré. Par défaut, cette option est désactivée
'Emplacement : Indique l'emplacement où enregistrer le classeur. Si vide, l'enregistrement sera simple.
'Fin_Optimisation : Indique si les optimisations doivent être annulées. Par défaut, cette option est désactivée



'*********************************** Exécution
With Excel.Application

    'Enregistrement du classeur
    If Enregistrer Then
        If Emplacement = "" Then Classeur.Save
        If Emplacement <> "" Then Classeur.SaveAs Emplacement
    End If

    'Réactivation des calculs automatiques
    If Fin_Optimisation Then .Calculation = xlCalculationAutomatic

    'Fermeture du classeur
    Classeur.Close False


    If Fin_Optimisation Then
        'Réactivation du rafraîchissement
        .ScreenUpdating = True
        'Réactivation des messages d'alerte
        .DisplayAlerts = True
        'Réactivation des événements
        .EnableEvents = False
    End If

End With

End Sub




Sub testA()

'------------Relève de l'heure de début
Dim tGlo As Double 'Heure de début d'exécution du script
Dim infoFin As String
tGlo = Round(Timer)

Dim x As Excel.Application
Dim c As Workbook
Dim f As Worksheet
Dim i As Integer, j As Integer

Set c = Ouvrir_Classeur_Excel(, True)

Set f = c.Sheets(1)
i = 1
Do Until i = 100
    j = 1
    Do Until j = 100
        f.Cells(i, j) = "Coucou"
        j = j + 1
    Loop
    i = i + 1
Loop

Fermer_Classeur_Excel c, , , True


'------------Message de fin
infoFin = infoFin & Chr(10) & Chr(10) & "DUREE DE TRAITEMENT : " & (Round(Timer) - tGlo) \ 60 & "'" & Format((Round(Timer) - tGlo) Mod 60, "00") & "''"
MsgBox infoFin, , title:="** FIN DU TRAITEMENT **"


End Sub

Like this, it takes 2 to 7 seconds (with Excel already running), which is huge compared to less than 1 in Excel with the same optimisations.

The issue being that it does not seem that important here, but another process that usually takes 40 seconds in Excel, took more than 3 minutes when I migrated it to Access. I have another one (not yet optimised) that takes more that 3:30 in Excel, I don't even dare trying to run it in Access.

4
excelms-accessvba
Many things could speed up the operation. You need to try them. i.e. make the excel application not visible, disable events etc. also, those additional delays may be due to some setup-time in the beginning, etc. I suggest you post some code you ran from Access to see how it could be tweaked. – A.S.H
You may also be able to seek help over at Code Review. That site emphasizes on slimming down code and improving performance on code that already works. – PartyHatPanda
I did not put any Application.visible, etc. on purpose in my message to make the code the shortest possible. Here are my optimisations on Access : - Excel is not visible (the instruction is not mandatory, but I put it anyway) - ScreenUpdating disabled - Alerts disabled - Events disabled - Calculation disabled In Excel, I do the same. The ratio remains very high (3 to 6 times slower). Setup time would only delay the code execution for a few seconds when launching Excel, it would not justify that a process that takes 40 seconds in Excel would take more than 3 minutes with Access. – Bertrand Warot
Thank you both for your support, I will dig in further, and have a look at Code Review :-) Plus I added my optimisation code in my first post (but not my full report building script, which is too long). – Bertrand Warot

4 Answers

0
votes

I know you mentioned you have a lot of custom procedures within Access to produce various reports, but is there any reason you can't move that code to Excel? I have several solutions I have built that run code in Excel but use Access as the data source and they are very fast- the userforms you built in Access to customize the reports would have to be rebuilt in Excel but could solve your performance issues between Excel/Access...

Hope this helps, TheSilkCode

0
votes

The "bottle neck" is running for 100*100 times [s.Cells(i,j) = "Hi!"] 

For i = 1 To 100
    For j = 1 To 100
        s.Cells(i, j) = "Hi!" 'Write some useless comment
    Next
Next

To solve the issue, try run the "bottle-neck" only once as [Rg.Value = D2]

Dim Ws as Worksheet: Set Ws = Sheets(1)
Dim C1 as Range:     Set C1 = Ws.Cells(2,2)     ' Cell1 - The top-left-corner
Dim C2 as Range:     Set C2 = Ws.Cells(101,202) ' Cell2 - The bottom-right-corner
Dim Rg as Range:     Set Rg = Ws.Range(C1,C2)   ' Rg - the Rectangle Range At-Top-Left-Cell(2,2) of 100 Rows and 200 columns
Dim D2(): Redim D2(1-100,1-200)                 ' D2 - The 2-dimension array of 100 rows and 200 columns
For R%=1 to 100
    For C%=1 to 200
        D2(R,C)=R*100+C
    Next
Next
Rg.Value = D2                                   ' Run the "Bottle neck" only once.
0
votes

I do not know if it is still open, but I've encountered similar issue.

First time, when I faced such problem was during automation of Excel through C# using VSTO. VSTO had huge issues in reading and writting from cells in worksheet, e. g. I was able to do the same operation via VBA like 40 times faster. Then I figured out, that it is much faster to read and write Excel cells using arrays (Warning - in case of C# I had issues with out of memory using such conception and I had to divide one worksheet using arrays like 100k rows and 40 columns).

The idea is to: - in case of reading, copy all interesting cells to array and then loop through array, - in case of writting, create array and copy values to array and then copy array to proper cells in worksheet.

In VBA to read cells via array You can do sth like this:

Dim arr As Variant 'declare array
Dim LC As Long 'last column
Dim LR As Long 'last Row
'ws is worksheet variable


LC = ws.UsedRange.Columns(ws.UsedRange.Columns.Count).Column
LR = ws.UsedRange.Rows(ws.UsedRange.Rows.Count).Row
arr = (Range(ws.Cells(1,1), ws.Cells(LR, LC)).Value) 'copy cells from A1 to last column and last row

Dim r As Long 'r - is var for row
Dim c As Long 'c - is var for column

For r = 1 To LR
    For c = 1 To LC
      ' do sth with value from cells
      MsgBox(Cstr(arr(r,c))) 'eg. I display value of cell in MsgBox
    Next c
Next i
0
votes

When you working with Recordset you can use

.Range("A2").CopyFromRecordset rs

https://docs.microsoft.com/ru-ru/office/vba/api/excel.range.copyfromrecordset