# Solved: Excel VBA Overflow Error



## engti (Oct 5, 2005)

Hi,

I just wrote an excel vba script to convert long ip addresses to dotted ip addresses. But it doesn't work 

The formulas by themselves work great when entered into an excel cell, but when used in in the vba they give an overflow error.

Here's the script:

Sub conv()
For Each cell In Selection
ip1 = (Int(cell / 256 ^ 3))
ip2 = Int((cell Mod (256 ^ 3)) / (256 ^ 2))
dip = ip1 & "." & ip2
cell.Offset(0, 1) = dip
Next cell
End Sub

The 4th line "ip2=int((cell....." is the one giving the overflow error. 

Do i need to declare something or use another command. 

Could you please help me out, it's very urgent


Thanks in advance,

Robin


----------



## engti (Oct 5, 2005)

Hi,

I forgot to give some of the test inputs.... here they are:

3272130102
3654946314
411052329
995969609
1167033658


Regards,

Robin


----------



## cristobal03 (Aug 5, 2005)

Looks like it's just too much for *mod* to handle. Your 4th and 5th examples process correctly. I imagine *mod* must be limited to the long integer upper boundary of 2.1 billion. You might be able to use a *Decimal* data type and emulate *mod* mathematically, but it'll be slower.

GL

chris.


----------



## cristobal03 (Aug 5, 2005)

[bump]

In fact, I can almost guarantee it's *mod* and the 2.1 billion thing.

In the Immediate window, *?2100000000 / (256 ^ 3)* executes, but *?2200000000 / (256 ^ 3)* overflows.

chris.


----------



## engti (Oct 5, 2005)

Thanks a lot Chris.

Though I must say that this is weird. Cause if you use Mod inline in an excel cell, it handles the calculation perfectly.

I got myself a workaround, but it's ungainly one involving concatenating all the formulas and using it inline.

I've been trying to learn vba from some time, but i keep on getting stuck 

Thanks.

Robin


----------



## cristobal03 (Aug 5, 2005)

[bump 2]

*Variant* seems to work well enough. Try this:


```
Sub conv()
  Dim ip1 As Variant
  Dim ip2 As Variant

  Dim varCellValue  As Variant
  Dim varModEmul    As Variant

  Dim strDip As String

  Const BIG_BIT     As Variant = 16777216
  Const LITTLE_BIT  As Variant = 65536

  For Each cell In Selection
    varCellValue = cell.Value

    ip1 = CInt(varCellValue / BIG_BIT)
    varModEmul = CInt(varCellValue - (ip1 * BIG_BIT))

    ip2 = CInt(varModEmul / LITTLE_BIT)
    strDip = CStr(ip1) & "." & CStr(ip2)
    cell.Offset(0, 1) = strDip
  Next cell
End Sub
```
HTH, but I might be fuzzy here.

chris.


----------



## cristobal03 (Aug 5, 2005)

[bump 3]

Man, my math's off in there somewhere. Gimme a sec.

chris.


----------



## cristobal03 (Aug 5, 2005)

[bump 4]

Sorry, I must've been smoking crack. I don't know why I used *CInt* instead of *Int*. Try this code instead:


```
Sub conv()
  Dim ip1 As Variant
  Dim ip2 As Variant

  Dim varCellValue  As Variant
  Dim varModEmul    As Variant

  Dim strDip As String

  Const BIG_BIT     As Variant = 16777216
  Const LITTLE_BIT  As Variant = 65536

  For Each cell In Selection
    varCellValue = cell.Value

    ip1 = Int(varCellValue / BIG_BIT)
    varModEmul = Int(varCellValue - (ip1 * BIG_BIT))

    ip2 = Int(varModEmul / LITTLE_BIT)
    strDip = CStr(ip1) & "." & CStr(ip2)
    cell.Offset(0, 1) = strDip
  Next cell
End Sub
```
HTH

chris.


----------



## cristobal03 (Aug 5, 2005)

[bump last]

Just to be sure, running those sample numbers produced the following IP pairs:

195.8
217.218
24.128
59.93
69.143​Is that correct?

chris.


----------



## engti (Oct 5, 2005)

Yeah, thats how the first two series goes. Thanks a ton once again.

Now, I'm trying to get the next two series. I'm trying to figure out how to do that myself.

Who knows, A couple hours later I might still be groping in the dark 

Robin


----------



## engti (Oct 5, 2005)

I finally got all my maladies solved.

My input was a string of Ip addresses, and I wanted dotted ip addresses as the output.

So 3272130102 should give me 195.8.190.54




Thanks a lot Chris for everything. I just extended your idea to give me the whole deal.

Here's the VB function

Sub conv()
Dim i As Integer, num As Currency
Dim LIP As String
For Each Cell In Selection
LongIp = Cell
For i = 1 To 4
num = Int(LongIp / 256 ^ (4 - i))
LongIp = LongIp - (num * 256 ^ (4 - i))
If i = 1 Then
LIP = num
Else
LIP = LIP & "." & num
End If
Next
Cell.Offset(0, 1) = LIP
Next
End Sub


Awesome.....and have a great day Chris


----------



## cristobal03 (Aug 5, 2005)

Glad you got it sorted out :up:

You can mark this thread "Solved" using the *Thread Tools* at the top of the page.

chris.


----------

