Why integer variable is storing string values in VBA Excel

Question

I have declared a variable of integer type. VBA Excel is not restricting to store only integers in it. It accepts String Values (such as "10") and correctly displays 10 in message box. I want a solution in which integer variable can only store integer values. A sample code is

    Option Explicit

    Sub Button1_Click()
        Dim a As Integer
        a = "10"
        MsgBox (a)
   End Sub

Here 'a' is declared as integer and "10" has been stored in 'a' without error. Is there a way that it shows error on each string assignment such as in other programming languages.

it know to read the value within the ", try putting a = "string1" and see what happens... — Shai Rado
– Shai Rado, Commented Nov 29, 2016 at 7:39
This is called implicit type conversion. The "10" is implicit converted to integer 10. So the integer variable does not store a string but a implicit converted integer. Not only VBA will do this but also some other programming languages. As far as I know, you cannot avoid this in VBA. — Axel Richter
– Axel Richter, Commented Nov 29, 2016 at 8:01

gembird · Accepted Answer · 2016-11-29 09:43:17Z

One quick idea could be to store the new value in a variable of type Variant and prior to assignment to the Integer variable check its sub type.

 Sub Button1_Click()
    Dim newIntegerValue As Variant
    newIntegerValue = "10"

    If VarType(newIntegerValue) = vbString Then
        Err.Raise 123, "Button1_Click", "Invalid cast"
    End If

    Dim a As Integer
    a = newIntegerValue
End Sub

This functionality could be wrapped in a class named e.g. StrictInteger.

StrictInteger class module

Option Explicit

Private m_value As Integer
Private m_hasValue As Boolean
Private Const invalidValueErrorNumber As Long = vbObjectError + 600

Private Sub Class_Initialize()
    m_value = 0
    m_hasValue = False
End Sub

Public Function Assign(ByVal newIntegerValue As Variant)
    ' TODO: check with next variant sub types
    If VarType(newIntegerValue) = vbString Or _
        VarType(newIntegerValue) = vbBoolean Then
        Err.Raise invalidValueErrorNumber, _
            "StrictInteger::Initialize", _
            "Value initialization failed"
    End If
    On Error GoTo Err_Initialize
    m_value = newIntegerValue
    m_hasValue = True
    Exit Function
Err_Initialize:
    m_hasValue = False
    Err.Raise Err.Number, "StrictInteger::Initialize", Err.Description
End Function

Public Property Get Value() As Integer
    If m_hasValue Then
        Value = m_value
        Exit Property
    End If
    Err.Raise invalidValueErrorNumber, _
        "StrictInteger::Value", _
        "Valid value is not available"
End Property

Standard module test

Sub Test()
    On Error GoTo Err_Test
    Dim strictInt As StrictInteger
    Set strictInt = New StrictInteger
    strictInt.Assign "10"
    strictInt.Assign "ABC"
    strictInt.Assign ActiveSheet
    strictInt.Assign Now
    strictInt.Assign True
    strictInt.Assign False
    strictInt.Assign 10
    MsgBox strictInt.Value
    Exit Sub
Err_Test:
    MsgBox Err.Number & ". " & Err.Description, vbCritical, "Error"
    Resume Next
End Sub

Collectives™ on Stack Overflow

Why integer variable is storing string values in VBA Excel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related