OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Receive a column of values in a function

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API
View previous topic :: View next topic  
Author Message
blesson2k
Newbie
Newbie


Joined: 06 Jun 2004
Posts: 3

PostPosted: Tue Jun 08, 2004 1:08 am    Post subject: Receive a column of values in a function Reply with quote

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

Code:
Function StatusByName (NameCol, StatusCol, CompName, CompStatus) as long
Dim count as long
for i = 0 to 190
   if ((NameCol(i) = CompName) And (StatusCol(i) = CompStatus)) then
      count = count + 1
   end if
Next i
End Function


I call the above function as StatusByName (C3:C195; H3:H195; A3; E2). In this case I do not get the values of the columns I specified. How do I get around this?

Thanx in advance
Back to top
View user's profile Send private message
dfrench
Moderator
Moderator


Joined: 03 Mar 2003
Posts: 1605
Location: Wellington, New Zealand

PostPosted: Tue Jun 08, 2004 1:42 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
blesson2k
Newbie
Newbie


Joined: 06 Jun 2004
Posts: 3

PostPosted: Tue Jun 08, 2004 2:40 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Cybb20
Super User
Super User


Joined: 02 Mar 2004
Posts: 1569
Location: Frankfurt, Germany

PostPosted: Tue Jun 08, 2004 5:17 am    Post subject: Reply with quote

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 -
Back to top
View user's profile Send private message Send e-mail
blesson2k
Newbie
Newbie


Joined: 06 Jun 2004
Posts: 3

PostPosted: Tue Jun 08, 2004 7:53 pm    Post subject: Reply with quote

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

Code:
Name       Job      Status

a         Job1      Completed
b         Job2      waiting
a         Job3      under review
c         Job4      Completed
b         Job5      Completed


Now I want to find the no: of jobs completed, under review and waiting by a, b and c in another sheet in the same file. So I wrote a function as follows:

Code:
Function StatusByName (NameCol, StatusCol, CompName, CompStatus)
Dim count as long
for i = 0 to 190
   if ((NameCol(i) = CompName) And (StatusCol(i) = CompStatus)) then
      count = count + 1
   end if
Next i
StatusByName = count
End Function


What I intended to do was send the name column to NameCol parameter in the function, status column to StatusCol parameter in the function and then the name I wanted (ie: a, b or c) to search for and the status I wanted to search for (ie: completed, waiting or under review). I hope it's clear now. Please do let me know if not.

Thanks
Back to top
View user's profile Send private message
dfrench
Moderator
Moderator


Joined: 03 Mar 2003
Posts: 1605
Location: Wellington, New Zealand

PostPosted: Wed Jun 09, 2004 12:28 am    Post subject: Reply with quote

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,
Back to top
View user's profile Send private message
r0bber
General User
General User


Joined: 03 Jul 2004
Posts: 14
Location: Brisbane, Australia

PostPosted: Sun Jul 04, 2004 12:08 am    Post subject: Function & array variables Reply with quote

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 ?
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group