Convert Numeric String to Integer

[GeeWhiz7]

Hi,
I have an array in VBA that is filled with string values representing numbers.
So, one of them might look like a number, but the type in a locals window would look like

Value
"128371"
Type
Variant/String

I would like to convert this to an integer before writing out the array to a worksheet, but can't quite get it to work in array.
I have tried CInt(x), but get a type mismatch error on the conversion line, presumably because it's missing quotes.

For example, if x(1)=128371 as type String,
using CInt(x) or CInt(""" & x & """) returns the type mismatch error.

Not sure how to keep it in the vba/array and make it work.

 

[dmt32]

Hi,
try it like this

CLng(x(1))

Suggest use Clng type conversion function to avoid overflow errors

Dave

 

[GeeWhiz7]

Hi,
try it like this

CLng(x(1))

Suggest use Clng type conversion function to avoid overflow errors

Dave

Hi Dave, it does work if I test a single input, but also shows me another problem I have. I get an overflow error. I changed to CLng, but it persists.
The snip of code pulling from one array -dat- into another -datconv- looks like this. The error is on the CLng line.

The numbers coming in are unix time stamp milliseconds so extremely long. Perhaps there isn't a way to do this unless I parse the unix numbers to remove 0's from the string.

For i = 2 To UBound(dat)
datconv=CLng(dat(i))
Next i

 

[dmt32]

Hi,
I can only go by the limited example you posted which will corece your string number to a long data type.
If the issue is really more complex than your original post you really should share with forum all the code from the outset.

Dave

 

[GeeWhiz7]

Hi,
I can only go by the limited example you posted which will corece your string number to a long data type.
If the issue is really more complex than your original post you really should share with forum all the code from the outset.

Dav

Hi,
I can only go by the limited example you posted which will corece your string number to a long data type.
If the issue is really more complex than your original post you really should share with forum all the code from the outset.

Dave

Sorry, was trying to simplify...which made it more complex.

Here is the code:

Sub Convert()

Dim inarr() As Variant
Dim convarr() As Variant

Dim Target As Range
 
    inarr = Worksheets("Sheet1").Range("A2").CurrentRegion.Value
 
    ReDim convarr(1 To UBound(inarr),1)
 
        For i = 1 To UBound(inarr)
            convarr(i,1) = inarr(i,1) '#This works
            'convarr(i) = CLng(inarr(i)) '#This gets cannot assign to array error
            'convarr(i)= CLng(Right(inarr(i), Len(inarr(i) - 4))) 'This was to try to clip some 0's off, doesn't work
            Debug.Print (convarr(i, 1))
        Next i
 
    Set Target = Worksheets(1).Range("C1")
        Target.CurrentRegion.Clear
     

    MsgBox ""
End Sub

Here is a mini-sheet with the input string/dates in Unix MS

Book1
	A
2	1.62693E+12
3	1.62701E+12
4	1.62727E+12
5	1.62736E+12
6	1.62744E+12
7	1.62753E+12
8	1.62762E+12
Sheet1

 

[rlv01]

Hi Dave, it does work if I test a single input, but also shows me another problem I have. I get an overflow error. I changed to CLng, but it persists.
The snip of code pulling from one array -dat- into another -datconv- looks like this. The error is on the CLng line.

The numbers coming in are unix time stamp milliseconds so extremely long. Perhaps there isn't a way to do this unless I parse the unix numbers to remove 0's from the string.

For i = 2 To UBound(dat)
datconv=CLng(dat(i))
Next i

Given the length of your input string ("1626926400000"), I think you can use the older "Val" function to avoid overflow. That is:

    For I = 2 To UBound(dat)
        datconv = Val(dat(I))
    Next I

 

[dmt32]

Sorry, was trying to simplify...which made it more complex.

no worries, nearly always best to give forum full picture of what you are doing
try @rlv01 suggestion & see if resolves your issue

Dave

 

[GeeWhiz7]

no worries, nearly always best to give forum full picture of what you are doing
try @rlv01 suggestion & see if resolves your issue

Dave

Thanks guys, Val ended up working. Putting the whole code segment together to take UNIX MS to Date is below if its useful to the next searcher.

Sub Convert()

Dim inarr() As Variant
Dim convarr() As Variant
Dim i As Integer
Dim lRow As Integer
Dim ws As Worksheet
       
Set ws = Worksheets("Sheet1")
   
    inarr = ws.Range("A2").CurrentRegion.Value
   
    ReDim convarr(1 To UBound(inarr), 1 To 1)
   
        For i = 1 To UBound(inarr)
            convarr(i, 1) = (Val(inarr(i, 1)) / 86400000) + 25569
        Next i
     
    With ws
        [c2].Resize(UBound(convarr) - LBound(convarr) + 1) = convarr
        [c2].End(xlDown).NumberFormat = "mmmm-dd-yyyy"
    End With

End Sub