VBA Single Data Type (Dim Variable)

Single Variable Type

The VBA Single data type is used to store numbers that require decimal places.  It can store from -3.4028235E+38 through -1.401298E-45 for negative values and from 1.401298E-45 through 3.4028235E+38 for positive values.

To declare an Single variable, you use the Dim Statement (short for Dimension):

Dim sngA as Single 

Then, to assign a value to a variable, simply use the equal sign:

sngA = 3658.25

Putting this in a procedure looks like this:

Sub sngExample()
'declare the double variable
   Dim sngA as Single
'populate the double variable
   sngA = 3658.25
'show the message box
   MsgBox sngA 
End Sub

If you run the code above, the following message box will be shown.

Double Data Type

The Double data type is just a longer version of the Single data type.  Due to this fact, it can effect the rounding when used in a procedure as the Single data type will round to 4 decimal places, while the Double data Type will round to 12 decimal places.  If you need more than 4 decimal places, you can use the Double data type.

Int or Long Data Types

If you do not need a decimal place, you can use either the Int data type or the Long data type.

Dim intA as Integer

Dim lngB as Long

Declare Single Variable at Module or Global Level

In the previous examples, we’ve declared the Single variable within a procedure. Variables declared with a procedure can only be used within that procedure.

Instead, you can declare Single variables at the module or global level.

Module Level

Module level variables are declared at the top of code modules with the Dim statement.

These variables can be used with any procedure in that code module.

Global Level

Global level variables are also declare at the top of code modules. However, instead of using the Dim statement, use the Public statement to indicate that the Single variable is available to be used throughout your VBA Project.

Public SngA as Single

If you were to declare the Single variable at a module level and then try to use it in a different module, an error would occur.

However, if you had used the Public keyword to declare the Single variable, the error would not occur and the procedure would run perfectly.

Format Single Stored as String

There may be a time where you wish to format a single data type to a string – for example you might want to display a currency symbol and round the number to 2 decimal places.

To achieve this, you use the Format function.

The  following procedure

Sub TestSingleToCurrencyString()
'declare the string variable
Dim strMoney As String
'declare the single and populate the value
Dim sngValue As Single
sngValue  = 44055.256
'convert the single to a string with a currency symbol with 2 decimal places
strMoney = Format(sngValue , "$#,##0.00")
'view the result
MsgBox strMoney
End Sub

would return this result:

Similarly, you may want to display a number as a formatted phone number.

This procedure:

Sub TestSingleToPhone()
'declare the string variable
Dim strPhone As String
'declare the single and populate the value
Dim sglValue As single 
sglValue = 555968541
'convert the single to a string with a currency symbol
strPhone = Format(sglValue , "(000)-000 0000")
'view the result
MsgBox strPhone
End Sub

would return this result: