Syntax problem fassing named range into function

P

Pete Mac

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
 
C

Chip Pearson

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top