Forum Discussion

Anonymous's avatar
Anonymous
Jun 17, 2020

VBA code for inputs

I've written a function in Matlab that calculates the average growth rate for i.g. a companys profit.
Profit is sometimes negative, so I've solved this "issue" with a couple of for-loops and if-statements, nothing fancy.

 

function[average]=Growth(y)
format shortG

growth=[];

for i=1:length(y)-1
    if y(i)<0 & y(i+1)<0
        growth(i)=y(i)/y(i+1);
    elseif y(i)>0 & y(i+1)>0
        growth(i)=y(i+1)/y(i);
    elseif y(i)<0 & y(i+1)>0
        growth(i)=(y(i+1)-(y(i))/(-y(i)));
    else
        growth(i)=0;
    end
end
format shortG
growth=(growth'-1)*100;
average=mean(growth)

end
format shortGgrowth=(growth'-1)*100;average=mean(growth)

 

I would like to create a vba function in excel that does this, but I'm not familiar with vba.
First and foremost I would like to be able to import the data into the vba function, obviously.
In my Matlab function the input is a vector y, and the output is average growth.

 

So what I need help with is to import the data from and Excel spreadsheet to a vba function, followed by one output. The input varies in size, do I need paramarray?
If someone could give me the first line of code it would be appreciated, the loops etc I can figure out myself.

excel
Formulas and Functions
Macros and VBA

1 Reply

  • Andrew_Duncan54's avatar
    Andrew_Duncan54
    Brass Contributor
    Jan 23, 2021

    Not quite sure what you are attempting, as I'm not familiar with Matlab.

    But the guts of your function should look much the same as what you have written:

    Function someFunction(y As Variant) As Variant

Dim growth() As Variant
Dim i As Long
ReDim growth(LBound(y) To UBound(y))
For i = 1 To UBound(y) - 1
    If y(i) < 0 And y(i + 1) < 0 Then
        growth(i) = y(i) / y(i + 1)
    ElseIf y(i) > 0 And y(i + 1) > 0 Then
        growth(i) = y(i + 1) / y(i)
    ElseIf y(i) < 0 And y(i + 1) > 0 Then
        growth(i) = (y(i + 1) - (y(i)) / (-y(i)))
    Else
        growth(i) = 0
    End
End
someFunction = growth
End Function

    If you paste your raw data into an excel spreadsheet as a single row or column. In another cell reference, the function "someFunction" (or whatever you have called it), for the function input, have the range of cells referring to your input data (i.e. A1:A100, etc), the above code will return an array of growth values.

    With the lower bit of code, not quite sure what that is doing. Excel has good inbuilt functions for generating AVERAGE. Either use excels Functions to process the output or put the cove into the VBA Function.

     

    Hope this helps.

Resources