passing either an array or a range to VBA Function

[AEberli]

My function FindCell is developed to find a specific cell in a range, that contains one or more keywords. I am looking for a solution to pass either a range or an array as Keywords-Parameter, the function itself works with Keywords as Array.

Function FindCell(SearchArea As Range, Keywords As Variant, Optional Usage = "OR") As Integer

DateCell = FindCell(ActiveSheet.UsedRange, KeywordsInRange)
DateCell = FindCell(ActiveSheet.UsedRange, {"Date", "Expiration", "Valid from"} ) <-- does not work in this way

1) How can i check what kind of Keywords (Range or Array) I receive in the function ?
2) How can I transform a given Range Object into an Array for further execution in my function ?
3) Is it possible to declare one or more keywords in the function call and how, what else is the easiest way ?

[Bernie Deitrick]

You are passing the array incorrectly: Not sure how you are using FindCell - this is just an example of passing an array or range to a function.

Please Login or Register  to view this content.

[AEberli]

I have tried

Zelle = FindCell(ActiveSheet.UsedRange, Array("Date", "Expiration", "Valid from"))

and there are no compile or execution errors, but i receive just 1 element in the array:

Function FindCell(SearchArea As Range, Keywords As Variant, Optional Usage = "OR") As Integer

Debug.Print "FindCell: VarType=" & VarType(Keywords) & " Size= " UBound(Keywords)

Result: FindCell: VarType=8204 Size=1

VarType tells Array that's ok, but Size should be 3 in this case and not 1

[Norie]

I think we need to see the code for the function.

[AEberli]

Not finished, but here it is:

Please Login or Register  to view this content.

[Bernie Deitrick]

Try this example code to show how to access the values passed to the function - just make sure you have values in cells A1:A10

Please Login or Register  to view this content.

[Norie]

Why not check if Keywords is a range right at the start and if it is convert it to an array?

Please Login or Register  to view this content.

[AEberli]

Ok, I was starting with Index=1 and LBound(Keywords) starts with 0 !?

2019-02-07_21-38-14.jpg


The second case works also, but could you please explain your solution.
I do not understand why to produce an error instead os check varType ?

[Bernie Deitrick]

Option Base 0 is Excel's default, so unless you set Option Base 1 you will get LBound = 0 for any non-declared array - Array(....) is not declared.

I check for the error because.... that was what the example code in my library of code (that I copied out a long time ago) uses. I'm not against using different types but I typically define my UDFs a little more narrowly, so I never need to check what is being passed to my functions.

Note, too, that when I used VarType against the Array(....) it returned 8204, not a string and I could not remember how to get a string from a variant assigned an undeclared variable... Note that passing a Range("...") returned 0 unless the range object is declared as a range object, in which case it returns "Range" - so you actually need to do two checks against the variant = 0 or ="Range" and there may be other odd cases as well. And if the array was declared as a specific type, you get a different return from vartype.

So what to do? Throwing the error will always find non-range variants...

[AEberli]

Thank's to all !

I have finished my function GetArray and it was at last a combination of VarType and TypeName that delivered the result for the correct work flow. Maybe there are situations, that are not respected in this code, but it works for the intended parameters.

GetArray: VariantParameter is VarType=8 and TypeName=String with value "Land"
GetArray: VariantParameter is VarType=8204 and TypeName=V() mean's Array(2) with values "eins", "Land"
GetArray: VariantParameter is VarType=8204 and TypeName=V() mean's Array(3) with values "Date", "Expiration", "Valid from"
GetArray: VariantParameter is VarType=8204 and TypeName=Range ("$A$1:$A$5") with values "Rohstoffe / Land*", "", "EXXON MOBIL*", "Öl, Gas,*", "Chemie, Energie

Please Login or Register  to view this content.