VBA code for inputs

%3CLINGO-SUB%20id%3D%22lingo-sub-1470813%22%20slang%3D%22en-US%22%3EVBA%20code%20for%20inputs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1470813%22%20slang%3D%22en-US%22%3E%3CP%3EI've%20written%20a%20function%20in%20Matlab%20that%20calculates%20the%20average%20growth%20rate%20for%20i.g.%20a%20companys%20profit.%3CBR%20%2F%3EProfit%20is%20sometimes%20negative%2C%20so%20I've%20solved%20this%20%22issue%22%20with%20a%20couple%20of%20for-loops%20and%20if-statements%2C%20nothing%20fancy.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3Efunction%5Baverage%5D%3DGrowth(y)%0Aformat%20shortG%0A%0Agrowth%3D%5B%5D%3B%0A%0Afor%20i%3D1%3Alength(y)-1%0A%20%20%20%20if%20y(i)%26lt%3B0%20%26amp%3B%20y(i%2B1)%26lt%3B0%0A%20%20%20%20%20%20%20%20growth(i)%3Dy(i)%2Fy(i%2B1)%3B%0A%20%20%20%20elseif%20y(i)%26gt%3B0%20%26amp%3B%20y(i%2B1)%26gt%3B0%0A%20%20%20%20%20%20%20%20growth(i)%3Dy(i%2B1)%2Fy(i)%3B%0A%20%20%20%20elseif%20y(i)%26lt%3B0%20%26amp%3B%20y(i%2B1)%26gt%3B0%0A%20%20%20%20%20%20%20%20growth(i)%3D(y(i%2B1)-(y(i))%2F(-y(i)))%3B%0A%20%20%20%20else%0A%20%20%20%20%20%20%20%20growth(i)%3D0%3B%0A%20%20%20%20end%0Aend%0Aformat%20shortG%0Agrowth%3D(growth'-1)*100%3B%0Aaverage%3Dmean(growth)%0A%0Aend%0Aformat%20shortGgrowth%3D(growth'-1)*100%3Baverage%3Dmean(growth)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20to%20create%20a%20vba%20function%20in%20excel%20that%20does%20this%2C%20but%20I'm%20not%20familiar%20with%20vba.%3CBR%20%2F%3EFirst%20and%20foremost%20I%20would%20like%20to%20be%20able%20to%20import%20the%20data%20into%20the%20vba%20function%2C%20obviously.%3CBR%20%2F%3EIn%20my%20Matlab%20function%20the%20input%20is%20a%20vector%20y%2C%20and%20the%20output%20is%20average%20growth.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20what%20I%20need%20help%20with%20is%20to%20import%20the%20data%20from%20and%20Excel%20spreadsheet%20to%20a%20vba%20function%2C%20followed%20by%20one%20output.%20The%20input%20varies%20in%20size%2C%20do%20I%20need%20paramarray%3F%3CBR%20%2F%3EIf%20someone%20could%20give%20me%20the%20first%20line%20of%20code%20it%20would%20be%20appreciated%2C%20the%20loops%20etc%20I%20can%20figure%20out%20myself.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1470813%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Deleted
Not applicable

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.

0 Replies