Overflow Error Puzzle

[nigelandrewfoster]

Hi. I am exploring execution time overheads using various structures of code. I came across this today - an Overflow error (on the highlighted line) caused simply by declaring data type. Can anyone explain it please?

When I run the code without declaring the arguments I am passing as integers (i.e. I leave them as variants) the code executes with no problems.

Thanks

Sub B()

Dim StartTime As Double
Dim SecondsElapsed As Double
Dim Count As Single
Dim Result As Single
Dim A As Integer, B As Integer, C As Integer, D As Integer, E As Integer, _
        F As Integer, G As Integer, H As Integer, I As Integer, J As Integer
       
A = 1
B = 2
C = 3
D = 4
E = 5
F = 6
G = 7
H = 8
I = 9
J = 10


StartTime = Timer


For Count = 1 To 10000000
    Result = Multiply(A, B, C, D, E, F, G, H, I, J)
Next


SecondsElapsed = Round(Timer - StartTime, 2)


MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation


End Sub


Function Multiply(a1 As Integer, a2 As Integer, a3 As Integer, a4 As Integer, a5 As Integer, _
        a6 As Integer, a7 As Integer, a8 As Integer, a9 As Integer, a10 As Integer) As Single
[COLOR=#ff0000]    Multiply = a1 * a2 * a3 * a4 * a5 * a6 * a7 * a8 * a9 * a10[/COLOR]
End Function

 

[nigelandrewfoster]

Wow. Sold. Longs it is then

 

[GTO]

Nice example @GTO if Integers are converted into Longs, is that what cause the difference in execution time?

Curious why Longs process faster than Integers, is it to do with using 32 or 64-bit computing, vs old days of 16-bit or less or because of the conversion of integers to longs?

Greetings Jack,

The short answer is yes.

Most of the articles or threads I've read seem not overly clear to me, on a subject that is a stretch for my knowledge level (What's really going on inside the black box?) to begin with, so hopefully I do not muddy the waters.

In gist though, I would not think of it in terms of the VBA Integer being converted into a VBA Long. Rather, as I understand it, the 16-bit binary value (by example: 0000000000000011 or 3 in decimal) stored in the VBA Integer gets processed or worked with in 32-bit chunks in 32-bit Excel (let us presume 32-bit Excel and disregard the sign bit). So... there is a "conversion" going on, which is that VBA has to write the 16-bit value into a 32-bit chunk (sorry, 'chunk' is the only word I could think of) before it starts adding/multiplying it to some other value. Does that make any sense?

Mark

 

[GTO]

Wow. Sold. Longs it is then

Glad we were able to help.

 

[JackDanIce]

Hi Mark, thanks for the short confirmation and more helpfully your interpretation of it, does make sense, even use of the word "chunk!" Much appreciated, enjoy rest of your day!

 

[nigelandrewfoster]

Thanks, Jack. You say "processing data in memory". Could you explain where the arguments are to be stored and how referenced if not in variables? Referencing the worksheet's cell values directly takes far longer...?

 

[JackDanIce]

Hi Andrew,

I wouldn't be able to explain sufficiently in a thread reply, but using arrays to read data from a spreadsheet, then process the data in the array (in memory) and then writing out the results is faster than VBA repeatedly making calls to and from the sheet for each formula or calculation. Caveat, this is a generalised comment and far too many examples where alternatives are faster!

Maybe start by reading about arrays, here's one link: https://www.excel-pratique.com/en/vba/vba_arrays.php

Another on general speed execution: http://chandoo.org/wp/2012/03/27/75-excel-speeding-up-tips/

 

[nigelandrewfoster]

Thanks Jack. I did know about arrays and have been using them to speed execution. I guess I still considered them variables so didn't realise that's what you were referring to. I will read the article on speed execution with interest. It's not a subject I've had an awful lot of concern for until quite recently when I have been asked to develop code for Macbooks by one of my main clients. As you may or may not know Mac for Excel chugs along like an old steam roller and it's a little embarrassing when you've developed something which works at lightning speed on your laptop to find it executing so slowly for the client so any speed increase I can get is going to be useful.

 

[GTO]

Hi Mark, thanks for the short confirmation and more helpfully your interpretation of it, does make sense, even use of the word "chunk!" Much appreciated, enjoy rest of your day!

You are most welcome and glad I didn't booger that up too awful! It's a lot to think about, least for me.