Syntax problem fassing named range into function
 

Hi,
I have a function that works when I pass in a string for a range name

I need to extend it to use an array and then loop it. Can't figure it out.


Any help appereciated


Works
If NameExists("exampleRangeName", Workbooks(ActiveWorkbook.Name)) Then

Doesn't work
MyRange(1,1)="exampleRangeName"

If NameExists((myRange(1 1)), Workbooks(ActiveWorkbook.Name)) Then


Function NameExists(WhatName As String, Optional WB As Workbook) As Boolean
Dim N As Long
On Error Resume Next
'Y = WB.Name
N = Len(IIf(WB Is Nothing, ThisWorkbook, WB).Names(WhatName).Name)
NameExists = (Err.Number = 0)
End Function

Syntax problem fassing named range into function
 

You need to cast the MyRange(1,1) value to a String type of variable,
since that is how the WhatName parameter of NameExists is declared.

E.g.,

B = NameExists(CStr(MyRange(1, 1)), ActiveWorkbook)

The CStr function converts whatever is in MyRange(1,1) to an explicit
String type, which is how the WhatName parameter to NameExists is
declared.


Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




On Thu, 11 Dec 2008 14:35:01 -0800, Pete Mac <Pete
wrote:

Hi,
I have a function that works when I pass in a string for a range name

I need to extend it to use an array and then loop it. Can't figure it out.


Any help appereciated


Works
If NameExists("exampleRangeName", Workbooks(ActiveWorkbook.Name)) Then

Doesn't work
MyRange(1,1)="exampleRangeName"

If NameExists((myRange(1 1)), Workbooks(ActiveWorkbook.Name)) Then


Function NameExists(WhatName As String, Optional WB As Workbook) As Boolean
Dim N As Long
On Error Resume Next
'Y = WB.Name
N = Len(IIf(WB Is Nothing, ThisWorkbook, WB).Names(WhatName).Name)
NameExists = (Err.Number = 0)
End Function