Hello, I have a libreoffice basic programming question.

JackZed · October 5, 2018, 5:00pm

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?

mikekaganski · October 5, 2018, 5:39pm

an error

which error? and how are you calling the function?

JackZed · October 5, 2018, 6:44pm

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

mikekaganski · October 5, 2018, 6:46pm

quoting myself:

and how are you calling the function?

JackZed · October 5, 2018, 6:47pm

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

mikekaganski · October 5, 2018, 6:51pm

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)”…

JackZed · October 5, 2018, 6:54pm

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

mikekaganski · October 5, 2018, 6:58pm

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?

JackZed · October 5, 2018, 6:59pm

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).

mikekaganski · October 5, 2018, 7:07pm

so, please make it crystal clear here:

Your A1:A22 contain 0 to 22 with step 1? (That’s impossible)
Your B1:B22 contain what values exactly?
Your C2 and D2 contain what values?
The formula =INFLATION_ADJUSTMENT(A1:A22, B1:B22, C2, D2) is in what cell?

JackZed · October 5, 2018, 7:22pm

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

JackZed · October 5, 2018, 7:26pm

okay, I will be as detailed as I can. A1:A22 contain 0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21.
B1:B22 contains 2+cos(0), 2+cos(1),2+cos(2), 2+cos(3),…,2+cos(21)
INFLATION_ADJUSTMENT(A1:A22, B1:B22, C2, D2) is in cell E3.

JackZed · October 5, 2018, 7:58pm

Thank You for the help. I was not using ParamArray Correctly. Thank You.

mikekaganski · October 5, 2018, 7:52pm

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.

JackZed · October 5, 2018, 7:58pm

Thank You.