VBA variables (1)
This module includes examples of the Declaration statements for common data type:
- Double - a double precision floating point number
- Long - a large whole number
- String - text
- Boolean - True / False
- Date - date (integer digits, to the left of the decimal place) and time (fractional digits, to the right of the decimal place)
Variable declaration and assignment
Code 1: Sub
DemoVars
- demonstrate variable declaration and assignment - send output to the Immediate Window
Option Explicit Sub DemoVars() '' Declare variables with the Dim statement Dim Price '' Price has no explicit type - thus it is of type Variant Dim StkPrice As Double Dim Counter As Long Dim StkName As String Dim OptType As Boolean Dim TransDate As Date, TransTime As Date '' Assign values Debug.Print vbNewLine & "===========================" '' To Immediate Window Debug.Print "Print time: " & Time '' Print system time Price = 12.34 Debug.Print "1. Price: " & Price & vbNewLine StkPrice = 12# '' Entered as 12.00. VBA converts to 12# Debug.Print "2. StkPrice: " & StkPrice & vbNewLine StkPrice = StkPrice * (1 + 0.1) Debug.Print "3. StkPrice * (1 + 0.1): " & StkPrice & vbNewLine Counter = 1234567890 + 9 Debug.Print "4. Counter: " & Counter & vbNewLine StkName = "DSH.AX" Debug.Print "5. StkName: " & StkName & vbNewLine Debug.Print "6. OptType default: " & OptType OptType = True Debug.Print "7. OptType: " & OptType OptType = Not (OptType) Debug.Print "8. Not (OptType): " & OptType & vbNewLine TransDate = #6/9/2015# '' Enter in mm/dd/yyyy format Debug.Print "9. TransDate: " & TransDate TransDate = DateValue("10-Jun-2015") '' A variable can be assigned a new value Debug.Print "10. DateValue("" 10-Jun-2015""): " & TransDate & vbNewLine '' A string including double quotes Debug.Print "11. " & Format(TransDate, "ddd dd mmm yyyy") & vbNewLine TransTime = #9:00:00 AM# Debug.Print "12. TransTime: " & TransTime Debug.Print "13. TransTime + 1 / 24:" & TransTime + 1 / 24 '' vbNewLine inserts a line break End Sub
Suppose you enter the date 25th December 2015 as a date literal #dd/mm/yyyy# at line 38.
TransDate = #25/12/2015# '' Enter in dd/mm/yyyy format Debug.Print "9. TransDate: " & TransDate ' Immediate Window returns TransDate: 25/12/2015; dd/mm/yyyy
When you complete the statement, the date format switches to the VBE date literal format of #mm/dd/yyyy# (Figure 1). This is not effected by the Windows or Excel regional settings.

The next image is the output of code 1 with Debug.Print sent to the Immediate Window.

- This example was developed in Excel 2013 with VBA 7.1
- Revised: Saturday 25th of February 2023 - 09:37 AM, [Australian Eastern Time (AET)]