i have been trying use vba couple of months now, i'm not sure code efficiently when comes speed.
the below code calculation of zscores used in ranking model stocks. calculation quite simple, calculation of zscore=zscore1*weight1+zscore2*weight2....zscoren*zscoren, zscores in different sheets , weights contained in array. code works, 500 stocks , 103 periods takes 30 seconds complete. looking advice speed code / make more "correct" in terms of programming practices.
i know code bít messy, since works hoped general advice on way use loops, if-sentenses , arrays.
public factor() single sub zscores() dim starttime double, endtime double dim sheetnames() string dim r integer, integer dim antalaktier integer, perioder integer dim zscore single starttime = timer worksheets("zscores").range("b2:aaa1000").clearcontents 'perioder , antalaktier variables determine number of stocks , periods perioder = application.worksheetfunction.counta(worksheets("returns").range("a2:a1500")) antalaktier = application.worksheetfunction.counta(worksheets("returns").range("b1:aaa1")) 'makes array of sheetnames r = 1 = 0 redim sheetnames(0) until worksheets("bloomdataflds").cells(r, 1).value = "" sheetnames(i) = worksheets("bloomdataflds").cells(r, 1).value = + 1 redim preserve sheetnames(i) r = r + 1 loop 'factor() array of values textboxes in userform 'code uses sheetnames array jump between sheets , making weighted average of cell values , factor array values k = 2 k = 2 antalaktier + 1 r = 2 r = 2 perioder + 1 zscore = 0 = 0 (ubound(factor) - 18) zscore = zscore + (factor(i) * worksheets(sheetnames(i)).cells(r, k).value) next 'truncates value max/min +/- 3 if worksheets("binær").cells(k, r).value = 1 , worksheets("returns").cells(r, k).value <> "#n/a n/a" if zscore < 3 , zscore > -3 worksheets("zscores").cells(r, k).value = zscore elseif zscore < -3 worksheets("zscores").cells(r, k).value = -3 elseif zscore > 3 worksheets("zscores").cells(r, k).value = 3 end if else: worksheets("zscores").cells(r, k).value = "" end if next r next k endtime = timer msgbox "execution time in seconds: " + format$(endtime - starttime) end sub
generally speed code add
application.screenupdating = false to start of code &
application.screenupdating = true to end.
i guess using vbas count function have @ least better performance excel's counta. instead of
perioder = application.worksheetfunction.counta(worksheets("returns").range("a2:a1500")) you might better off using
perioder = worksheets("returns").range(range("a2"),range("a2").end(xldown)).count (i assuming there should not gaps considering you're loop ends when cell empty).
multiple redims slowing down remove
redim preserve sheetnames(i) from loop & change
redim sheetnames(0) to
redim sheetnames(perioder) also
if zscore < 3 , zscore > -3 worksheets("zscores").cells(r, k).value = zscore elseif zscore < -3 worksheets("zscores").cells(r, k).value = -3 elseif zscore > 3 worksheets("zscores").cells(r, k).value = 3 end if would more efficient
with worksheets("zscores").cells(r, k) if zscore < 3 , zscore > -3 .value = zscore elseif zscore < -3 .value = -3 elseif zscore > 3 .value = 3 end if end hope helps.
Comments
Post a Comment