validate ip-address 2

[InDenial]

Hi all,

I checked everywhere for ip-address validation in excel, but I did not find any answer.

I found the following in the help files with excel wich might be of use to me, but I have no idea where to start.

This is the code in the helpfiles:

Private Sub Worksheet_Calculate()
    Columns("A:F").AutoFit
End Sub

to validate an ip-address I started doing the following:

Private Sub Worksheet_Calculate()
    Dim ipaddr As String
    Set ipaddr = Worksheets("Test").Range("c6")
End Sub

Okay as far as I know..
[ul]
[li]I declared a variable ipaddr[/li]
[li]I put the value in cell c6 into the variable ipaddr[/li]
[/ul]

now what?.. I need something like split in perl to check if the parts are smaller than 255 and larger than 0. How would I go about that?

I also found something like ParseLine but I have no idea how to use that in my situation where I do not want the parts to be put in another cell but want to use the parts to end up in another variable.

(I know that checking for 0<part<255 is not a complete way of checking if it is a valid ip-address but if I know that one the rest will be easy I think)

InDenial

 

[RoyVidar]

Perhaps along these lines (typed not tested, would probably need some more testing, errorhandling...)

[tt]Dim ipaddr As String
dim arripaddr() as string
dim lngCount as long
dim fok as boolean
ipaddr = Worksheets("Test").Range("c6")
arripaddr=split(ipaddr,".")
for lngcount = 0 to ubound(arripaddr)
fok=isnumeric(arripaddr(lngcount))
if fok then
fok = (val(arripaddr(lngcount))>= 0 and val(arripaddr(lngcount))<=255)
end if
if not fok then exit for
next lngcount
msgbox fok[/tt]

Regexp?

[tt]Dim ipaddr As String
dim re as object
ipaddr = Worksheets("Test").Range("c6")
set re=createobject("vbscript.regexp")
re.multiline=true
re.global=true
re.pattern = <some pattern found here? RegExpLib.Com >
msgbox re.test(ipaddr)
set re=nothing[/tt]

Should be more patterns for IP validation available elsewhere on net too, probably.

Roy-Vidar

 

[InDenial]

Roy,

so you are actually telling me there is a split in vb? I was looking for it in help but could not find it. I thought it was stupid that it was not there.

I am gonna check out the above later today.. worktime...

and ofcourse... thanks...

InDenial

 

[RoyVidar]

Yes, starting with the 2000 version (VB6), I think. For prior versions one would need to create ones own, but then, RickSpr has alredy done so in their faq faq705-4342, in the Access section, which I think you can also use in your Excel app.

Roy-Vidar

 

[InDenial]

Ok I got it working but I am encountering a problem. I use

Private Sub Worksheet_Change(ByVal Target As Range) to check if there is a change on the worksheet. problem is that whenever I change something on the sheet it will give a true or false. I just want it to respond to the changing of the cell (C6) itself.

Another problem is that I want to change the cell to 0.0.0.0 if someone entered a wrong ip-address. If that happens the sub will be called again.

So in short..
if the ip-address is fales -> popup a message that says it is false and change the value to 0.0.0.0
before the value is changed I need to disable the sub untill the next time the cell is changed

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ipaddr As String
Dim arripaddr() As String
Dim lngCount As Long
Dim fok As Boolean
ipaddr = Worksheets("Sheet1").Range("C6")
arripaddr = Split(ipaddr, ".")
If (UBound(arripaddr) = 3) Then
For lngCount = 0 To UBound(arripaddr)
        fok = IsNumeric(arripaddr(lngCount))
        If fok Then
            fok = (Val(arripaddr(lngCount)) >= 0 And Val(arripaddr(lngCount)) <= 255)
        End If
        If Not fok Then Exit For
        Next lngCount
Else
    MsgBox "This is not an ip-address"
    Worksheets("Sheet1").Range("C6").Value = "0.0.0.0"
End If
MsgBox fok
End Sub

InDenial

 

[RoyVidar]

I'll probably show off how little I know about Excel programming, but I think I'd declare a public variable in a standard module that get's assigned either what's in the cell at the start or "0.0.0.0", then per each on change, check against it.

[tt]' In a standard module
Public strIP as String

' This Workbooks on Open
Private Sub Workbook_Open()
if Worksheets("Sheet1").Range("C6").Value = "" Then
Worksheets("Sheet1").Range("C6").Value = "0.0.0.0"
strIP = "0.0.0.0"
Else
strIP = Worksheets("Sheet1").Range("C6").Value
End If
End Sub

' The change event
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ipaddr As String
Dim arripaddr() As String
Dim lngCount As Long
Dim fok As Boolean
if strIP<>Worksheets("Sheet1").Range("C6").Value then
ipaddr = Worksheets("Sheet1").Range("C6")
arripaddr = Split(ipaddr, ".")
If (UBound(arripaddr) = 3) Then
For lngCount = 0 To UBound(arripaddr)
fok = IsNumeric(arripaddr(lngCount))
If fok Then
fok = (Val(arripaddr(lngCount)) >= 0 And Val(arripaddr(lngCount)) <= 255)
End If
If Not fok Then Exit For
Next lngCount
if not fok then
MsgBox "This is not an ip-address"
Worksheets("Sheet1").Range("C6").Value = "0.0.0.0"
strIP = "0.0.0.0"
else
strIP = Worksheets("Sheet1").Range("C6").Value
end if
Else
MsgBox "This is not an ip-address"
Worksheets("Sheet1").Range("C6").Value = "0.0.0.0"
strIP = "0.0.0.0"
End If
end if
End Sub
[/tt]

Roy-Vidar

 

[TonyJollans]

Hi InDenial,

1. To make your code only check cell C6 when cell C6 is changed, you must check whether cell C6 is in the Target Range. Add this at the start of your procedure ..

[blue]Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Range("C6"), Target) Is Nothing Then Exit Sub

    :
    :[/blue]

2. To stop the recursive calls to your change routine, disable events while you change the cell ..

[blue]    :
    :

    Application.EnableEvents = False
    Worksheets("Sheet1").Range("C6").Value = "0.0.0.0"
    Application.EnableEvents = True

    :
    :[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[InDenial]

Thanks all,

I will check it out later today when I get home. I do not know enough yet about this object stuff. I will though.

Btw. is it me or is the help within office just crap?

InDenial

 

[InDenial]

Hi all,

Thanks for the help.. It worked and I made some other changes to the script. I would like to know if:

I could do things in a better way and Bad code and so on

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("C6"), Target) Is Nothing Then Exit Sub

Dim ipaddr As String
Dim arripaddr() As String
Dim lngCount As Long
Dim fok As Boolean

ipaddr = Worksheets("Sheet1").Range("C6")
arripaddr = Split(ipaddr, ".")

If (UBound(arripaddr) = 3) Then
For lngCount = 0 To UBound(arripaddr)
        fok = IsNumeric(arripaddr(lngCount))
        If fok Then
            Select Case lngCount
            Case 0, 3
                fok = (Val(arripaddr(lngCount)) > 0 And Val(arripaddr(lngCount)) < 255)
            Case 1, 2
                fok = (Val(arripaddr(lngCount)) >= 0 And Val(arripaddr(lngCount)) <= 255)
            End Select
        End If
        If Not fok Then GoTo BadIp
        Next lngCount
Else
 GoTo BadIp
End If

Exit Sub
BadIp:
 MsgBox "This is not an ip-address"
 Application.EnableEvents = False
 Worksheets("Sheet1").Range("C6").Value = "0.0.0.0"
 Application.EnableEvents = True
End Sub

InDenial

 

[InDenial]

Oh and Roy,

Your part as with that StrIp.

The idea in the end is that it is also possible to read lines from a file and extract information from it and put it in the sheet. Then add values and rewrite the tekst file with the new info added.

And the file should be clean everytime it is started. and I was thinking about making it a read only file so you can put values in.. press a button.. put those values in a text file (which is actually gonna be a configurationfile for a switch)

I will keep this thread in mind especially because of the Workbook_open sub.

InDenial

 

[TonyJollans]

Hi InDenial,

Only thing I would find real fault in is your references to "Sheet1". Whilst it is generally good practice to make references explicit, your code is in Sheet1's Code Module and references are, by default, to Sheet1. If you change the name of Sheet1 the explicit references in your code will stop working and it might be better to remove them before that happens.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[InDenial]

Tony,

So best thing I can do is to reference to the active sheet and put the code in a module in the workbook? or is there another better way?

When I decide to make another sheet where the cell C6 means something else to me... then the script would react to that cell too?

InDenial

 

[PHV]

I think suggested to replace this:
ipaddr = Worksheets("Sheet1").Range("C6")
By this:
ipaddr = Range("C6")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TonyJollans]

Hi InDenial,

PH has correctly interpreted what I was saying.

Your code (in the Worksheet_Change Event) has to be in the Worksheet's code module - you can't put it anywhere else (although you could call a procedure elsewhere) and, because of that, all unqualified references are deemed to belong to that sheet. That sheet, however, is not necessarily the Active Sheet. The point I was making was that naming "Sheet1" added nothing (Range("C6") by itself refers absolutely to cell C6 on the sheet with the code) and would cause an error if you changed the name to "My IP Sheet" or something else.

Hope that makes it a bit clearer.

Your reference to cell C6 is specific because that is the cell that you are interested in. If you are interested in a different cell (at another time) you will have to say what it is - but if you wanted to use the same code in a different sheet it would work if you copied it directly without the reference to Sheet1, but wouldn't work correctly with the reference.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[InDenial]

Okay I get it.. but it leaves me with a small problem right now... at the end of my code I have :

BadIp:
 MsgBox "This is not an ip-address"
 Application.EnableEvents = False
 [b]Worksheets("Sheet1").Range("C6").Value = "0.0.0.0"[/b]
 Application.EnableEvents = True

When I try to remove the Worksheets("sheet1") there it just does not work.. Sometimes I start to believe that something is simple but then something like this happen and I am back to square one...

InDenial

 

[PHV]

there it just does not work
Any error message ?
You may try this:
BadIp:
Application.EnableEvents = False
Range("C6") = "0.0.0.0"
Application.EnableEvents = True
MsgBox ipaddr & " is not an ip-address"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[InDenial]

Thanks PHV,

I did what you typed there before I read this.. and it worked.. after I closed the workbook and excel completely and started it up again...

thanks all... you all are of a great help...

InDenial