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

cell range in Calc macro, Excel conversion

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
Jerry R Booker
Newbie
Newbie


Joined: 02 Dec 2005
Posts: 3

PostPosted: Fri Dec 02, 2005 2:27 pm    Post subject: cell range in Calc macro, Excel conversion Reply with quote

Iím trying to convert a rather complex Excel spreadsheet, that I didnít write, to Calc. There are several basic macros that are called many times, perhaps a total of 500 times, by the formula in various cells. Each call is with different cell range. Here is a typical cell formula that includes a macro call:

=Interpolate($main.$C$10:$C$20;$work.$D$10:$D$20;$D$5)

Here is the beginning of a typical Excel macro being called:

Function Interpolate(Xd, Yd, XS)
Dim Xdm(100) As Single, Ydm(100) As Single
On Error Resume Next
mind = 1
maxRows = Xd.Rows.Count
delta = 10000000
curval = 0
For i = 1 To Xd.Rows.Count
If Xd.Rows(i) <> "" And Yd.Rows(i) <> "" Then
curval = curval + 1
Xdm(curval) = Xd.Rows(i)
Ydm(curval) = Yd.Rows(i)
If Abs(Xd.Rows(i) - XS) < delta Then
delta = Abs(Xd.Rows(i) - XS)
mind = curval
End If
End If
Next I

It doesnít work that way in Calc. Calc canít deal with the Xd.Rows.Count and similar statements. I have tried various things with limited success. Any suggestions on a simple way to modify the macros to work in Calc. I donít want to change any of the hundreds of formulas in the cells.

Thanks in advance,
Jerry R
Back to top
View user's profile Send private message
ms777
Super User
Super User


Joined: 07 Feb 2004
Posts: 1355

PostPosted: Sat Dec 03, 2005 2:09 am    Post subject: Reply with quote

Hi,

below function demonstrates the passing of arrays to cell functions in OO. This differs in some aspects from the VB way ...

good luck,

ms777

call e.g. with = TestArrayFunc(A1:B6)

Code:
function TestArrayFunc(a as Any) as String
lRows = UBound(a, 1)
lCols = UBound(a, 2)

dUpperLeft  = a(1,     1)
dUpperRight = a(1,     lCols)
dLowerLeft  = a(lRows, 1)
dLowerRight = a(lRows, lCols)

s = "Rows: " & lRows & "; Cols: " & lCols & ";"
s = s & "UpperLeft: " & dUpperLeft & "; UpperRight: " & dUpperRight & ";"
s = s & "LowerLeft: " & dLowerLeft & "; LowerRight: " & dLowerRight & ";"
TestArrayFunc = s
end function
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 Calc 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