[Solved] Convert IP address to decimal

Discuss the spreadsheet application

[Solved] Convert IP address to decimal

Postby Rav3nw00d » Tue Mar 25, 2008 12:44 am

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 String
Dim str1, str2, str3, str4 As String
Dim dec1, dec2, dec3, dec4 As Integer

str1 = 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^24
dec2 = (str2)*2^16
dec3 = (str3)*2^8
dec4 = (str4)*2^0

IP2Dec = (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

Postby Villeroy » Tue Mar 25, 2008 1:08 am

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
User avatar
Villeroy
Volunteer
 
Posts: 28650
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Convert IP address to decimal

Postby Rav3nw00d » Tue Mar 25, 2008 3:59 pm

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

Postby Villeroy » Tue Mar 25, 2008 5:10 pm

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 compatible with any other spreadsheet program beeing able to read your file.
- 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
User avatar
Villeroy
Volunteer
 
Posts: 28650
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Convert IP address to decimal

Postby Rav3nw00d » Tue Mar 25, 2008 6:57 pm

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

Postby Villeroy » Tue Mar 25, 2008 7:31 pm

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 String
On error goto NullErr
Dim str1, str2, str3, str4 As String
Dim dec1, dec2, dec3, dec4 As Integer
Dim p1, p2, p3

p1 = 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^24
dec2 = Int(str2)*2^16
dec3 = Int(str3)*2^8
dec4 = Int(str4)*2^0

IP2Dec = (dec1 + dec2 + dec3 + dec4)
exit function
NullErr:
  IP2Dec = Null
End 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
User avatar
Villeroy
Volunteer
 
Posts: 28650
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Convert IP address to decimal

Postby Rav3nw00d » Tue Mar 25, 2008 8:47 pm

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

Postby Rav3nw00d » Wed Apr 16, 2008 9:55 pm

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.

In the spreadsheet I have
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

Postby Villeroy » Wed Apr 16, 2008 10:53 pm

#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
User avatar
Villeroy
Volunteer
 
Posts: 28650
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Convert IP address to decimal

Postby Rav3nw00d » Wed Apr 16, 2008 11:35 pm

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

Postby Villeroy » Wed Apr 16, 2008 11:49 pm

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
User avatar
Villeroy
Volunteer
 
Posts: 28650
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Convert IP address to decimal

Postby Villeroy » Thu Apr 17, 2008 12:05 am

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
User avatar
Villeroy
Volunteer
 
Posts: 28650
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: No registered users and 27 guests