• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 536
  • Last Modified:

How can I pass a named range from an excel vba function into an excel database function like dcount?

:  How can I pass a named range from an excel vba function into an excel database function like dcount?

The below call in an excel cell works fine.

=DCOUNTA([DataWorkbook]Data!_FilterDatabase,"Field1",NAMED_RANGE)

I would like NAMED_RANGE to come from my own VBA function.  Something like:

Function criteriaRangeLookup(lookupField as integer) As Range

if lookukpField = 1 then
   criteriaRangeLookup =Range("'NAMED_RANGE")
end if
end function

I would then like DCOUNTA to work as follows:

=DCOUNTA([DataWorkbook]Data!_FilterDatabase,"Field1",criteriaRangeLookup(1))

However this does not work.  How can I get something like this to work?

Function criteriaRangeLookup(lookupField as integer) As Range
    if lookukpField = 1 then 
        criteriaRangeLookup =Range("'NAMED_RANGE")
    end if
end function

Open in new window

0
jnash67
Asked:
jnash67
  • 2
1 Solution
 
Rory ArchibaldCommented:
It does work but your function is incorrect:

Function criteriaRangeLookup(lookupField as integer) As Range
    if lookupField = 1 then
        set criteriaRangeLookup =Range("'NAMED_RANGE")
    end if
end function

Regards,
Rory
0
 
jnash67Author Commented:
Thanks.  It's always the simple things that screw you up.
0
 
Rory ArchibaldCommented:
Ain't that the truth. Thanks for the grade.
Rory
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now