Can't convert string to an integer in VBA or by Excel formulas (failure to convert or wrong number even if string is all numeric)

Question

I have a string inside a cell: 105501008962100001

Cell A10 contains this text.

When I run ISTEXT(A10) it returns TRUE.

I used VALUE(A10) but it returned 105501008962100000

When I multiply A10 * 1, I get 105501008962100000 as well.

I have also created a VBA function to convert to integer but when I create checkpoints, it creates an error saying that there is a failure to convert to integer.

I do not understand why I cannot convert this string inside this cell to an integer when it is composed entirely of numbers. This number is imported from a CSV file.

Function ConvertToInteger(v1 As Variant) As Integer
On Error GoTo 100:

    'MsgBox Len(v1)

    Dim tempArray As Variant
    Dim arraySize As Integer
    arraySize = (Len(v1) - 1)
    ReDim tempArray(arraySize)

    Dim tempText As String
    Dim finalNumber As Integer
    Dim i As Integer

    For i = 1 To Len(v1)
        tempArray(i - 1) = CStr(Mid(v1, i, 1))
    Next

    tempText = Join(tempArray, "")
    tempText = CStr(tempText)

    'MsgBox tempText

     finalNumber = CInt(tempText)

     MsgBox finalNumber

    'ConvertToInteger = finalNumber

Exit Function
100:
    MsgBox "Failed to convert """ & v1 & """ to an integer.", , "Aborting - Failed Conversion"
    End
End Function

I made modifications to the code but still get the 0 at the end not 1 in the number:

Function ConvertToInteger(v1 As Variant) As Double

    Dim tempArray As Variant
    Dim arraySize As Double
    arraySize = (Len(v1) - 1)
    ReDim tempArray(arraySize)

    Dim tempText As String
    Dim finalNumber As Double
    Dim i As Integer

    For i = 1 To Len(v1)
        tempArray(i - 1) = CStr(Mid(v1, i, 1))
    Next

    tempText = Join(tempArray, "")
    tempText = CStr(tempText)

    finalNumber = CDbl(tempText)

    MsgBox finalNumber

    'I get an error here when assigning finalNumber to ConvertToInteger
    'ConvertToInteger = finalNumber

End Function

Answer 1

You can identify this error (overflow) in your error-handling block using the Description property of the intrinsic Err object:

100:
MsgBox "Failed to convert """ & v1 & """ to an integer." & vbCrLf & Err.Description, , "Aborting - Failed Conversion" 
End

As you have it implemented, the error-handler works, but does not display the useful information for debugging the problem.

Once you have identified as a overflow error, note that the value 105501008962100001 exceeds the size allowed for Integer or Long data type. Declare it as a Double instead.

Dim tempArray As Variant
Dim arraySize As Double
arraySize = (Len(v1) - 1)
ReDim tempArray(arraySize)

Note limitations in Excel of using large numbers. Your data may exceed this.

For handling very large numbers, see:

Handling numbers larger than Long in VBA

And subsequently:

http://tushar-mehta.com/misc_tutorials/project_euler/LargeNumberArithmetic.htm

Here is a simple example of adding two large numbers. You will be able to use the cLarge class to perform arithmetic operations against these "numbers" (which are actually strings). Your functions will need to change type declaration from Long/Double to String.

In a standard module, do:

Sub foo()
Dim lrg As New cLarge

MsgBox lrg.LargeAdd("105501008962100001", "205501231962100003")
End Sub

Create a class module named cLarge and in that module, put the following code:

'### Class module for adding very large (> Decimal precision) values
'    http://tushar-mehta.com/misc_tutorials/project_euler/LargeNumberArithmetic.htm
'
'    Modified by David Zemens, 9 December 2015
Option Explicit

Public cDecMax As Variant, cDecMaxLen As Integer, cSqrDecMaxLen As Integer
Private pVal As String

Public Sub Class_Initialize()
    Static Initialized As Boolean
    If Initialized Then Exit Sub
    Initialized = True
    cDecMax = _
        CDec(Replace("79,228,162,514,264,337,593,543,950,335", ",", ""))
            'this is 2^96-1, the limit on Decimal precision data in Excel/VBA
    cDecMaxLen = Len(cDecMax) - 1
    cSqrDecMaxLen = cDecMaxLen \ 2
End Sub


Function Ceil(X As Single) As Long
    If X < 0 Then Ceil = Fix(X) Else Ceil = -Int(-X)
End Function

Private Function addByParts(ByVal Nbr1 As String, ByVal Nbr2 As String) _
    As String
    Dim NbrChunks As Integer
    If Len(Nbr1) > Len(Nbr2) Then _
        Nbr2 = String(Len(Nbr1) - Len(Nbr2), "0") & Nbr2 _
    Else _
        Nbr1 = String(Len(Nbr2) - Len(Nbr1), "0") & Nbr1
    NbrChunks = Ceil(Len(Nbr1) / cDecMaxLen)
    Dim I As Integer, OverflowDigit As String, Rslt As String
    OverflowDigit = "0"
    For I = NbrChunks - 1 To 0 Step -1
        Dim Nbr1Part As String
        Nbr1Part = Mid(Nbr1, I * cDecMaxLen + 1, cDecMaxLen)
        Rslt = CStr(CDec(Nbr1Part) _
            + CDec(Mid(Nbr2, I * cDecMaxLen + 1, cDecMaxLen)) _
            + CDec(OverflowDigit))
        If Len(Rslt) < Len(Nbr1Part) Then
            Rslt = String(Len(Nbr1Part) - Len(Rslt), "0") & Rslt
            OverflowDigit = "0"
        ElseIf I = 0 Then
        ElseIf Len(Rslt) > Len(Nbr1Part) Then
            OverflowDigit = Left(Rslt, 1): Rslt = Right(Rslt, Len(Rslt) - 1)
        Else
            OverflowDigit = "0"
            End If
        addByParts = Rslt & addByParts
        Next I
End Function

Function LargeAdd(ByVal Nbr1 As String, ByVal Nbr2 As String) As String
    'Initialize
    If Len(Nbr1) <= cDecMaxLen And Len(Nbr2) <= cDecMaxLen Then
        LargeAdd = CStr(CDec(Nbr1) + CDec(Nbr2))
        Exit Function
        End If
    If Len(Nbr1) > cDecMaxLen Then LargeAdd = addByParts(Nbr1, Nbr2) _
    Else LargeAdd = addByParts(Nbr2, Nbr1)
End Function

So, long story short, you can't actually use these "numbers" and you strictly cannot "convert" them to Integer/Long values. You need to use a custom class implementation to perform the maths against the string values.