Help checking if ranges exist

[flashsam]

Hello,

For a macro to work there are files that need to contain the 4 named ranges:
InvCountry
InvDate
TotalDisb
TotalFees

I have a word document with instructions in but then at the end i have a link to a file that has a macro in it that will test the workbook to make sure that the ranges exist. If any are missing then i want a message to the user to let them know which ones are missing.

I'm not too sure about how to see if a range exists or not so the code i have already uses 'goto error' sections in the event that the range doesn't exist which will add that range name to variable 'a' which will then produce the message box in the end.

My code doesn't work. It keeps on coming up with error messages and it seems to ignore my 'On error' lines. Does anyone know why the code isn't working or does anyone know a better way to check if ranges exist?

(I don't have much experience using On Error so this is the best i coud come up with to check the ranges)

here's the code i have at the moment: (It sits in the ThisWorkbook section)

Private Sub Workbook_Open()
ActiveWindow.ActivateNext

Check1:
On Error GoTo Error1
Application.Goto Reference:="InvCountry"
On Error GoTo 0

Check2:
On Error GoTo Error2
Application.Goto Reference:="InvDate"
On Error GoTo 0

Check3:
On Error GoTo Error3
Application.Goto Reference:="TotalDisb"
On Error GoTo 0

Check4:
On Error GoTo Error4
Application.Goto Reference:="TotalFees"
On Error GoTo 0

Error1:
a = "InvCountry" & Chr(13)
GoTo Check2

Error2:
a = a & InvDate & Chr(13)
GoTo Check3

Error3:
a = a & TotalDisb & Chr(13)
GoTo Check4

Error4:
a = a & TotalFees & Chr(13)

If a <> " " Then
MsgBox a, vbInformation, "Check complete"
else
MsgBox "The check is complete. You have added all the correct ranges to the workbook", vbInformation, "Check complete"
End If

End Sub


Thanks for any help in advance.

 

[Richie(UK)]

Hi Sam,

Well, one way might be to create your own testing function for the named ranges, rather than repeating the code for the same test. How about something like this?:

Sub RangeTest()
    Dim vNames As Variant, l As Long
    
    vNames = Array("InvCountry", "InvDate", "TotalDisb", "TotalFees")
    For l = LBound(vNames) To UBound(vNames)
        If RngExist(CStr(vNames(l))) = False Then MsgBox vNames(l) & " is missing"
    Next l
    
End Sub

Function RngExist(strRangeName As String) As Boolean
    Dim rngTest As Range
    On Error Resume Next
    Set rngTest = Range(strRangeName)
    On Error GoTo 0
    RngExist = Not rngTest Is Nothing
End Function

Only tested briefly so make sure you test it fully!

HTH

 

[SIXTH SENSE]

hi!
try this!

sub RangeSeeker()
   Dim RangeNames 
 rangeNames = Array("InvCountry", "InvDate", "TotalDisb", "TotalFees") 
    Set X = activesheet
    With X
for j=lbound(rangenames) to ubound(rangenames)
        found=false
         For I = 1 To .Names.Count
            if ucase(rangenames(j)) =ucase( .Names(I).Name) then                  found=true
        Next I
             if not found then msgbox rangenames(j) &" is missing"
    End With

 

[flashsam]

Hello.

Thanks both of you for your suggestions. Sixth Sense, i'm afraid i got an error saying 'End With Without With'. Wasn't sure how to get rid of the error.

Richie,
your code worked fine. I just made a minor adjustment so that i received only 1 msgbox at the end listing the missing names (see below).

Sub RangeTest()
Dim vNames As Variant, l As Long

ActiveWindow.ActivateNext

vNames = Array("InvCountry", "InvDate", "TotalDisb", "TotalFees")
For l = LBound(vNames) To UBound(vNames)
If RngExist(CStr(vNames(l))) = False Then a = a & vNames(l) & Chr(13)
Next l

If a <> "" Then
MsgBox "You are missing the following named ranges. Please add them and then test again: " & Chr(13) & Chr(13) & a, vbInformation, "Check complete"
Else
MsgBox "The check is complete. You have added all the correct ranges to this workbook", vbInformation, "Check complete"
End If

End Sub

Function RngExist(strRangeName As String) As Boolean
Dim rngTest As Range
On Error Resume Next
Set rngTest = Range(strRangeName)
On Error GoTo 0
RngExist = Not rngTest Is Nothing
End Function

Thank you both for your very quick responses!

 

[SIXTH SENSE]

oh!
Im sorry I miss the next J.

try this again!

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> RangeSeeker()
   <SPAN style="color:#00007F">Dim</SPAN> RangeNames
RangeNames = Array("InvCountry", "InvDate", "TotalDisb", "<SPAN style="color:#00007F">To</SPAN>talFees")
    <SPAN style="color:#00007F">Set</SPAN> X = ActiveSheet
    <SPAN style="color:#00007F">With</SPAN> X
    <SPAN style="color:#00007F">For</SPAN> j = <SPAN style="color:#00007F">LBound</SPAN>(RangeNames) <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(RangeNames)
        found = <SPAN style="color:#00007F">False</SPAN>
        <SPAN style="color:#00007F">For</SPAN> I = 1 To .Names.Count
            <SPAN style="color:#00007F">If</SPAN> UCase(RangeNames(j)) = UCase(.Names(I).Name) <SPAN style="color:#00007F">Then</SPAN> found = <SPAN style="color:#00007F">True</SPAN>
        <SPAN style="color:#00007F">Next</SPAN> I
             <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> found <SPAN style="color:#00007F">Then</SPAN> MsgBox RangeNames(j) & " is missing"
    <SPAN style="color:#00007F">Next</SPAN> j
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[flashsam]

Sorry Sixth Sense, i tested your code using a workbook that had only one of the names there but every time it always says that all four of them aren't there!

Don't worry though, cause Richie's code works fine, although it would have been a bit neater to be able to do it without a function.

One last question though if you don't mind.

Although Richie's macro works fine, it needs to be activated through the workbook. What i want to do is have a link to this workbook in a Word document and then make the macro on_open so that all a user has to do to test for the ranges is click the link.

However, if i use Explorer or excel to open the file it works fine but when i open the file using the hyperlink in word it does the macro twice.

does anyone know why?

 

[SIXTH SENSE]

Hi!
use this instead.

Set x = ThisWorkbook

I got no idea on the your query. ill take a look at that.