VBA Declaring Variables Type Hints

Help us to keep this website almost Ad Free! It takes only 10 seconds of your time:
> Step 1: Go view our video on YouTube: EF Core Bulk Insert
> Step 2: And Like the video. BONUS: You can also share it!

Example

Type Hints are heavily discouraged. They exist and are documented here for historical and backward-compatibility reasons. You should use the As [DataType] syntax instead.

Public Sub ExampleDeclaration()

        Dim someInteger% '% Equivalent to "As Integer"
        Dim someLong&    '& Equivalent to "As Long"
        Dim someDecimal@ '@ Equivalent to "As Currency"
        Dim someSingle!  '! Equivalent to "As Single"
        Dim someDouble#  '# Equivalent to "As Double"
        Dim someString$  '$ Equivalent to "As String"

        Dim someLongLong^  '^ Equivalent to "As LongLong" in 64-bit VBA hosts
End Sub

Type hints significantly decrease code readability and encourage a legacy Hungarian Notation which also hinders readability:

Dim strFile$
Dim iFile%

Instead, declare variables closer to their usage and name things for what they're used, not after their type:

Dim path As String
Dim handle As Integer

Type hints can also be used on literals, to enforce a specific type. By default, a numeric literal smaller than 32,768 will be interpreted as an Integer literal, but with a type hint you can control that:

Dim foo 'implicit Variant
foo = 42& ' foo is now a Long
foo = 42# ' foo is now a Double
Debug.Print TypeName(42!) ' prints "Single"

Type hints are usually not needed on literals, because they would be assigned to a variable declared with an explicit type, or implicitly converted to the appropriate type when passed as parameters. Implicit conversions can be avoided using one of the explicit type conversion functions:

'Calls procedure DoSomething and passes a literal 42 as a Long using a type hint
DoSomething 42&

'Calls procedure DoSomething and passes a literal 42 explicitly converted to a Long
DoSomething CLng(42)

String-returning built-in functions

The majority of the built-in functions that handle strings come in two versions: A loosely typed version that returns a Variant, and a strongly typed version (ending with $) that returns a String. Unless you are assigning the return value to a Variant, you should prefer the version that returns a String - otherwise there is an implicit conversion of the return value.

Debug.Print Left(foo, 2)  'Left returns a Variant
Debug.Print Left$(foo, 2) 'Left$ returns a String

These functions are:

  • VBA.Conversion.Error -> VBA.Conversion.Error$
  • VBA.Conversion.Hex -> VBA.Conversion.Hex$
  • VBA.Conversion.Oct -> VBA.Conversion.Oct$
  • VBA.Conversion.Str -> VBA.Conversion.Str$
  • VBA.FileSystem.CurDir -> VBA.FileSystem.CurDir$
  • VBA.[_HiddenModule].Input -> VBA.[_HiddenModule].Input$
  • VBA.[_HiddenModule].InputB -> VBA.[_HiddenModule].InputB$
  • VBA.Interaction.Command -> VBA.Interaction.Command$
  • VBA.Interaction.Environ -> VBA.Interaction.Environ$
  • VBA.Strings.Chr -> VBA.Strings.Chr$
  • VBA.Strings.ChrB -> VBA.Strings.ChrB$
  • VBA.Strings.ChrW -> VBA.Strings.ChrW$
  • VBA.Strings.Format -> VBA.Strings.Format$
  • VBA.Strings.LCase -> VBA.Strings.LCase$
  • VBA.Strings.Left -> VBA.Strings.Left$
  • VBA.Strings.LeftB -> VBA.Strings.LeftB$
  • VBA.Strings.LTtrim -> VBA.Strings.LTrim$
  • VBA.Strings.Mid -> VBA.Strings.Mid$
  • VBA.Strings.MidB -> VBA.Strings.MidB$
  • VBA.Strings.Right -> VBA.Strings.Right$
  • VBA.Strings.RightB -> VBA.Strings.RightB$
  • VBA.Strings.RTrim -> VBA.Strings.RTrim$
  • VBA.Strings.Space -> VBA.Strings.Space$
  • VBA.Strings.Str -> VBA.Strings.Str$
  • VBA.Strings.String -> VBA.Strings.String$
  • VBA.Strings.Trim -> VBA.Strings.Trim$
  • VBA.Strings.UCase -> VBA.Strings.UCase$

Note that these are function aliases, not quite type hints. The Left function corresponds to the hidden B_Var_Left function, while the Left$ version corresponds to the hidden B_Str_Left function.

In very early versions of VBA the $ sign isn't an allowed character and the function name had to be enclosed in square brackets. In Word Basic, there were many, many more functions that returned strings that ended in $.



Got any VBA Question?