VBA CDec Function

This tutorial will demonstrate how to use the CDec VBA function.

CDec Function

VBA CDec Convert Expression to Decimal

The VBA CDec function converts an expression to a decimal data type. The decimal data type is actually a subtype of the variant data type.

Sub CDecExample_1()
MsgBox CDec(12.34000001)
'Result is: 12.34000001
MsgBox CDec(10000000000012.3)
'Result is: 10000000000012.3
MsgBox CDec(-0.00000000000001)
'Result is: -0.00000000000001
MsgBox CDec(-12.34)
'Result is: -12.34
End Sub

VBA CDec Converting Strings to Decimals

The VBA CDec function can be used to convert strings to decimals if the characters in the string have a meaning as numbers.

Sub CDecExample_2()
Dim StrEx As String

StrEx = "112.112112"
MsgBox CDec(StrEx)
'Result is: 112.112112

StrEx = "112.3"
MsgBox CDec(StrEx)
'Result is: 112.3

StrEx = "11,2"
MsgBox CDec(StrEx)
'Result is: 112        , is ignored

StrEx = "$112.07"
MsgBox CDec(StrEx)
'Result is: 112.07        $ is ignored
End Sub

VBA CDec Run-Time Error 13 Type Mismatch

Using VBA CDec function with strings that contain non-numerical characters or characters that don’t have meaning in numerical context will result in a Run-Time error ’13’: Type mismatch.

Sub CDecExample_3()
'The code below will result in an ERROR message
'CDec can't handle non-numerical characters
Dim StrEx As String
StrEx = "Ab13"
MsgBox CDec(StrEx)
End Sub

VBA CDec Run-Time Error 6 Overflow

Using VBA CDec function with strings that result in a value smaller or bigger than the expected decimal will result in a Run-Time error ’6’: Overflow. A Decimal number should be between -7.9 E28 and +7.9 E28.

Sub CDecExample_4()
'The code below will result in an ERROR message
'CDec can handle numbers between -7.9 E28 and +7.9 E28
Dim StrEx As Variant
StrEx = 8E+30
MsgBox CDec(StrEx)
End Sub

VBA CDec Regional Settings

VBA CDec function has different behavior converting strings with comma or dot.  It uses the Regional Settings of the operating system for decimal separator and digit separator.

Sub CDecExample_5()
Dim StrEx As String
StrEx = "1,0000009"
MsgBox CDec(StrEx)
'If Regional settings have , as a grouping separator then
'Result is: 19
'If Regional settings have , as a decimal separator then
'Result is: 1.0000009

StrEx = "1.0000009"
MsgBox CDec(StrEx)
'If Regional settings have . as a grouping separator then
'Result is: 19
'If Regional settings have . as a decimal separator then
'Result is: 1.0000009
End Sub

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!


Learn More!