Declaring variables

  • 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!

    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.


    You ain't seen me......right!

  • I started off the "lazy" way not declaring variables because it was quicker to write the code. Now I use the option explicit for new code at all times and try never to just take the default variant. If I need a variant I dim it as such. It takes a bit longer, but sure is easier to catch errors. I am a lousy typist (slow and mistake prone) so I tend to use the shortest variable names I can get away with, but I suspect the discipline of londger more descriptive variables would be a good habit to start.

  • Dave,

    Nothing like a good working example to remind us all of proper disipline. You've prompted me to reinstall Option Explicit as the default:

    in the VBA Editor: TOOLS > OPTIONS > EDITOR tab > Require Variable Declaration

    NB: This automatically inserts Option Explicit at the start of a new module and forces one to DIM varaibles.

  • I know the first post had an example of code for illustration purposes, but you would probably get a speed increase if you used <font color=blue&gt;Long</font&gt; integers rather than short <font color=blue&gt;Integer</font&gt;.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!