I am a newbie to VBA and learning it all from the ground up. I have taken Dave's VBA lessons and am reading books and practising when I have spare time. I also get great help from the members of this forum.
I was therefore amazed when I looked at some code from some of the so called "spreadsheet experts" from my company - 90% of the code didn't have variables declared. When I asked them why I got two answers
1. Homer Simpson type blank stare eg what do you mean? :o
2. What's the point VBA does it for you :o
I then gave them this snippet of code and told them to run the code a second time with the Dim statements commented out.
On my machine it took 0.9s and 2.2s respectively. On one of the machines in the office it took 4.1s and 8.9s!
Sub timeTest() Dim x As Integer, y As Integer Dim a As Integer, b As Integer, c As Integer Dim i As Integer, j As Integer Dim StartTime As Date, EndTime As Date ' Store the starting time StartTime = Timer ' perform calculations x = 0 y = 0 For i = 1 To 5000 For j = 1 To 1000 a = x + y + i b = y - x - i c = x - y - i Next j Next i ' get ending time EndTime = Timer ' display total time in seconds MsgBox Format(EndTime - StartTime, "0.0") End Sub
It took twice as long without the variables being declared on such a simple piece of code so I hate to think what the difference would be on a large program??
I didn't write the code myself - it's from a John Walkenbach book.