VBA embedded image glitch - One sheet fast the other slow

VBAmonkey

New Member
Joined
Jul 17, 2020
Messages
4
Office Version
  1. 2013
Platform
  1. Windows
Long time reader first time poster. I've been using VBA for 6+ years now. I have a problem with a process that is largely exposed with a certain section of code. I have attached a link to the file for reference.

The file:- Contains 100 embedded images and minimal text
The code:- loops through and reformats 100 ranges of cells

On my old laptop(I5,8Gb Ram,Win7 and Office 2013) - This macro runs in 0.68 seconds on average
On my new XPS(I7,16Gb Ram, Win10 and Office 2013) - This macro runs in 20 seconds on average
I have tested on a few different computers running different windows/office and the results are between the two above.

Now here is the interesting part. I created a new file, copied over the images and formatted everything the same and then it ran at 0.5seconds(on the XPS). When I ran it a second time it was then up at 20 seconds. I duplicated the sheet in the same file and one sheet runs at 0.5 seconds and the other runs at 20 seconds. If I delete the images in the slow sheet then the speed is back down at 0.5 seconds also so the images are the culprit but I need them for the way this sheet(and others) are formatted rather than using conventional headers.

This code is part of a larger section of code in a very large and highly used spreadsheet and so this 20 seconds is amplified significantly in reality.

Can anyone replicate these results with the linked sheet on their computer, and if so can anyone tell me what is different between the sheets so that I can finally solve the problem?

 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

yky

Well-known Member
Joined
Jun 7, 2011
Messages
1,816
Office Version
  1. 2010
Platform
  1. Windows
Sheet1: first run 1.1 seconds, subsequent runs around 1.1 seconds,
Sheet2: first run 4.6 seconds, subsequent runs around 1.1 seconds.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,151
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Sheet 1 runs between 0.5-0.7 seconds for me. Sheet2 takes just over 1s the first time and between 0.5 and 0.7 thereafter. I do note that the used range on sheet 2 is about double that of sheet 1.
 

VBAmonkey

New Member
Joined
Jul 17, 2020
Messages
4
Office Version
  1. 2013
Platform
  1. Windows
Thanks, I'll look into the used range as it is different even though the No. of filled cells are the same on each sheet.

I tried it on 8 computers before creating the thread. It ran slow(10-20 seconds) on 5 of them and the fast sheet was always fast.

I think it might be a glitch in the computer settings or excel itself. For some reason, something changes in the sheet properties and any objects on the sheet slows the sheet processing down and make the sheet laggy, glitchy and ultimately slow to move around and edit when there are lots or dense chart.
 

VBAmonkey

New Member
Joined
Jul 17, 2020
Messages
4
Office Version
  1. 2013
Platform
  1. Windows
After investigating further with the lead from RoryA, I found that these was corrupt formatting outside the used range that was slowing the sheet down. I am still unable to explain why it only slows down some systems but was able to find out that clearing all the cells outside of the used range repairs the sheet. I used the code below if anyone has the same issue.

Also, if anyone is able to explain why this happens and why it may only slow some computers I would still greatly appreciate the advice.

Sub clearFormatOutsideUsedRange()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Set sht = ActiveSheet
sht.UsedRange 'Refresh UsedRange
lastRow = sht.UsedRange.Rows(sht.UsedRange.Rows.Count).Row
LastCol = sht.UsedRange.Columns(sht.UsedRange.Columns.Count).Column
lLastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
RowEnd = Cells(lastRow, 1).End(xlDown).Row
ColEnd = Cells(LastCol, 1).End(xlToRight).Column
Range(Cells(lastRow, 1), Cells(RowEnd, ColEnd)).Clear
Range(Cells(1, LastCol), Cells(RowEnd, ColEnd)).Clear

Application.ScreenUpdating = True
Application.Calculation = calcState
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,123,305
Messages
5,600,867
Members
414,410
Latest member
4610

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top