Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

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

language?

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.

>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:

TypeName()

VarType()

IsObject()

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

>language?

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

array

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

Next

Next

Test2 = b

End Function

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

InitialRange.

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.

Related Topics:

- user defined functions
- User defined functions not recognized
- User Defined Function didn't work
- cells containing formula that refer to user-defined VBA function
- User Defined Functions for Financial Accounts
- defining variables in function
- user defined-help required
- Hibernate and User Defined Types Persistence
- user define packages
- Excel User Functions
- user-designed Excel math functions
- User Function - is optional calculation possibe?
- How does a user defined function know its cell address?
- Wait function and Invisible function
- Wait function and Invisible function
- Using a Defined Variable Range in a VBA SumIf
- The binomial coefficients can be defined as follows:
- defining subroutine in the main program
- java.util.Arrays not defined?
- Defining an event property in a usercontrol
- How can I call the function TotalPrice within another function?
- help on functions
- help requested for using excel's built-in function in coding
- Linest Function
- How to call a JNI function in C