cell range in Calc macro, Excel conversion

[Jerry R Booker]

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

[ms777]

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)