MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

user-designed Excel math functions

  Asked By: Asksuresh    Date: Feb 14    Category: MS Office    Views: 2389

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.



1 Answer Found

Answer #1    Answered By: Sammy Brown     Answered On: Feb 14

There are some great math  functions here:


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.