Understanding DIM

[Jstns]

Hi guys!

I have a question about Variables and dimming

I understand that it is best practice to dim as Excel otherwise has to guess what type of variable it is. Can someone however give me an example in which case this happens so I can make it more tangible for myself and my colleague?

So an example in which a macro isn't working properly because the user has not dimmed his/her variable

Thank you all in advance!

[AB33]

You can not have a variable with out dim in VBA. VBA is statically and strongly typed programming language, unlike dynamic languages, such as Python. You need to declare not only the name, but the data type as well. If you do not declare the data type, it assumed as a "Variant" data type, so if you say Dim MyVariable with out explicitly defined the data type, it assumed as "Variant" data type.

[Jstns]

Thank you for your reply!

Yes I understand that, I am asking for an example so that my colleague who never uses dim understands why it is dangarous to not declare

[AB33]

Let's say

Dim i As Long, j,k
Many people fallin for the above trap, assuming that all variables are declared as LONG, which is what you do in other c based programming languages. However, in VBA, this means, it is only (I) is declared as LONG, but , j and k as "Variant".

In general, you should avoid declaring variables as "Variant" and be explicit in declaring data types. If you declare all variables as "Variant" , you have left for VBA to figure out how to fit in (Convert) each variables in to correct data type. This could result in overhead and may lead not only to poor performance, but also wrong output.

[Arkadi]

Another detail is this:

using Option Explicit at the top of a sub will force you to Dim any variable used. That can be annoying, but also prevents problems. For example,

Option Explicit

Sub Calculate_Value

Dim myValue1 as Long
Dim myValue2 as Long

myValue1 = 50
myValue2 = myValu1 * 10 'notice typo in the myValue1 variable

End Sub

The code above will not run, letting you know myValu1 is not declared. If the programmer didn't notice the typo, and didn't use Option Explicit, then myValu1 not having been assigned a value, myValue2 would actually be 0 instead of the intended result of 500.

[bakerman2]

why it is dangarous to not declare

The ongoing discussion.

[xlnitwit]

If a macro is properly written, it would be extremely unlikely that not declaring a variable would stop it working. It might run slower, but it is unlikely to actually break.

However, declaring variables can assist you while writing and debugging code, not just for spelling errors, but also catching issues where you forgot to use Set with a range object for example, or tried to pass the wrong data type to a routine, or merely providing Intellisense as you work.

Here's a simple example where forgetting to use Set would result in no error at run time, but nothing would actually happen

dataRange = range("A1:A100")
' do some stuff with an array
dataRange = some_array

You might have intended to output the array to the range in question but by omitting set all you did was assign one value to a variable then later assign another value to the variable. No complaints from the compiler at run time and therefore harder to track down.

[Andy Pope]

I agree with xlnitwit, declaring the type of variable is useful but bad coding is bad coding.

Here is an example where variant type can cause confusion.

Sub Test()

    Dim value1
    Dim value2
    Dim lngvalue1 As Long
    Dim lngvalue2 As Long
    
    
    value1 = Right("Name1", 1)
    value2 = Right("Name2", 1)
    
    Debug.Print value1 + value2
    
    lngvalue1 = Right("Name1", 1)
    lngvalue2 = Right("Name2", 1)
    
    Debug.Print lngvalue1 + lngvalue2
    
End Sub

When the variable type is defined the compiler can cast the return value with the appropriate type. Although use of Clng() function would make it explicit what the coder had in mind.

A much more useful benefit of declaring variable type, especially objects, is intellisense