Newsletter by

View this email in your browser Newsletter for November 2016


November has been an exciting month for me. I've attended the Microsoft MVP Summit in Redmond and got to engage with the people that build our favorite product: Excel. We had four full days of outstanding discussions about the direction Microsoft is taking with Excel and the team showed us many insights in what is coming. Very interesting stuff you know but alas, nothing I am allowed to share with you.
This month's newsletter has two subjects. I revisit the bug report caused by a recent MSFT update and I talk a bit about whether to use Longs or Integers in VBA.

Userform controls bug in Office 2016 revisited

A recent update by Microsoft is causing strange behavior in userforms (that is in VBA). The controls Transparency and ZOrder properties are no longer respected, causing your intricate userform adjustments to look odd. I'm pleased to say Microsoft has published a fix.

Integer or Long, what to use

If you write VBA, you must know the variable types Integer and Long. Question is which type should you use when?

Why do I ask this question?

Well, because I found this:
There is an (ancient) MSDN Article that states :"Traditionally, VBA programmers have used integers to hold small numbers, because they required less memory. In recent versions, however, VBA converts all integer values to type Long, even if they're declared as type Integer. So there's no longer a performance advantage to using Integer variables; in fact, Long variables may be slightly faster because VBA does not have to convert them."

Does size matter?
Don't assume, test!

I wrote these two very simple routines:

Sub IntegerOrLong1()
    Dim a(1 To 1000000) As Integer
    Dim lCt As Long
    For lCt = 1 To 1000000
        a(lCt) = 1
End Sub

Sub IntegerOrLong2()
    Dim a(1 To 1000000) As Long
    Dim lCt As Long
    For lCt = 1 To 1000000
        a(lCt) = 1
End Sub

Next thing I did was open Task Manager and write down memory usage (I know, perhaps not very accurate) of 32 bit Excel 2010 on a 64 bit Windows 7 machine. Then I ran each routine 4 times, each time writing down the memory footprint of Excel when the routine reached the Stop statement and writing it down again after the routine was ended. This is what I got:
first run 41052
end 39300
Delta 1752
second run 41296
end 39444
Delta 1852
third run 41472
end 39648
first run 43788
end 39908
Delta 3880
second run 43802
end 39904
Delta 3898
third run 43828
end 39916
Delta 3912

So it seems an Array of Longs does take up about twice the size an array of integers does.

What about speed?

I got rid of the Stop statements and added this little routine (which makes use of a timer module with a high-resolution timer):

Sub RunTest()
    Dim lCt As Long
    Dim lCt1 As Long
    For lCt = 1 To 4
        TimerStart "Integer"
        For lCt1 = 1 To 100
    For lCt = 1 To 4
        TimerStart "Long"
        For lCt1 = 1 To 100
End Sub

Which gave me:
Integer 1.763577563
Integer 1.784700817
Integer 1.759667846
Integer 1.750379157
Long 2.016973102
Long 1.935987109
Long 1.907695843
Long 1.914178086

So using Integers is just a tiny bit faster (about 10% in this code sample) too.

The verdict

So you might say if your numbers are not very big then Integers are better than Longs. They use less space and are faster. But in Excel VBA, Integers and Longs are often used to count your way through a heap of spreadsheet cells. and there are over a million of them in a single column. More than an Integer can hold.
I stay on the safe side and have taken on the habit of using Long as much as I can.

But of course there are very good reasons to use Integers:
- If you need them for a function call that expects an integer argument
- If speed and memory use matters a lot in your application

Auditing of Formulas made easy

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

Mission-critical Excel Model Crashes

Do you have mission-critical Excel files that cause problems? Consider our Excel File Remediation Utility
Copyright © 2016 JKP Application Development Services, All rights reserved.

unsubscribe from this list    update subscription preferences 

Email Marketing Powered by Mailchimp