A Dim understanding: declaring variables in VB/VBA

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

A Dim understanding: declaring variables in VB/VBA

Post by HansV »

In The importance of 'Option Explicit', I stressed the importance of declaring variables explicitly.

Declaring a variable is usually done using the keyword Dim, for example

Dim MyVar As Integer

or

Dim MyVar

In the first example, we tell Visual Basic that we are going to store numbers of type Integer in the variable. Integers are whole numbers between -32,768 and +32,767. If we try to store a number outside this range, or another kind of data in the variable, we will get an error message. For example,

MyVar = 37

is OK, but

MyVar = "Lounge"

will cause an error message.

In the second example, we merely tell Visual Basic that we are going to use a variable named MyVar, but we don't specify what kind of data we are going to store in it. Since VBA is very forgiving, it allows us to store any kind of data in it - text, numbers, dates, and more complicated data. Both

MyVar = 37

and

MyVar = "Lounge"

are OK in this situation. Technically speaking, omitting the data type is the same as declaring a variable as a Variant.

If we can store anything in a Variant, why bother to declare a data type explicitly at all? Isn't it more work?

It is more work, but it does have advantages, the same way declaring variables explicitly has advantages. It enables Visual Basic to warn us if we try to assign the wrong type of value to a variable. Specifying the data type also makes code execution more efficient. With a Variant, Visual Basic has to inspect the data type each time you do something with the variable, and try to determine the best way to handle it. With an explicitly declared data type, Visual Basic knows in advance what to expect.

Still, there is a place for Variants too.

Some functions can return more than one type of result. For example, the Excel VBA function GetOpenFilename displays the Open dialog. It returns the Boolean value False if the user cancels the dialog, otherwise it returns a string or an array of strings depending on whether the MultiSelect argument is False or True.

In a construction such as

Code: Select all

MyArray = Array(2, 3, 5, 8, 13)
For Each MyItem In MyArray
  ...
Next MyItem
the variable MyItem must be a Variant.

If you want to declare multiple variables, you can do so on multiple lines:

Dim FirstName As String
Dim LastName As String

or on a single line:

Dim FirstName As String, LastName As String

Note that both variables are explicitly declared as strings. If I had written

Dim FirstName, LastName As String

LastName would have been declared as a string, but FirstName would have been a Variant because I didn't specify a data type explicitly. This is different from many other programming languages, so keep it in mind when writing VBA code.
Best wishes,
Hans

User avatar
Jan Karel Pieterse
Microsoft MVP
Posts: 656
Joined: 24 Jan 2010, 17:51
Status: Microsoft MVP
Location: Weert, The Netherlands

Re: A Dim understanding: declaring variables in VB/VBA

Post by Jan Karel Pieterse »

Nice write-up, Hans.
Regards,

Jan Karel Pieterse
Excel MVP jkp-ads.com

User avatar
Wendell
4StarLounger
Posts: 482
Joined: 24 Jan 2010, 15:02
Location: Colorado, USA

Re: A Dim understanding: declaring variables in VB/VBA

Post by Wendell »

Amen!
Wendell
You can't see the view if you don't climb the mountain!

vpprof
NewLounger
Posts: 1
Joined: 26 Jul 2012, 14:25

Re: A Dim understanding: declaring variables in VB/VBA

Post by vpprof »

Hi everyone,
HansV wrote:Dim FirstName As String, LastName Is String

Note that both variables are explicitly declared as strings. If I had written

Dim FirstName, LastName Is String

LastName would have been declared as a string, but FirstName would have been a Variant because I didn't specify a data type explicitly. This is different from many other programming languages, so keep it in mind when writing VBA code.
Yes, this is true and certainly bizarre when compared to the practices of other programming languages, but it is also blatantly different from what they say in a msdn article about Dim:
http://msdn.microsoft.com/en-us/library/7ee5a7s1%28v=VS.80%29.aspx wrote:
  • Different Types. You can specify different data types for different variables by using a separate As clause for each variable you declare. Alternatively, you can declare several variables to be of the same type by using a common As clause. Each variable takes the data type specified in the first As clause encountered after its variablename part.

    Code: Select all

    Dim a, b, c As Single, x, y As Double, i As Integer
    ' a, b, and c are all Single; x and y are both Double
True, the article is about VB and not VBA. And there seems to be no article about Dim statement in VBA on msdn. Well it most certainly can be misleading.

And this behavior doesn't change no matter if Option Explicit is included or not.

Anyway, thanks HansV, your post put an end to hours of my fruitless research. Well… better late than never :)

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: A Dim understanding: declaring variables in VB/VBA

Post by HansV »

Welcome to Eileen's Lounge!

The way VBA handles declarations is inherited from "classic" VB (VB6 and before). The article you refer to (Dim Statement (Visual Basic)) applies to its successor VB.Net. VB.Net resembles VB / VBA in many ways, but it has a more modern syntax.
Best wishes,
Hans

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: A Dim understanding: declaring variables in VB/VBA

Post by Rudi »

Hi Hans,

Thanks for the lesson :) (Always valuable)

How about dimming Function variables?

Dimming usually takes lace in the Function creation, but is it important to dim both the Function and the Arguments or is just the arguments OK

EG:

Function MyCalc(Num1 as Integer, Num2 as Integer) as Long
or
Function MyCalc(Num1 as Integer, Num2 as Integer)

In many cases, I never see the function dimmed, just the arguments. Is this acceptable?
TX
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: A Dim understanding: declaring variables in VB/VBA

Post by HansV »

It depends.

In a function to be used as a custom worksheet function in Excel, for example, you often want to be able to return a specific value if all arguments are OK, and an empty string if an argument is missing or incorrect. In such a situation, the return type of the function needs to be a Variant, and since this is the default, you can omit it.

Similarly, in a function to be used in a query in Access, you often want to be able to return a specific value if the arguments are OK, and Null if an argument is missing or incorrect. In that situation, the return type must be Variant too.

But if your function should always return a value of a specific type, e.g. a Long (Integer), it's better to specify that explicitly.
Best wishes,
Hans