Overflow on a Long

ccsalway

New Member
Joined
Aug 19, 2020
Messages
4
Office Version
  1. 365
Platform
  1. MacOS
I'm getting an overflow on "CLng(octects(0)) * 16777216" but Longs support much larger numbers I thought?

VBA Code:
Public Function to_number(ByVal sInput As String) As Long
   On Error Resume Next
   
    sDelim = "."
    octects = Split(sInput, sDelim)
    to_number = CLng(octects(0)) * 16777216
    to_number = to_number + CLng(octects(1)) * 65536
    to_number = to_number + CLng(octects(2)) * 256
    to_number = to_number + CLng(octects(3))
    
    If Err.Number <> 0 Then
        MsgBox Err.Description
    End If
End Function

Sub test_to_number()
    Debug.Print to_number("192.168.0.1")
End Sub
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

davesexcel

Well-known Member
Joined
Feb 26, 2006
Messages
1,084
CLng(octects(0)) * 16777216

16777216 x 128 would be the max

I believe long max is 2,147,486,647
 

ccsalway

New Member
Joined
Aug 19, 2020
Messages
4
Office Version
  1. 365
Platform
  1. MacOS
So I see... LongLong doesn't see to work on my MacBook

Long (Long integer)4 bytes-2,147,483,648 to 2,147,483,647
LongLong (LongLong integer)8 bytes-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

Valid on 64-bit platforms only.
 

ccsalway

New Member
Joined
Aug 19, 2020
Messages
4
Office Version
  1. 365
Platform
  1. MacOS
Microsoft Excel:
Version: 16.40
Obtained from: Identified Developer
Last Modified: 12/08/2020, 11:43
Signed by: Developer ID Application: Microsoft Corporation (UBF8T346G9), Developer ID Certification Authority, Apple Root CA
Location: /Applications/Microsoft Excel.app
Kind: 64-bit
 

ccsalway

New Member
Joined
Aug 19, 2020
Messages
4
Office Version
  1. 365
Platform
  1. MacOS
I had to convert to octects to CLngLng. Now it works. Thanks
 

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
1,054
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
The error you are getting is the result of the first calculation. 192*16777216 is trying to set to_number to 3,221,225,472 which is larger than the maximum long size of 2,147,483,647. Off the top of my head I can't think of a way of handling it - but it begs the question as to why you are wanting to convert what looks like an IP address to a VERY large number.



 

Watch MrExcel Video

Forum statistics

Threads
1,123,088
Messages
5,599,664
Members
414,325
Latest member
kfg1287

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top