Ask Your Question
0

Hello, I have a libreoffice basic programming question.

asked 2018-10-05 19:00:44 +0100

JackZed gravatar image

updated 2018-10-05 20:45:08 +0100

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
    INFLATION_ADJUSTMENT = r
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 flag offensive close merge delete

Comments

1

an error

which error? and how are you calling the function?

Mike Kaganski gravatar imageMike Kaganski ( 2018-10-05 19:39:41 +0100 )edit

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

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

quoting myself:

and how are you calling the function?

Mike Kaganski gravatar imageMike Kaganski ( 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.

JackZed gravatar imageJackZed ( 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)"...

Mike Kaganski gravatar imageMike Kaganski ( 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.

JackZed gravatar imageJackZed ( 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?

Mike Kaganski gravatar imageMike Kaganski ( 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).

JackZed gravatar imageJackZed ( 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?
Mike Kaganski gravatar imageMike Kaganski ( 2018-10-05 21:07:48 +0100 )edit

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

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

1 Answer

Sort by » oldest newest most voted
2

answered 2018-10-05 21:52:53 +0100

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

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

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.

edit flag offensive delete link more

Comments

Thank You.

JackZed gravatar imageJackZed ( 2018-10-05 21:58:38 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-10-05 19:00:44 +0100

Seen: 69 times

Last updated: Oct 05 '18