VBA - User Defined Indirect Function with COUNTIFS-CodePudding

I am trying to create a UDF to replace the INDIRECT function for use in the COUNTIFS function to allow dynamic ranges.

Goal:

Current:

=countifs(indirect([cell reference to named range],[criteria1]...)

I am trying to achieve:

=countifs(INDIRECTVBA([cell reference to named range],[criteria1...)

The function returns #VALUE at the moment.

Here is my VBA:

Public Function INDIRECTVBA(ref_text As String)
    INDIRECTVBA = Range(ref_text)
End Function

The ref_text argument would be a reference to a cell in which a VLOOKUP is dynamically listing a named range based on user selection elsewhere.

My first guess is a data type mismatch but I am out of ideas.

Any help is appreciated!

Thanks, Jordan

CodePudding user response：

You're returning a Variant value instead of a Range.

This would give an error because CountIf is expecting a Range in its first argument and not a Variant value.

If you want to return a range add As Range to your function declaration and use a Set statement.

Public Function INDIRECTVBA(ref_text As String) As Range
    Set INDIRECTVBA = Range(ref_text)
End Function

Because you omit Set the compiler is interpreting it as a Let statement. Without As Range in the function declaration, it is defaulting to As Variant. So the function is retrieving the value within the Range instead of returning the Range itself (Range objects give their Range.Value property by default when in Let statements).

I'm not too educated in CS, so this answer might be inaccurate. I genuinely appreciate learning more when people correct me.