VBA performance question

[paulsprunken]

My macro starts with the following declarations:

Dim pathActHdr As String
Dim pathSoHdr As String
Dim createNewSub As String
Dim saveNewSub As String

pathActHdr = Range("'PARAMETERS'!pathActHdr").Value
pathSoHdr = Range("'PARAMETERS'!pathSoHdr").Value
createNewSub = Range("'PARAMETERS'!createNewSub").Value
saveNewSub = Range("'PARAMETERS'!saveNewSub").Value

Note: there is a sheet 'PARAMETERS" in the workbook that hold a couple references that are needed furtheron in the macro.
The values in this sheet have been given Named ranges
The Dim variables are using the same name as the Named Ranges (for ease of reference)

However, when running the macro, it takes between 1-2 minutes to get past the last of the 8 lines copied above and I'm wondering
how this performance might be improved. Searches did not yield any clear direction for me to pursue yet

Thanks to anyone taking an interest; much appreciated!

 

[rlv01]

To measure is to know. You might start by (temporarily) adding timer code to make sure you understand where the slowdown is coming from.

Dim pathActHdr As String
Dim pathSoHdr As String
Dim createNewSub As String
Dim saveNewSub As String
Dim ST As Single

ST = Timer
pathActHdr = Range("'PARAMETERS'!pathActHdr").Value
Debug.Print "Elapsed Time: " & Round(Timer - ST, 3) & " sec  - pathActHdr"

ST = Timer
pathSoHdr = Range("'PARAMETERS'!pathSoHdr").Value
Debug.Print "Elapsed Time: " & Round(Timer - ST, 3) & " sec  - pathSoHdr"

ST = Timer
createNewSub = Range("'PARAMETERS'!createNewSub").Value
Debug.Print "Elapsed Time: " & Round(Timer - ST, 3) & " sec  - createNewSub"

ST = Timer
saveNewSub = Range("'PARAMETERS'!saveNewSub").Value
Debug.Print "Elapsed Time: " & Round(Timer - ST, 3) & " sec  - saveNewSub"

(Tip: For future posts , please try to use code tags like I did above when posting code. It makes your code easier to read and copy.

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com

)

 

[paulsprunken]

Dim ST As Single ST = Timer pathActHdr = Range("'PARAMETERS'!pathActHdr").Value Debug.Print "Elapsed Time: " & Round(Timer - ST, 3) & " sec - pathActHdr" ST = Timer pathSoHdr = Range("'PARAMETERS'!pathSoHdr").Value Debug.Print "Elapsed Time: " & Round(Timer - ST, 3) & " sec - pathSoHdr" ST = Timer createNewSub = Range("'PARAMETERS'!createNewSub").Value Debug.Print "Elapsed Time: " & Round(Timer - ST, 3) & " sec - createNewSub" ST = Timer saveNewSub = Range("'PARAMETERS'!saveNewSub").Value Debug.Print "Elapsed Time: " & Round(Timer - ST, 3) & " sec - saveNewSub"

Could agree more about measuring and thanks to your explanation it is clear the issue is further down the macro code
I'll insert more timers to zoom in on the culprits and will update this post in case I cannot manage to resolve

Thanks so much for the Timer feature; we learn everyday!

 

[paulsprunken]

Could agree more about measuring and thanks to your explanation it is clear the issue is further down the macro code
I'll insert more timers to zoom in on the culprits and will update this post in case I cannot manage to resolve

Thanks so much for the Timer feature; we learn everyday!

the Timer revealed a timeconsuming number of text and cell formatting lines; Not yet sure how I'll improve on those but for now this question can be closed
Thanks

 

[rlv01]

the Timer revealed a timeconsuming number of text and cell formatting lines; Not yet sure how I'll improve on those but for now this question can be closed
Thanks

One way is to turn off screen updating while you make formatting changes.

Application.ScreenUpdating = False

Another is to try to make as many changes as you can to larger ranges instead making them to individual cells one at a time.