Dim statement for dummies (1 Viewer)

[mamradzelvy]

Hello,
I'm trying to understand the Dim statement in vba, however I don't know whether it's the lack of english skills or what, but I just don't understand what it does based on explanations found on support.office.com.
Could anybody explain this to me as you would to a dumb person? (because i apparently am one)
Maybe based on this example, which is what I was trying to grasp in the first place:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim Answer As Integer
If Me.Dirty = True Then
    Dim Response As Integer
    ' Displays a message box with the yes and no options.
    Response = MsgBox(Prompt:="Do you wish to save this data?", Buttons:=vbYesNo)
    ' If statement to check if the yes button was selected.
    If Response = vbNo Then
        DoCmd.RunCommand acCmdUndo
        DoCmd.Close
    End If
Else
    ' The yes button was selected.
    DoCmd.Close
    Globals.Logging "dbTabule Record Edit"
  
  End If

End Sub

 

[arnelgp]

Dim simply means you are Declaring a variable.

 

[Minty]

Dim is short for Dimension and it allows you to declare ( set up) a variable name and it's data type.

By default if you omit the data type you are declaring a Variant.
You should always Dim your variables and an explicit data type, as not doing so can and will lead to problems.

The normal way to do this is at the start each code section, and you should also at the top of every code module add the command

Option Compare ' This is always there Option Explicit ' This SHOULD always be here

To force this to happen automatically you can set an option in the VBA editor options;

This will help identify typing error in variable names etc.

 

[CJ_London]

Variables can be defined with different characteristics

Dim - dimension
Const - constant

are the two most common ones

there are a number of others - this link is for VB rather than VBA so has a few extra

Dim statement - Visual Basic

Learn more about: Dim statement (Visual Basic)

docs.microsoft.com

 

[mamradzelvy]

Thank you!
So in my code example, the Dim Response As Integer declares "Response" as a number value?

 

[bob fitz]

Thank you!
So in my code example, the Dim Response As Integer declares "Response" as a number value?

Correct. A whole number. No decimal part.

 

[CJ_London]

and values limited to the range -32,768 to +32,767

For Long datatype, the range is -2,147,483,648 to +2,147,483,647

 

[mamradzelvy]

Correct. A whole number. No decimal part.

Ok but for what purpose? Is the number then used in the procedure somehow? I don't see it, i mean, i don't see the meaning behind declaring "response" being a number, when i then clearly use the word response.. I apologise if this sounds stupid, but i honestly don't get the point of this.

I keep repeating myself on most of my posts, but I'm still new to all of this, thank you for understanding.

 

[isladogs]

If you fail to declare a variable, you will get a code error when it is used.
You need to tell Access exactly what e.g. Answer is otherwise it will throw a 'wobbly'

 

[arnelgp]

for the purpose of memory allocation.
when you use Dim, Access prepares how much byte it will reserved on memory for that var.
when you Dimmed a variable you cannot change it's type any more.

using Dim var, without explicitly specifying it's type, it will default to a Variant.
"Variant variables with numbers require 16 bytes of memory.
Variables of the Variant data type with characters usually require 22 bytes of memory plus the memory required by the string."
-https://bettersolutions.com/vba/data-types/variant-data-type.htm

 

[Minty]

Ok but for what purpose? Is the number then used in the procedure somehow?

Yes, your number variable, (in this case Response) is set to the answer provided by the message box click.
It is holding the answer ready for you to use later. You basically are telling Access to set aside a place in memory for that answer, and giving it a meaningful(to you) name.
Access randomly assigned a code (pointer) to your variables (let's say Response = 76238768324) and you had to go away and type that each time your code would get very confusing.

In your example, you are using it only once, immediately so it possibly seems a bit wasteful, but it is the correct structured way to do things.

 

[CJ_London]

Is the number then used in the procedure somehow?

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim Answer As Integer
If Me.Dirty = True Then
    Dim Response As Integer 'used to receive the value returned by msgbox
    ' Displays a message box with the yes and no options.
    Response = MsgBox(Prompt:="Do you wish to save this data?", Buttons:=vbYesNo)'vbYesNo, vbYes, vbNo are all system constant names for numbers of a specific value
    ' If statement to check if the yes button was selected.
    If Response = vbNo Then
        DoCmd.RunCommand acCmdUndo
        DoCmd.Close
    End If
Else
    ' The yes button was selected.
    DoCmd.Close
    Globals.Logging "dbTabule Record Edit"
 
  End If

End Sub

you could have written your code as

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.Dirty Then
    ' Displays a message box with the yes and no options.
    if MsgBox(Prompt:="Do you wish to save this data?", Buttons:=vbYesNo)=vbNo Then
        DoCmd.RunCommand acCmdUndo
        DoCmd.Close
    End If
Else
    ' The yes button was selected.
    DoCmd.Close
    Globals.Logging "dbTabule Record Edit"
 
  End If

End Sub

Note you have declared Answer as an integer, but never use it, so it can be removed.

Also with simpler code, you might notice that the Else refers to me.Dirty, not Response, which your comment implies it should be

 

[Deleted member 147267]

Dim Response As Integer is kind of a special case. Usually that is for when you're expecting someone to click a message box button and will do something according to their choice. Since a message box has several different configurations that allow some combination of OK, Cancel, Yes, No (there may be more) each one of those choices represents a number that the message box function can return. Those numbers are of the data type called Integer (i.e. they have been defined as such by Access). So when you call that function as in
Response = MsgBox(message, etc goes here)
If Response = 6 do this
and so on
you are assigning the choice to Result.

Hopefully this comment won't confuse the issue, but many people prefer to use vbConstants instead of trying to remember what 6 or 7 represents, so you can substitute vbYes (or vbNo, etc) and the system will equate that with the proper number. Thus you could also write
If Response = vbYes
and since vbYes represents (equates to) a number which has been defined as an integer and you have dimensioned your variable as an integer, all should be copacetic.

One last thing if you're interested: The message box function call must be contained in ( ) if it is to return a value to a variable. If not, you don't. So
Msgbox "hello" is OK <- because you are not returning a value
Result = Msgbox "hello" is not OK