Wednesday, 21 December 2011

Variable Declaration – Sounds Easy in VBA..

Well its easy up to the point you like to keep it easy by single individual variable declaration, but when the smart ways kicks in even simple things don’t remain easy in VBA.

Well some time I myself got confused about some codes of what’s happening and what these symbols interpret. So I wanted to clarify some bits of variable declaration styles in this post of mine.

Combined Declaration:

When multiple variables are declared in one Dim statement, the types have to be individually defined for each of them using the “as” operator.

Data Type Symbol declaration:

This is another way of declaring variables whereby you are escaped from writing a whole big line specifying the data type a variable is declared of.In this you just use the specified symbols and works done!!
The following, lists the symbols used for the same:

! = Single Precision
% = Integer
& = Long Integer
@ = Currency
# = Double Precision
$ = String

Also along with this post I am providing some quick referencing links for the data types and their capacity with byte consumption to efficiently design your code.

1. Quick ref 1:
2. Quick ref 2:

The following VBA code illustrates the declaration styles.

Public Sub dataTypes_Declarations()

    '--Old ways of declaration

    Dim r As Integer
    Dim s As Double

    Debug.Print "--Old ways of Declaration--"
    Debug.Print "r is of type: " & TypeName(r)
    Debug.Print "s is of type: " & TypeName(s)

    '--Combined declaration BEWARE

    Dim i, j, k As Integer

    Debug.Print "--Combined Declaration --"
    Debug.Print "i is of type: " & TypeName(i)
    Debug.Print "j is of type: " & TypeName(j)
    Debug.Print "k is of type: " & TypeName(k)
    '--Declaration Shortcuts
    '! = Single Precision
    '% = Integer
    '& = Long Integer
    '@ = Currency
    '# = Double Precision
    '$ = String

    Dim a!, b%, c&, d@, e#, f$

    Debug.Print "--Declaration Shortcuts --"
    Debug.Print "a (!) is of type: " & TypeName(a)
    Debug.Print "b (%) is of type: " & TypeName(b)
    Debug.Print "c (&) is of type: " & TypeName(c)
    Debug.Print "d (@) is of type: " & TypeName(d)
    Debug.Print "e (#) is of type: " & TypeName(e)
    Debug.Print "f ($) is of type: " & TypeName(f)
End Sub


No comments: