Macros run quickly, until different macro is run

Question

Cross-posted here:

https://www.reddit.com/r/excel/comments/ea4zb1/macros_run_quickly_until_different_macro_is_run/

I have a macro, that upon initial opening of excel runs fairly quickly. and I can run it multiple times, or run different macros (except one in particular) with no consequences on performance. I also have a macro that prints the file to pdf. After I run this macro, performance of all other macros suffer. The culprit code is posted below, are there any things that it's doing that's causing other macros to run slower? Thanks

Private Sub Save_Workbook_As_PDF2()



Application.EnableEvents = False

Application.ScreenUpdating = False



Dim sPrinter As String

Dim sDefaultPrinter As String

'Debug.Print "Default printer: ", Application.ActivePrinter

sDefaultPrinter = Application.ActivePrinter ' store default printer

sPrinter = GetPrinterFullName("Adobe PDF")

If sPrinter = vbNullString Then ' no match

Debug.Print "No match"

Else

Application.ActivePrinter = sPrinter

'Debug.Print "Temp printer: ", Application.ActivePrinter

' do something with the temp printer

Sheets(Array("Quote Sheet", "Terms and Conditions")).Select

ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"

Sheets("Quote Sheet").Select

Application.ActivePrinter = sDefaultPrinter

End If

'Debug.Print "Default printer: ", Application.ActivePrinter

Application.EnableEvents = True

Application.ScreenUpdating = True

End Sub



Private Function GetPrinterFullName(Printer As String) As String

' This function returns the full name of the first printerdevice that matches Printer.

' Full name is like "PDFCreator on Ne01:" for a English Windows and like

' "PDFCreator sur Ne01:" for French.

' Created: Frans Bus, 2015. See http://pixcels.nl/set-activeprinter-excel

' see http://blogs.msdn.com/b/alejacma/archive/2008/04/11/how-to-read-a-registry-key-and-its-values.aspx

' see http://www.experts-exchange.com/Software/Microsoft_Applications/Q_27566782.html

Const HKEY_CURRENT_USER = &H80000001

Dim regobj As Object

Dim aTypes As Variant

Dim aDevices As Variant

Dim vDevice As Variant

Dim sValue As String

Dim v As Variant

Dim sLocaleOn As String

' get locale "on" from current activeprinter

v = Split(Application.ActivePrinter, Space(1))

sLocaleOn = Space(1) & CStr(v(UBound(v) - 1)) & Space(1)

' connect to WMI registry provider on current machine with current user

Set regobj = GetObject("WINMGMTS:{impersonationLevel=impersonate}!\\.\root\default:StdRegProv")

' get the Devices from the registry

regobj.EnumValues HKEY_CURRENT_USER, "Software\Microsoft\Windows NT\CurrentVersion\Devices", aDevices, aTypes

' find Printer and create full name

For Each vDevice In aDevices

' get port of device

regobj.GetStringValue HKEY_CURRENT_USER, "Software\Microsoft\Windows NT\CurrentVersion\Devices", vDevice, sValue

' select device

If Left(vDevice, Len(Printer)) = Printer Then ' match!

' create localized printername

GetPrinterFullName = vDevice & sLocaleOn & Split(sValue, ",")(1)

Exit Function

End If

Next

' at this point no match found

GetPrinterFullName = vbNullString

End Function

It doesn't seem too surprising that some code might run slower after `Application.EnableEvents = True` and `Application.ScreenUpdating = True`. Beyond that observation, you really haven't given us anything to go on. — John Coleman, Dec 13 '19 at 15:09
Curious - why not just use say `ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="FileName Here"`? Instead of doing all the printer changing business? — BruceWayne, Dec 13 '19 at 15:10
Why are you using an ancient Excel4Macro to print `ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"` and not the normal [Sheets.PrintOut method](https://learn.microsoft.com/en-us/office/vba/api/excel.sheets.printout) using `ActiveSheet.PrintOut`? — Pᴇʜ, Dec 13 '19 at 15:10
@BruceWayne: I agree, one thing to consider though: what a PDF looks like when using ExportAsFixedFormat is affected by the currently selected printer! — jkpieterse, Dec 13 '19 at 15:26
Cross-posted here: https://www.reddit.com/r/excel/comments/ea4zb1/macros_run_quickly_until_different_macro_is_run/ — jkpieterse, Dec 13 '19 at 15:32
@JohnColeman, Those lines are at the beginning and end of each macro, so they are turned off when running each set of code. — bananabongos, Dec 13 '19 at 15:33
@jkpieterse I'm pretty new to posting, sorry if it's not allowed to cross-post, I was just looking to gain visibility — bananabongos, Dec 13 '19 at 15:35
@BruceWayne I was having trouble getting it to print the filename and format i wanted using that method. If there are threads that would help me out in those regard it would be greatly appreciated. — bananabongos, Dec 13 '19 at 15:41
@Pᴇʜ I wanted to control it as a print to pdf and not just the default printer on a user's computer. And part of the complication with the whole thing is that the "Adobe PDF" printer is on different networks for a large number of users so I wanted to build that flexibility in. — bananabongos, Dec 13 '19 at 15:46
"Those lines are at the beginning and end of each macro" -- not in the code that you showed. As I said in my comment, you haven't really given us anything to go on. You seem to be asking us why some unknown (to us) macros run slower after you run the code that you have shown us. — John Coleman, Dec 13 '19 at 17:01

score 1 · Accepted Answer · answered Dec 13 '19 at 16:21

1

Actually the following should do the trick. I think your approach was unnecessarily complicated.

Option Explicit

Private Sub Save_Workbook_As_PDF2()
    Dim CurrentSheet As Worksheet
    Set CurrentSheet = ThisWorkbook.ActiveSheet

    ThisWorkbook.Worksheets(Array("Quote Sheet", "Terms and Conditions")).Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="D:\Temp\test.pdf"

    CurrentSheet.Select
End Sub

answered Dec 13 '19 at 16:21

Pᴇʜ

56,719
10
49
73

Thanks for the response. This will work if I decide to put all of the files into one consistent location. I think the reason I was avoiding this was because I wanted a Save dialog box to pop up so the user could save the file where they wanted. Is that a possibility using the export method? – bananabongos Dec 13 '19 at 16:55
@bananabongos - Use [this function](https://stackoverflow.com/a/26392703/4650297) to get the path, set that as a variable and there you go! E.g. `Dim fldr as String // fldr = GetFolder // ...Filename:= fldr & "\test.pdf"`. – BruceWayne Dec 13 '19 at 17:16
Thank you both! This solution works very well, and is much cleaner. – bananabongos Dec 13 '19 at 18:35
Just so you know; the layout of the resulting PDF *may* depend on whatever the default printer is at the moment you issue that command.. – jkpieterse Dec 16 '19 at 09:40
@jkpieterse Interesting .. how does it affect the export which should not use any printer at all? Do you have more information? – Pᴇʜ Dec 16 '19 at 09:45
It affects the layout as Excel uses the current printerdriver's defaults (like available paper sizes and margins) to format the sheet for PDF 'printing'. This also applies to thngs like aspect ratios of graphical objects such as logo's, which Excel is terrible at maintaining anyway. – jkpieterse Dec 16 '19 at 13:22
@jkpieterse Ah, well that's true. Of course this will be a *"super nice"* combination with the Win10 auto select default printer feature (which sets the last used printer as default printer, if not turned off manually) :(( – Pᴇʜ Dec 16 '19 at 13:42

Macros run quickly, until different macro is run

1 Answers1