# Hello, I have a libreoffice basic programming question.

This is a function i want to use to predict the amount of money lost inflation after a number of years. option compatible

    Function INFLATION_ADJUSTMENT(ParamArray xData(),ParamArray yData(),AMT,year) As Double
dim r as single
dim r0 as single
dim Amplitude as single
dim Period as single

dim delta_X as single
dim delta_Y as single
dim Y as single
dim H as single
dim V as single
dim Inflation_Rate0 as single
dim Inflation_Rate as single

delta_X = xData(1)-xData(0)
delta_Y = yData(1)-yData(0)
Y = delta_Y/delta_X REM The Derivative of y

Amplitude = Math.SQRT(yData(0)^2+Y^2)
Period = 2*Math.PI()/(UBOUND(xData))
H = Math.ASIN(yData(0)/Amplitude)

REM The Average of the yData = the verticle shift constant
For n = 0 to UBOUND(xData)
V = V + yData(n)/UBOUND(xData)
Next n

For i = 1 to year
Inflation_Rate0 = (Amplitude*Math.SIN(Period*(0)+H)+V)
Inflation_Rate = (Amplitude*Math.SIN(Period*(i)+H)+V)
r0 = AMT*Inflation_Rate0/100
r = r0 + r*Inflation_Rate/100
Next i
End Function


As you may know, I am getting an error on the line of code where I try to compute xData(1)-xData(0). The Error is: Inadmissible Value or Data Type. Index out of defined range.Any way I could make this better?

edit retag close merge delete

1

an error

which error? and how are you calling the function?

( 2018-10-05 19:39:41 +0100 )edit

The Error is: Inadmissible Value or Data Type. Index out of defined range.

( 2018-10-05 20:44:04 +0100 )edit
1

quoting myself:

and how are you calling the function?

( 2018-10-05 20:46:09 +0100 )edit

I am calling the function in the from within the spreadsheet as option compatible. I hope that helped.

( 2018-10-05 20:47:34 +0100 )edit
1

sigh... no that haven't helped. If I were asked how I call a function, I'd try to provide arguments, and context, like "I have this in A1: XX; A2: YY; A3: MY_FUNCTION(A1; A2; 13)"...

( 2018-10-05 20:51:42 +0100 )edit

Well, The function is not in a procedure or anything like that. It was just meant to be a simple macro function.

( 2018-10-05 20:54:49 +0100 )edit
1

Then what do you expect the function's arguments to be? your code tries to get the arguments' values, namely (in the failing line) elements of array xData - but where should it get the data from, if you haven't provided the arguments?

( 2018-10-05 20:58:52 +0100 )edit

Okay. for the arguments, I was putting in for xData values 0 to 22 with an increment of 1. the yData is just the function result of the xData values in the spreadsheet. I called it in the spreadsheet as INFLATION_ADJUSTMENT(A1:A22, B1:B22, C2, D2).

( 2018-10-05 20:59:40 +0100 )edit
1

so, please make it crystal clear here:

1. Your A1:A22 contain 0 to 22 with step 1? (That's impossible)
2. Your B1:B22 contain what values exactly?
3. Your C2 and D2 contain what values?
4. The formula =INFLATION_ADJUSTMENT(A1:A22, B1:B22, C2, D2) is in what cell?
( 2018-10-05 21:07:48 +0100 )edit

the y data is 2+cos(A1). 2+cos(A2) .....etc. C2 = 15000 D2 = 20.

( 2018-10-05 21:22:14 +0100 )edit

Sort by » oldest newest most voted

The ParamArray compatibility argument must not be used here, since it is used to pack all the function's arguments into a single array. In the form of your initial signature

Function INFLATION_ADJUSTMENT(ParamArray xData(),ParamArray yData(),AMT,year) As Double


the call like =INFLATION_ADJUSTMENT(A1:A22;B1:B22;C2;D2) will give you xData being 4-element array with its first element being the array of A1:A22 values; xData's second element would be array of B1:B22 values; third element would be a scalar value of C2, and fourth element - scalar of D2. yData, AMT, and year would be empty.

After you fixed your function's signature to

you need to remember that range value is not a 0-based vector, but a 2-dimensional 1-based matrix (because a range may be more than 1 column wide); so for range like A1:A22, it will contain xData(1,1), xData(2,1), xData(3,1), etc., not xData(0), xData(1), etc.

You also need to replace calls to Math.Foo functions with their StarBasic equivalents. Namely, use Scr instead of Math.SQRT; Pi instead of Math.PI; and Sin instead of Math.SIN. Unfortunately, there's no ASIN equivalent in StarBasic; you'd need to invent how to use arctangent (Atn) instead.

more