Hi,


I would like to test, if a given URL contains a valid (existing)
top-level domain.

Say, if the user enters ".net" everything is fine, but if the user, by
accident, enters ".nwt" a waring will appear (the "w" key is right next
to the "e" key on the keyboard, so this mistake is very easy to do!).

Question is: Do I really have to test for each and every one of the 317
top-level domains, one by one, giving one heck of a 'Select Case' test
or is there a more cleaver way of doing this???


TIA,

CE

"Charlotte E." wrote:
Question is: Do I really have to test for each and every one
of the 317 top-level domains, one by one, giving one heck of
a 'Select Case' test or is there a more cleaver way of doing
this???

Create an ordered array and use WorksheetFunction.VLookup(...,TRUE). It
will find a match (or fail) in 9 comparisons or less.

Errata.... "joeu2004" wrote:
"Charlotte E." wrote:
Question is: Do I really have to test for each and every one
of the 317 top-level domains, one by one, giving one heck of
a 'Select Case' test or is there a more cleaver way of doing
this???

Create an ordered array and use WorksheetFunction.VLookup(...,TRUE).
It will find a match (or fail) in 9 comparisons or less.

First, that should be an ascending-ordered array.

Second, use WorksheetFunction.Match(...,1) with On Error Resume Next, or use
Application.Match(...,1).

Also, if the match succeeds, you must test the result for equality, since
Match(...,1) might find a different result with a "smaller" value.

Alternatively, use Match(...,0). Not as efficient -- failure requires 317
comparisons -- but it avoids the extra test for equality, which you might
consider messy

Yeah, that solution is a no-brainer :-)

But, I was hoping to avoid using the worksheet interface, and get af
full VBA solution...

Guess, I'll have to stick to my 'Select Case' function...


Thanks anyway...


CE



Den 25.05.2013 18:59, joeu2004 skrev:
"Charlotte E." wrote:
Question is: Do I really have to test for each and every one
of the 317 top-level domains, one by one, giving one heck of
a 'Select Case' test or is there a more cleaver way of doing
this???

Create an ordered array and use WorksheetFunction.VLookup(...,TRUE). It
will find a match (or fail) in 9 comparisons or less.

"Charlotte E." wrote in message
Yeah, that solution is a no-brainer :-)

But, I was hoping to avoid using the worksheet interface, and get af
full VBA solution...

Guess, I'll have to stick to my 'Select Case' function...

I'd have gone with joeu2004's suggestion but 317 select case!!!

Here's an alterntive approach

Sub test()
MsgBox IsValidTLD(".net")
' maybe ensure its prefixed with a dot
MsgBox IsValidTLD("co.uk")
' hmm, is .co.uk a top level name or is only .uk
End Sub

Function IsValidTLD(ByVal sTLD As String) As Boolean
Dim sNames As String
Dim arr, v
sTLD = LCase$(sTLD)
sNames = ".net,.com," & _
".org,.co.uk"
sNames = Replace(sNames, " ", "")

arr = Split(sNames, ",")
For Each v In arr
If v = sTLD Then
IsValidTLD = True
Exit Function
End If
Next
End Function

Regards,
Peter T

"Charlotte E." wrote:
I was hoping to avoid using the worksheet interface,
and get af full VBA solution...
Guess, I'll have to stick to my 'Select Case' function...

Why avoid WorksheetFunction operations? They __are__ "full VBA" solutions,
just as much as any VBA built-in function such as InStr. WorksheetFunction
operations are compiled implementations. They do not invoke Excel to do the
work, as a method like Range.Sort does.

Anyway, alternatively, you could implement the binary search yourself. For
example:

Function myMatch(key As Variant, table As Variant) As Long
Dim lo As Long, hi As Long, m As Long
lo = LBound(table, 1)
hi = UBound(table, 1)
Do
m = (lo + hi) \ 2
If key = table(m) Then myMatch = m: Exit Function
If key < table(m) Then hi = m - 1 Else lo = m + 1
Loop Until hi < lo
myMatch = 0 ' for clarity; not really needed
End Function


Sub testit()
Dim table As Variant, key As Variant
Dim x As Long
table = Array("com", "edu", "gov", "org")
For Each key In Array("org", "xxx")
x = myMatch(key, table)
If x = 0 Then MsgBox key & " " & x & " Not Found" _
Else MsgBox key & " " & x & " " & table(x)
Next
End Sub

"Peter T" wrote:
Here's an alterntive approach
[....]
Function IsValidTLD(ByVal sTLD As String) As Boolean
Dim sNames As String
Dim arr, v
sTLD = LCase$(sTLD)
sNames = ".net,.com," & _
".org,.co.uk"
sNames = Replace(sNames, " ", "")

arr = Split(sNames, ",")
For Each v In arr
If v = sTLD Then
IsValidTLD = True
Exit Function
End If
Next
End Function

Or more simply:

Const table As String = _
".net,.com,.org,.co.uk," ' note comma at end

Function isValidDom(dom As Variant) As Long
isValidDom = InStr(table, "." & dom & ",")
End Function


Sub testit()
Dim key As Variant
For Each key In Array("org", "xxx")
If isValidDom(key) Then MsgBox key & " okay" _
Else MsgBox key & " bad"
Next
End Sub


I was trying to avoid the linear comparison of 300+ names. But on second
thought, an Instr solution is probably just as good.