VBA: passing variables (from worksheet) into functions

Question

A quick one for noob like me!

So usually I pass in some range of data from worksheet into function like that:

public function foo (someRange as range)
   dim someData as double
   if IsNumeric(someRange.value), do some crap
end function

The problem occur when I try to code some function that uses this function foo. Instead of range i would like to pass in a double(). So if I were to fix this, i can either:

a. I have seen some other site uses "someRange as Variant" instead (which then avoid problem like i face). However, is it "good coding practice"?

b. use a wrapper function foo_wrap instead

Andy G · Accepted Answer · 2013-07-21 11:11:19Z

You could use a Variant and then TypeName to find out what kind of argument was supplied:

?TypeName(range("A1"))
Range
?TypeName(45.66)
Double
?TypeName(array(34.5,56.7))
Variant()

Personally, I would just create two separate Functions. Using one function would just be messy to me. For a single function NOT to just duplicate code, it would have to extract all the Range values into a Double array. This just tells us that it should be two functions.

Creating a wrapper-function is an option, but I'll leave you to decide whether this is a good solution for you.

If you did want to pursue a multi-purpose function then you could investigate ParamArray:

Used only as the last argument in arglist to indicate that the final argument is an Optional array of Variant elements.

This would allow you to create a Function that behaves similarly to the built-in functions (SUM, etc.) which can accept a variable number of arguments, which can be Ranges and/or values. However, particularly as you are a noob, I would ignore this possibility for a while. The KISS principle.

Collectives™ on Stack Overflow

VBA: passing variables (from worksheet) into functions

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related