• September 24, 2021


Bring Technology Ideas with you

 VBA- Variable Types, Declare and Data Types in Microsoft Access

What is VBA Stand For?

There are many development programming which is used their environment to develop the applications such as web, mobile and even desktop application. So what is VBA and why it is still important for developers? VBA is standing for Visual Basic Application. You can find VBA code in any Microsoft Office Applications. The most usage of VBA application to work within nowadays business is Microsoft Excel Application and Microsoft Access Application.

What are the Variables in VBA?

Variables are being used in every programming language whether it is for desktop applications, web applications or even mobile applications. Those variables are performing specific tasks by the developers. Some programming may declare the variables with holding values which is called implicitly. For example, string name=”David”;. The other may declare only the variable name to use get input from users or other methods which is called explicitly. For example, string name;. Variable is specifically use to identify or store the value in the system memory and perform as specific task by the programming. VBA programming is also similar to other programming which its own datatypes such as string, integer, double, etc.

How To Declare Variable in VBA?

A little bit similar to Visual Basic.Net to declare the variable in VBA, it needs to use the Keyword Dim with following the Variable Name then using AS with data type without semicolon . As the example below:

Sub Connection()
Dim con As String
End Sub

Some of Data Types in Microsoft Access

As the real practice, we can classify the data type into two categories which is called Numeric Data Type and Non-Numeric Data Type.

Numeric Data Type and Its Values To Be Stored

TypeStorageRange of Values
Byte1 byte0 to 255
Integer2 bytes-32,768 to 32,767
Long4 bytes-2,147,483,648 to 2,147,483,648
Single4 bytes-3.402823E+38 to -1.401298E-45 for negative values 1.401298E-45 to 3.402823E+38 for positive values.
Double8 bytes-1.79769313486232e+308 to -4.94065645841247E-324 for negative values 4.94065645841247E-324 to 1.79769313486232e+308 for positive values.
Currency8 bytes-922,337,203,685,477.5808 to 922,337,203,685,477.5807
Decimal12 bytes+/- 79,228,162,514,264,337,593,543,950,335 if no decimal is use +/- 7.9228162514264337593543950335 (28 decimal places)
Source From garru99.com

Non-Numeric Data Type and Its Values To Be Stored

Data TypeBytes UsedRange of Values
String (fixed Length)Length of string1 to 65,400 characters
String (Variable Length)Length + 10 bytes0 to 2 billion characters
Boolean2 bytesTrue or False
Date8 bytesJanuary 1, 100 to December 31, 9999
Object4 bytesAny embedded object
Variant(numeric)16 bytesAny value as large as Double
Variant(text)Length+22 bytesSame as variable-length string
Source From garru99.com

How To Write VBA in Microsoft Access

By going to your Form Object, drag and drop the control you want to write the command in VBA. Let’s use Command Button. Please drag into your Form, then Click on Cancel.

    Command Button in Wizard Mode

    Then on your Form, please rename as “Admin Message” on the button then Right Click select Build Event —> select Code Builder

      Code Builder to Write VBA programming

      Then on Private Sub Command2_Click() you can start write your programming by declare your variable. For example

      Option Compare Database
      Private Sub Command2_Click()
      Dim Admin_Message As String
      Admin_Message = "Welcome to Phsarit.org"
      MsgBox (Admin_Message), vbInformation
      End Sub

      After you have declared as the example the above, please go back to your form design then select Form View. Click on your Command Button, the you should see the result as below:

        Form View in your Microsoft Access
          The result after clicking on the button

          Related post