If you are coming from the .Net world, then you are used to declare variables like this:
|
1 |
Dim foo, bar as Long |
In this case, both foo and bar are of type Long. Then, someone tells you that you want to switch for a side project to VBA and as far as you read somewhere that VBA is quite similar to VB .Net you agree. And you start declaring variables the same way.
Thus, one day you realize that you have declared all of your variables as Variants and this is one of the many reasons why your code acts somehow strangely. Yup, in VBA as you may see on the right picture below, the foo variable is declared as a Variant and only the bar variable is of type Long. Quite ironic.
As a next step, you read that you should be declaring like Dim foo as Long, bar as Long and you simply decide to stop reading and to quit working with VBA, because it is a funny scripting language* (I am citing a specific guy here).
And then something else appears – the type declaration suffixes of VBA. And as far as you are a VBA developer and you probably* code alone in Excel or Access, you decide that you will use them. Thus, the following two lines get declared absolutely identically:
|
1 2 |
Dim a() As Byte, u As Long, j As Long, s As Long, n As Long, q As Variant Dim a() As Byte, u&, j&, s&, n&, q |
The only difference is that the second line is about twice less and it can make a seasoned VBA developer crazy if he sees you using it. Still, it is in the language as a syntactic sugar and u& means that u is declared as a value of type Long. This is the list with the rest of the values:
|
1 2 3 4 5 6 7 |
Dim someInt% 'Integer Dim howLonge& 'Long Dim areYouSng! 'Single Dim youWant# 'Double Dim showMeDa@ 'Currency Dim girlsWear$ 'String Dim howManyHours^ 'LongLong on 64 bit |
It gets even more interesting – you can also declare the type of literal, when you assign it to a Variant type. Thus 10# will be declared as a double, when assigned to Variant here:
|
1 2 3 4 5 |
Public Sub TestMe() Dim myVariant myVariant = 10# Debug.Print VarType(myVariant) = vbDouble 'returns True End Sub |
At this point any developer, who does not have VBA as a first language would be given a reason to hate it even more, and probably bring VBA to the second place of the most hated languages. Who knows?
Take home message – stay calm, code in VBA & do not use type declaration suffixes.