Help checking if ranges exist

flashsam

Board Regular
Joined
Nov 13, 2003
Messages
59
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.
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Richie(UK)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
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?:
Code:
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

Well-known Member
Joined
Oct 29, 2003
Messages
1,883
hi!
try this!
Code:
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

Board Regular
Joined
Nov 13, 2003
Messages
59
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

Well-known Member
Joined
Oct 29, 2003
Messages
1,883

ADVERTISEMENT

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

Board Regular
Joined
Nov 13, 2003
Messages
59
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

Well-known Member
Joined
Oct 29, 2003
Messages
1,883
Hi!
use this instead.

Set x = ThisWorkbook

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

Watch MrExcel Video

Forum statistics

Threads
1,127,632
Messages
5,625,994
Members
416,149
Latest member
Bigpotato 668

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top