Receive a column of values in a function

blesson2k · Newbie Joined: 06 Jun 2004 Posts: 3

How do I receive a colum of values as an array in a macro? for eg:

dfrench · Posted: Tue Jun 08, 2004 1:42 am Post subject:

I have moved this to the appropriate form. Please read the conditions for posting in Coce Snippets.

It is not clear what you are attempting to achieve by this code.
Is it intended as a user function for CALC spread sheet formulas?
You are not assigning a return value of any kind to StatusByName.
You are not doing anything with count

blesson2k · Newbie Joined: 06 Jun 2004 Posts: 3

Sorry for posting it in the wrong place. Yes I am returning the count value. I forgot to add that in the code. And yes, it is intended as a user function. I need to get the values in the colum that I give as a string array.

Cybb20 · Posted: Tue Jun 08, 2004 5:17 am Post subject:

I think your mistake is that you use NameCol(i) and StatusCol(i) as arrays, but they aren't even declared as arrays.
But for giving you the correct answer to your problem I would like to get more information.
_________________
- Knowledge is Power -

blesson2k · Newbie Joined: 06 Jun 2004 Posts: 3

Ok, it's something like this. Suppose I have a calc spreadsheet as follows:

dfrench · Posted: Wed Jun 09, 2004 12:28 am Post subject:

If you are interested in writing functions for CALC, you will need to understand what you are being passed ... see dannyb's explanation here: http://www.oooforum.org/forum/viewtopic.php?t=2929

I do not recommend writing new functions where a simple formula will do the job.
There are probably many ways of doing the task that you expressed here as a formula. Database functions are the general solution to this kind of problem. These are fully documented in the HELP.
An alternative approach is outlined in the HELP for the SUM function. If you compare an array with a single cell, the result is an array of true/false or 1/0 values. So assuming your example data is place in A1:C6 and the name and status values for comparison are in E2:F2 then the formula =SUM((A2:A6=E2)*(C2:C6=F2)) will provide the correct count,

r0bber · Posted: Sun Jul 04, 2004 12:08 am Post subject: Function & array variables

I have a somewhat similar problem.

i have a Excel sheet that holds a register of drawing issues. What I need to do (in OOB) is work out what is the latest issue id.
A B C D E F
Dwg1 a b 0 1 A all these are valid issue id's set by the project manager.
Dwg2 A B 0 B note the blank cell in col B, means wanst issued.

I have =userfn(B1:F1) that is to return the latest issue id based upon a formula.
ie B > A > 1 > 0 > b

This works ok in excel as the userfn works on a range object, each cell of which can be tested for ISEMPTY.

However, in OO the userfn works on an array of variant values. This initself is no problem except that a blank cell is reurned as value 0, type double.
I cant work out how to tell the difference between empty and the valid 0 id.
I could force all id to be strings but this is likely to cause user confusion.

Any ideas ?