[Solved] Convert IP address to decimal

[Rav3nw00d]

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

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 ?

[Villeroy]

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")

[Rav3nw00d]

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

[Villeroy]

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.

[Rav3nw00d]

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

[Villeroy]

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

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.

[Rav3nw00d]

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

Now I need to figure out the reverse.

[Rav3nw00d]

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 ?

[Villeroy]

#REF! has nothing to do with the function. It occurs when you copy a relative reference out of the sheet. Refer to http://user.services.openoffice.org/en/ ... 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

=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)))

[Rav3nw00d]

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.

[Villeroy]

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"?

[Villeroy]

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?