Use the Dim Statement to Declare the Variable as a Variant and Then Try the Operation Again

A variable is a named storage location in which yous can store information while your macro/programme is running.  Each variable has a unique name where the proper name can identify what type of variable it is past using standard naming conventions.

A variable tin can contain dissimilar types of data – text, dates, or numbers, for example.  Variable names must begin with an alphabetic character, cannot be longer than 255 characters, and cannot incorporate an embedded full-stop.  The names must also be unique within the scope of the variable.

Scope refers to the availability of a variable, constant, or procedure for use past some other procedure. There are three scoping levels: procedure-level, private module-level, and public module-level.   Nosotros are going to focus on the procedure-level or private module-level variables – the public module-level variable uses a GLOBAL variable. Y'all can read more than almost that in our article on global variables.

Declaring a Variable

When you declare procedure-level or private module-level variables, yous employ a Dim statement. A announcement statement tin be placed inside a procedure to create a procedure-level variable, or it may exist placed at the top of a module, in the Declarations section, to create a module-level variable.

To declare a variable, you must offset make up one's mind what the variable is going to be used for (what information the variable is going to shop), so apply the relevant naming convention for the variable.  This makes it easy to see at a glance the type of data the variable is going to store.

For example:

To declare a string variable (ie one that stores text) within a procedure, y'all tin can declare the variable as follows:

Sub DataInput() 	Dim StrName Equally String  Terminate Sub        

You use the As keyword to specify the data type for the variable equally shown in a higher place.

Naming Conventions

Typically the commencement of a variable proper noun volition define what blazon of data is going to be stored in that variable.

For example

strName or sName – will store a string

intAmount or iAmt – volition store an integer

objWord or oWord – will shop an object

curAmt or cAmt – will store currency

And so on..

When you declare a variable, the Visual Basic AutoComplete will select the variable type from the drop down list available:

Pressing enter or the space bar will complete the text for you.

Data Types

A variable can incorporate data from whatever supported data blazon.  Visual Basic supports:

Information Type	Description	Range
Byte	1-byte binary information	0-255
Integer	2-byte integer	-32,768 to 32,767
Long	four-byte integer	-2,147,483,648 to 2,147,483,647
Single	4-byte floating-bespeak number	-3.402823E38 to –1.401298E-45 (negative values) one.401298E-45 to three.402823E38 (positive values)
Double	viii-goodbye floating-point number	-ane.79769313486231E308 to -4.94065645841247E-324 (negative values) iv.94065675841247E-324 to 1.79769313486231E308 (positive values)
Currency	8-byte number with fixed decimal point	-922,337,203,685,477.5808 to 922,337,203,685,477.5807
String	Cord of characters	Zero to approximately two billion characters
Variant	Date/time, floating betoken number, integer, string or object.  16 bytes plus 1 byte for each character if the value is a string value	Date values: January 1, 100 to December 31, 9999 Numeric values: same range as Double String values: same range as String Can also contain Error or Zero values
Boolean	2 Bytes	True or Fake
Appointment	viii-byte appointment and time	i January 100 to 31 December 9999
Object	iv-bytes	Any object reference

It is non mandatory to declare variables before they are used in a procedure, but it is good programming practice to do and then to prevent errors in your code occurring.  To make variable annunciation forced you can use the Option Explicit argument in the Declarations expanse of a module or class.  If yous take this choice set, then if y'all attempt and use a variable that has not been alleged, an error volition occur.

You can have Option Explicit automatically added to the beginning of each new module by setting it in the Options of Visual Basic.

Click on the Tools menu, Options and and then select the Editor tab.

Make sure Require Variable Declaration is switched on, and click OK.

Now when you create a new module or user form, Option Explicit will appear in the Declarations area of the module or form.

A userform code window with Option Explicit
switched on

A new module with Option Explicit
switched on

Understanding Scope

Telescopic refers to the availability of a variable, constant, or procedure for employ past another procedure. In that location are iii scoping levels: process-level, individual module-level, and public module-level.

You determine the scope of a variable when you declare it. Information technology'southward a good idea to declare all variables explicitly to avoid naming-disharmonize errors between variables with dissimilar scopes.

If a variable is declared at procedure level, the variable can be used only in that procedure. If the variable is declared in the Declarations section of the module, the variable is available to all procedures within the module, but non to procedures in other modules in the project. To brand this variable available to all procedures in the project, you would demand to precede it with the Public statement (see article on global variables).

For example:

This variable is alleged within the DataInput process.  Information technology has therefore been declared at procedure level and is only bachelor inside this procedure.

A variable defined within a procedure is not visible exterior that procedure :

This variable has been declared in the Declarations area of the module, and is therefore available to all procedures inside this module.

You lot can also apply the Private statement to declare private module-level variables.

Eg: Individual StrName Equally String

Private variables can exist used only past procedures in the same module.

Annotation: When used at the module level, the Dim statement is equivalent to the Individual statement. Y'all might want to employ the Private statement to brand your code easier to read and interpret.

HOT TIP: The Public and Private keywords do non only refer to variables – they refer to constants and procedures too.  All procedures are public by default, except for event procedures.

When Visual Basic creates an result procedure (for example the click event), the Private keyword is automatically inserted before the procedure declaration. For all other procedures, you must explicitly declare the procedure with the Individual keyword if you do non want it to be public.

Using a Variable in an Example

Using Give-and-take VBA, nosotros may have created a letterhead form as follows:

We then need to write the code backside the OK button to populate the form.  We are going to declare 4 module level variables beneath the Selection Explicit Statement, and then nosotros are going to populate those variables using the OK click event procedure. This procedure will run when the OK button is clicked.

One time the variables are populated, the routine PopulateLetter volition push the information that is then contained in the variables back into the Give-and-take letterhead template that nosotros would accept set up for this purpose.

Option Explicit Dim StrEnding As String  Dim StrPost Equally String  Dim strAddress As Cord Dim strDear As String  Private Sub cmdok_Click() 'Populate the variables     StrAddress = Me.txtTo     If Me.chkFax = True And then         StrPost = "BY FAX " & Me.txtFax     ElseIf Me.chkHand = True Then         StrPost = "By Paw"     ElseIf Me.chkPost = True Then         StrPost = "BY EMAIL"     End If     If Me.chkFaith = True Then         StrEnding = "Yours faithfully"     ElseIf Me.chksincere = True Then         StrEnding = "Yours sincerely"     Else         StrEnding = ""     End If     StrAttention = Me.txtAttn     StrDear = Me.txtDear 'run the routine to populate the letter     PopulateLetter Me     Unload Me End Sub        

Therefore, as you tin see, variables are a vital part of VBA lawmaking, and are used within all programming languages.  They give you the flexibility to conduct information through your lawmaking, from one module to another, or from a form to a module, then back to your workbook or document.

The scope of the variable is vital in the function that the variable will play in your code.  As nosotros take learnt, the Dim argument is the declaration statement for a private variable and is used within your course or your private module level VBA code.

robinsonsentort1955.blogspot.com

Source: https://software-solutions-online.com/vba-dim-statement/

Share this post