Check if a named range exists with VBA?
I am trying to see if a named range exists with VBA. In my
subroutine, I browse for and then open another Excel file. I then set ranges on that sheet = to defined variables. I would like to verify that these ranges exist on that sheet before trying to set them (in order to avoid an error in running). For example, does that sheet have a named range "Name"? Thanks for any help, Matt |
Check if a named range exists with VBA?
Matt
Chip Pearson describes Named Ranges at: http://www.cpearson.com/excel/named.htm Regards Trevor wrote in message ps.com... I am trying to see if a named range exists with VBA. In my subroutine, I browse for and then open another Excel file. I then set ranges on that sheet = to defined variables. I would like to verify that these ranges exist on that sheet before trying to set them (in order to avoid an error in running). For example, does that sheet have a named range "Name"? Thanks for any help, Matt |
Check if a named range exists with VBA?
wrote in message ps.com... I am trying to see if a named range exists with VBA. In my subroutine, I browse for and then open another Excel file. I then set ranges on that sheet = to defined variables. I would like to verify that these ranges exist on that sheet before trying to set them (in order to avoid an error in running). For example, does that sheet have a named range "Name"? Thanks for any help, Matt Try this function. You have to supply the range name you are interested in, it returns True/False Public Function RangeNameExists(argRangeName As String) As Boolean ' Returns TRUE if the range name exists ' Include the sheet name in argRangeName. ' i.e. "Sunday!freezer25" where the sheet name is Sunday and the range name is freezer25 Dim n As Name RangeNameExists = False For Each n In ActiveWorkbook.Names If UCase(n.Name) = UCase(argRangeName) Then RangeNameExists = True Exit Function End If Next n End Function |
Check if a named range exists with VBA?
On Jan 30, 5:41 pm, "Skip" wrote:
wrote in message ps.com... I am trying to see if anamedrange exists with VBA. In my subroutine, I browse for and then open another Excel file. I then set rangeson that sheet = to defined variables. I would like to verify that theserangesexiston that sheet before trying to set them (in order to avoid an error in running). For example, does that sheet have anamedrange "Name"? Thanks for any help, Matt Try this function. You have to supply the range name you are interested in, it returns True/False Public Function RangeNameExists(argRangeName As String) As Boolean ' Returns TRUE if the range name exists ' Include the sheet name in argRangeName. ' i.e. "Sunday!freezer25" where the sheet name is Sunday and the range name is freezer25 Dim n As Name RangeNameExists = False For Each n In ActiveWorkbook.Names If UCase(n.Name) = UCase(argRangeName) Then RangeNameExists = True Exit Function End If Next n End Function That worked great. |
All times are GMT +1. The time now is 12:08 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com