Excel StatusBar Font Size Changing Unintentionally

Question

I've been living with a very strange Excel quirk regarding the StatusBar for almost a year now, and was wondering if anyone had any insight, and possible solutions. There were about a dozen or so posts about how to update the StatusBar, but nothing quite related specifically to its appearance. I believe one user asked about changing the font color, but the solution was to use something other than the StatusBar.

Anyway, I use a macro to call a separate modeling software add-in. We have numerous cases organized in columns and each case may need to be 'run' several times. So I use the StatusBar to let me know which column it's on, the case #, and which iteration for that specific case. It looks like this...

This is the single line of code that I use to populate the StatusBar:

Application.StatusBar = "Processing column " & Split(Cells(1, icol).Address(True, False), "$")(0) & ", Case " & iCase & ", Iteration (Loop) " & j & " of " & icoljcount

The issue I have is that the appearance bounces back and forth between the previous image (Large font) and the following image (Small font):

I included a few tabs in the screen shots so you could see the size change. I have no idea why it does this. It doesn't really affect the function, except that it makes your eyes jump if you're watching it. And I watch it a lot. It get's annoying. And I hesitate to add more useful info to the StatusBar if I can't resolve this.

I put in a codebreak and stepped through to see if I could identify which line was being executed when each font size appeared. Turned out BOTH appeared when I hit F8 on the line above I provided. The large version appeared first, but only lasted split second before it switched to the small version. When my macro is running the large version sticks around for a full second - I believe this is because the very next line of code calls the external program, which triggers a recalc of the spreadsheet, and it's a very large spreadsheet.

I may be able to mitigate the annoyance by adding a 100 millisecond 'sleep' before calling the external program. That might give Excel the split second it needs to flip to the small font version before getting bogged down with the recalc. But I honestly don't know if that will end up being more or less annoying.

Just in case it matters, I'm using Windows 7, 2010 Excel 32-bit, "Silver" Excel theme, and enjoy The High Life, but not at work or this wouldn't annoy me nearly as much.

Sorry for the lengthy question. Thanks in advance to whoever cracks this one open.

Answer 1

I duplicated my macro in a new sub in order to trim it down to the bare minimum that was necessary for people to follow the loop. To make a long 'answer' short(er), the only line that mattered was:

Application.ScreenUpdating = False

As I was preparing this answer I learned even a bit more. My problem might still be the combination of ScreenUpdating AND calling of the external program. Here's a sample code of a pretty simple StatusBar with loop...

Sub StatusBarTest()

Dim i As Integer

Application.DisplayStatusBar = True
Application.ScreenUpdating = False '<-- toggle on/off to see the effects

For i = 1 To 5

Application.StatusBar = "Loop #" & i & " of 5.  Extremely long StatusBar text so as to amplify a font size change."

'In my full loop this is where my Application.Run statement goes.

Application.Wait (Now + TimeValue("00:00:02")) '2 second delay so user has a chance to witness StatusBar

Next i

Application.StatusBar = False
Application.ScreenUpdating = True

End Sub

When I run this sub from Excel I do NOT see any font switching. But when I step through the code with F8 from VBA I see the larger font for a split second each time the StatusBar is updated, IF screen updating is turned off as written above. If I comment out the screenupdating line then I don't see the font switch even when stepping through the code.

I really don't understand why VBA/Excel behaves like this, but I believe I've identified the cause/effect pattern.