Need help on making a custom function for excel 2003

Question

0.00/5 (No votes)

See more:

Hi guys,
Today I am asking for your help.
I need to build a custom function in VBA6 for Excel 2003.
My problem, I need to pass 2 lists of same size as parameters 3 and 4. I have this code:

VB

function MyFunc(P1, P2, P3 As Range, P4 As Range) As Long
    ...

From a Cell, I can call it like =MyFunc("Alpha", 123, A1:A3, {1,2,3}), so far, so good.

The problem is that I also need the lists as {2, B1*2, C1}, and there I am stuck. My lists need to be a mix of cell references, numeric constants and formulas.I can't use cells to build the lists as ranges of cells.

How would you do ?
Should I use the ParamArray ?

What I have tried:

Googled a lot but did not found anything interesting.

Posted 4-Jul-16 10:20am

Patrice T

Updated 4-Aug-16 2:11am

v4

Add a Solution

Comments

Maciej Los 4-Aug-16 5:45am

Which list has to be {2, B1*2, C1}: first or second one? Why first argument of function is string data type if the function have to return Long data type?
Does the number of elements for lis1 and list2 is always the same?

Patrice T 4-Aug-16 6:22am

Found a solution using ParamArray.
Parameters 1 and 2 are what they are because it is needed, and the function returns a numeric value, so long data type.

Maciej Los 4-Aug-16 6:44am

So, if you you want to pass 2 lists (function accepts only one ParamArray argument), then you have to know the number of arguments and theis order. Am i right? Are you interesed in different solution?

Patrice T 4-Aug-16 6:48am

No thank, this solution satisfy all my needs.

Maciej Los 4-Aug-16 6:56am

I'd suggest to share your solution due to the fact that someone else can stack with the same issue... Then mark it as an answer to remove your question from unanswered list.

Patrice T 4-Aug-16 7:20am

My solution is rather specific.

Maciej Los 4-Aug-16 7:45am

Agree, but you don't have to post entire function. You should explain how to work around such of issue.

2 solutions

Solution 1

Computations in excel are set/cell based so you need to think in sets/cells i.e. break your computations into steps and put then in columns as intermediary values.

So for your example where some of your cells in the column need to be multiplied, create a new column with the computation for the multiplication and use the values of that column in the range for your formula.

In short : create more columns as intermediary values.

Posted 4-Jul-16 18:15pm

Mehdi Gholam

Comments

Patrice T 5-Jul-16 4:10am

Ok if I understand well, my solution is ParamArray with variable number of parameters but no list in parameters.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2016-08-04T02:11:00

As to the ParamArray... Yes, this is good way to resolve issue by using ParamArray when the number of paramaters in unknown. Note, that you can use only one ParamArray parameter in the function. So, in your case, you lose the ability to pass two lists.

If the number of elements on both lists is always the same, you can define function this way:

VB

Function MyFunc(ByVal a As String, ByVal b As Long, _
       ByVal x1 As Range, ByVal y1 As Range, ByVal z1 As Range, _
       ByVal x2 As Range, ByVal y2 As Range, ByVal z2 As Range) As Long
    'body of function
End Function

This is common practice. Take a look at LINEST function[^] in MS Excel.

Unfortunately, there's no way (as per my knowledge) to pass user defined type (UDT) to worksheet function:

VB

Function MyFunc(ByVal a As String, ByVal b As Long, _
       ByVal x1 As MyPoint, ByVal x2 As MyPoint) As Long
    'body of function
End Function

where MyPoint is UDT (structure, class, etc.)