[Solved] Convert IP address to decimal

[Solved] Convert IP address to decimal

I have a VB function to take an Ip address and convert it to its decimal equivalent.
being new to OpenOffice I am not sure how to convert the function to be used in OpenOffice.
Code: Select all   Expand viewCollapse view
`Function IP2Dec(ip As String) As StringDim str1, str2, str3, str4 As StringDim dec1, dec2, dec3, dec4 As Integerstr1 = Left(ip, Find(".", ip, 1) - 1)str2 = Mid(ip, Len(str1) + 2, ( Find(".", ip, Len(str1) + 2) - Len(str1) -2))str3 = Mid(ip, Len(str1) + Len(str2) + 3, ( Find(".", ip, Len(str1) + Len(str3) + 3) - (Len(str1) + Len(str2) + 3))str4 = Right(ip, Len(ip) - (Len(str1) + Len(str2) + Len(str3) + 3))dec1 = (str1)*2^24dec2 = (str2)*2^16dec3 = (str3)*2^8dec4 = (str4)*2^0IP2Dec = (dec1 + dec2 + dec3 + dec4)End Function`

When I try to use the function in calc i get an error at str1 "BASIC Runtime error. Argument is not optional."
How do I use calc functions inside my function ?
In VB the Find function would be Application.WorksheetFunction.Find(x, y, z) is there some sort of equivalent in calc ?
Last edited by Hagar Delest on Mon Jun 09, 2008 10:46 pm, edited 4 times in total.
Reason: tagged the thread as Solved.
Rav3nw00d

Posts: 6
Joined: Tue Mar 25, 2008 12:24 am

Re: Convert IP address to decimal

The following should work in Calc, Excel and Gnumeric:
A1 binary IP string
B1 =FIND(".";\$A1)
C1 =FIND(".";\$A1;B1+1)
D1 =FIND(".";\$A1;C1+1)
E1 =BIN2DEC(LEFT(\$A1;\$B1-1))
F1 =BIN2DEC(MID(\$A1;B1+1;C1-B1-1))
G1 =BIN2DEC(MID(\$A1;C1+1;D1-C1-1))
H1 =BIN2DEC(RIGHT(\$A1;LEN(\$A1)-\$D1))
I1 =TEXT(E1;"000")&"."&TEXT(F1;"000")&"."&TEXT(G1;"000")&"."&TEXT(H1;"000")
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4

Villeroy
Volunteer

Posts: 28650
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Convert IP address to decimal

I fixed a typo in the function. I thought I had the answer that the "sub" functions needed " ; " instead of " , ".
But that was not the case. Now I get another error, " Basic runtime error. Sub-procedure or function procedure not defined. "

The input to the function would be an IP address such as 192.168.1.1
would have a decimal output of 3232235777
192 = 11000000
168 = 10101000
1 = 00000001
1 = 00000001
-------------------
11000000 10101000 00000001 00000001 = 3232235777
Last edited by Rav3nw00d on Tue Mar 25, 2008 6:25 pm, edited 1 time in total.
Rav3nw00d

Posts: 6
Joined: Tue Mar 25, 2008 12:24 am

Re: Convert IP address to decimal

Please, stop that Basic bullshit unless you know how to debug any code! It's not worth the effort since you can do everything in the native spreadsheet language.
Just apply the formulas I have given to you.
- It will be performant across many thousands of rows.
- It is easy to debug. Just see which cell shows an error. Your Basic does not even handle errors.
- You can share the calculation model with others without any security warnings nor install procedures.

If you really want to know how to call spreadsheet functions in the one of OOo's macro languages, search for "FunctionAccess" at http://oooforum.org. It is not as trivial as in VBMSA (Visual Basic for Microsoft Applications). If you still insist in Basic then use this sites API/Macro forum for this type of problem.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4

Villeroy
Volunteer

Posts: 28650
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Convert IP address to decimal

Geesh no need to get snippy. But my input is NOT a binary number. It is a IP address.
Rav3nw00d

Posts: 6
Joined: Tue Mar 25, 2008 12:24 am

Re: Convert IP address to decimal

In fact there is no need to use any API at all. It can be done in pure Basic.
Turning your code in something readable with basic error handling (return Null => #VALUE)
Code: Select all   Expand viewCollapse view
`Function IP2Dec(ip As String) As StringOn error goto NullErrDim str1, str2, str3, str4 As StringDim dec1, dec2, dec3, dec4 As IntegerDim p1, p2, p3p1 = inStr(ip, ".")p2 = inStr(p1 +1, ip, ".", ip)p3 = inStr(p2 +1, ip, ".", ip)str1 = Left(ip, p1 -1)str2 = Mid(ip, p1 +1, p2 - p1 -1)str3 = Mid(ip, p2 +1, p3 - p2 -1)str4 = Right(ip, Len(ip) - p3)dec1 = Int(str1)*2^24dec2 = Int(str2)*2^16dec3 = Int(str3)*2^8dec4 = Int(str4)*2^0IP2Dec = (dec1 + dec2 + dec3 + dec4)exit functionNullErr:  IP2Dec = NullEnd Function`

I would do it on the sheet, anyway.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4

Villeroy
Volunteer

Posts: 28650
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Convert IP address to decimal

Excellent ! Works like a charm. Thank you very much !

Now I need to figure out the reverse.
Rav3nw00d

Posts: 6
Joined: Tue Mar 25, 2008 12:24 am

Re: [Solved] Convert IP address to decimal

For some strange reason, when I try to use this function I get a " #REF! " error.
Tools, Macros, Organize Macros, OpenOffice.org Basic..., ( My Macros, Standard ), New ( IP2DEC )
I then dropped in the function and saved.

A1 192.168.1.100
B1 =IP2DEC(A1)

But instead of B1 being 3232235876 I get " #REF! "

Am I missing something here ?
Rav3nw00d

Posts: 6
Joined: Tue Mar 25, 2008 12:24 am

Re: [Solved] Convert IP address to decimal

#REF! has nothing to do with the function. It occurs when you copy a relative reference out of the sheet. Refer to viewtopic.php?f=9&t=3252

Just for the records, I add the regular way how to do this in any spreadsheet.
A1 192.168.100.23
B1 =FIND(".";\$A1)
C1 =FIND(".";\$A1;B1+1)
D1 =FIND(".";\$A1;C1+1)
E1 =2^24*VALUE(LEFT(\$A1;B1-1))
F1 =2^16*VALUE(MID(\$A1;B1+1;C1-B1-1))
G1 =2^8*VALUE(MID(\$A1;C1+1;D1-C1-1))
H1 =VALUE(MID(\$A1;D1+1;LEN(\$A1)-D1))
I1 =SUM(\$E1:\$H1)
This can be substituted with each other to build up the following formula which depends on constant A1 only:
Code: Select all   Expand viewCollapse view
`=2^24*VALUE(LEFT(\$A1;FIND(".";\$A1)-1))+2^16*VALUE(MID(\$A1;FIND(".";\$A1)+1;FIND(".";\$A1;FIND(".";\$A1)+1)-FIND(".";\$A1)-1))+2^8*VALUE(MID(\$A1;FIND(".";\$A1;FIND(".";\$A1)+1)+1;FIND(".";\$A1;FIND(".";\$A1;FIND(".";\$A1)+1)+1)-FIND(".";\$A1;FIND(".";\$A1)+1)-1))+VALUE(MID(\$A1;FIND(".";\$A1;FIND(".";\$A1;FIND(".";\$A1)+1)+1)+1;LEN(\$A1)-FIND(".";\$A1;FIND(".";\$A1;FIND(".";\$A1)+1)+1)))`
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4

Villeroy
Volunteer

Posts: 28650
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Convert IP address to decimal

Okay the regular way works.
But I am not sure why the function doesnt work.

I dont get the #REF! in the formula but displayed in the cell.
and the function changes from =ip2dec(c2) to ='IP2DEC'(C2).

I mean I get the #REF! after I enter the formula, not after copying it to another cell.
Rav3nw00d

Posts: 6
Joined: Tue Mar 25, 2008 12:24 am

Re: [Solved] Convert IP address to decimal

I can not reproduce the problem. The single quotes indicate that Calc tries to resolve an unknown name as quoted label, but no matter what I try to do wrong, I always get #NAME rather than #REF!
Are you shure that IP2DEC is a function in some Basic-library named "Standard"?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4

Villeroy
Volunteer

Posts: 28650
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Convert IP address to decimal

Villeroy wrote:I can not reproduce the problem. The single quotes indicate that Calc tries to resolve an unknown name as quoted label, but no matter what I try to do wrong, I always get #NAME rather than #REF!
Are you shure that IP2DEC is a function in some Basic-library named "Standard"?

I believe, I got it.
IF B1 =IP2DEC(A1)
AND somewhere below row #1 occurs the string "IP2DEC"
AND the following option is turned on: Menu:Tools>Options...Calc>Calculation:Automatically find row and column labels"
THEN the column-label is found at the wrong place before the function names in Basic. The wrong label reference gives the #REF! error.
Turn off the above mentioned option. It's one of those pointless "Excel has it too". When you understand how to use labels, you don't want them to spring into existence automatically.

Did I mention that I really hate macros, particulary the Basic ones?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4

Villeroy
Volunteer

Posts: 28650
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany