Function input containing operators

[beakerboy]

If one has a range of cells containing:
A
1 1
2 0
3 3
4 0
5 5

And you type in a cell: =A1:A4>0 you get a result of {True False True False True}. If, however, you have a VBA function:

Public Function NewFunc(myBools As Range)
    NewFunc = myBools
End Function

and call it as NewFunc(A1:A4>0) you get #Value. How do I set my function up so it evaluates the function in the input.

 

[SkipVought]

Hi,

Because A1:A4>0 is NOT A RANGE -- it is an expression.

You have myBools defined as a RANGE.

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[beakerboy]

You are correct: it is an expression and what data type does this expression return? There's no "Array" data type, so a Range is the closest thing I could think of.

Kevin

 

[beakerboy]

I used google to refresh my memory on arrays (It's been a few years since doing a lot of VBA). What I read suggested that one can't assign an array to a range, but you van assign a varient containing an array to a range. I assumed that the expression was returning an array, so I changed the above function definition to:

Public Function NewFunc(myBools As Varient)
    NewFunc = myBools
End Function

and it works.


Thanks for not helping, but getting me thinking.

Kevin

 

[beakerboy]

Well, now I'm stuck on part two...Accessing elements of the above array. Doing any sort of myBools(1) causes #Value errors. The vartype for myBools is 8204...or an array of Variants. My guess is that, as an array of variants, the script can't determine where each element starts and ends.
UBound and LBound work, but the index doesn't.

Thanks for any help,
Kevin

 

[Zathras]

The resulting array is 2-dimensioned. Try MyBools(1,1), MyBools(2,1), etc.