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.

**Fig 1: xlfAnimation -** enter 25/12/2015, complete entry and format reverses to 12/25/2015

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

xlf-demo-vars-1 — **Fig 2: Immediate Window** - view of output for Code 1

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