MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

User Defined Functions

  Asked By: Pedro    Date: Jan 24    Category: MS Office    Views: 812

I'm trying to learn how to write functions for use in Excel. For
example, one function I'd like to write would take as an argument a
one dimensional range, either an array or a vector.

To create a function for use in Excel, I understand that I have to do
the following:

Function Truncate(InitialRange)


End Function

First, how can I check that the variable InitialRange is a reference
to cells in the spreadsheet (e.g. "=Truncate(B6:B12)"), and not a
number or a string (e.g. "Truncate(6)")? Is VBA in any way a typed

Once that is done, how do I access the values in the range? I want to
check each value to make sure it satisfies a constraint; for example,
all cells must be under 100.

Finally, I want to return a new range that is a subset of InitialRange.

Please let me know if you can help me, or if I need to clarify this in
any way.



1 Answer Found

Answer #1    Answered By: Waggoner Fischer     Answered On: Jan 24

>First, how can I check  that the variable  InitialRange is a reference
>to cells  in the spreadsheet  (e.g. "=Truncate(B6:B12)"), and not a
>number or a string  (e.g. "Truncate(6)")?

In code, you can use functions  such as:

During debugging, you can see the Type of everything in the locals
window if you set a breakpoint.

>Is vba  in any way a typed

Yes, but it has a default type called Variant that can morph into
whatever type is expected by the code. Unless declared otherwise,
variables are Variants.

>Once that is done, how do I access  the values  in the range?

Here is an example  of a function  that returns Boolean values (e.g.
to the spreadsheet or another function or subroutine):

Function Test2(x)
a = x 'this is a trick to convert a range  object to a variant
NRows = UBound(a, 1)
NCols = UBound(a, 2)
ReDim b(1 To NRows, 1 To NCols)
For r = 1 To NRows
For c = 1 To NCols
b(r, c) = a(r, c) < 100
Test2 = b
End Function

>Finally, I want to return a new range that is a subset of

The above example returns values (e.g. for use in the spreadsheet).
If you want to return a Range Collection instead of values (e.g. for
use in another code block), look at the Range Collection, Offset and
Union methods in Excel help.

Didn't find what you were looking for? Find more on User Defined Functions Or get search suggestion and latest updates.