Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

I've been reading the ExcelVBA list for a while, and all I see is

subroutines, forms, files, etc.

It's the same with the published books as far as I can tell.

Is there no interest in this group in user-designed Excel math functions?

As far as I can tell, such a function cannot write into a spreadsheet cell,

which is probably a good thing. I think it can read from a spreadsheet

cell, but that would defeat its purpose. It should get its input from the

argument list.

The books tell me how to output a row vector using

Public Function FunctionName(arg1 as type, ...) as Variant

...

FunctionName = Array(out1, out2, out3, ...)

End Function

But I only recently learned that I can also output a column vector or a

2-dimension array just by declaring the return data type as an array with

parentheses, and I'm wondering what else don't I know that I should know.

Suppose you want to compute a table of output values. Is it more efficient

to output a 2-D table of values into a highlighted range from a single

function call than to have one function call per row with each call

outputting a line of values as a row vector? Can you check the dimensions

of the highlighted output range? Such a function would require a column

vector for at least one of the input arguments, and the other arguments

might be single individual values that are the same for every row.

I taught myself to input arrays as ranges and to check them as to size in

the function. I generally copy the range values into local variables or a

local array of the appropriate type. Is that necessary?

I taught myself that if I make the return data type a Variant instead of a

Double, I can output an error message instead of a numerical answer. Do I

pay a penalty for that technique?

I have heard that VBA.NET has eliminated the Variant data type. That would

break most of my code. Does this mean I am prevented from using VBA.NET to

write user-defined Excel math functions?

Since I like object-oriented programming, and I have heard that VBA.NET

allows loading constant data arrays in the code at compile time, I am

interested in migrating to VBA.NET in Excel if that is possible. Does the

newest version of Excel support VBA.NET for user-defined functions? If so,

how do I tell the compiler which language to use?

I have MS Excel 2003 in my new laptop running on XP. But the VBA IDE looks

just like it does in Excel 2000 and my old code with Variant data types runs

just fine.

There are some great math functions here:

digilander.libero.it/foxes/SoftwareDownload.htm

Many are open source so you can learn a lot from them.

From what I heard, the Variant type is slower for computing compared

to the Double type. The simpler the types such as Single and Long

have less bit resolution, but are faster for math, however the

Variant is handy and saves code. For example, you can copy a variant

array with a simple A=B assignment. You can make a routine to time

calculations with the Timer function.

Didn't find what you were looking for?
Find more on user-designed Excel math functions
Or get search suggestion and latest updates.

Related Topics:

- Math function to determine if a number is an int
- Why math functions do not work?
- Excel User Functions
- help requested for using excel's built-in function in coding
- Excel User Form and Active Excel Work Sheets Question
- excel question from a basic excel user
- Excel User Form and Active Excel Work Sheets Question
- Excel won't calculate my function
- description/help with own VBA functions in Excel
- Excel Function
- Create Excel Function with Visual Basic
- ABS function in Excel
- VBA Function in Excel 2000
- Accessing help file info for non-Excel 2007 functions from VBA
- Excel Function
- VBA Excel Coding for dynamically changing function range
- User defined functions not recognized
- user defined functions
- User Function - is optional calculation possibe?
- User Defined Function didn't work
- User Defined Functions
- cells containing formula that refer to user-defined VBA function
- User Defined Functions for Financial Accounts
- Math statement process
- Math.pow()