vba - How can I calculate the average of 1 variable of Array of Custom Type -


this tricky 1 me explain, i'll start code have far, , later trying achieve.

current code

option explicit  public esigtickerarr variant  ' public type save array type watchlist     esigticker string     op double     hi double     lo double     cl double     vol double     bartime variant end type public watchlistarr() watchlist ' save array of special type "watchlist"  '====================================================================  sub mainr()  redim watchlistarr(0) ' init array size esigtickerarr = array("part1", "part2", "part3")   each esigtickerelem in esigtickerarr      ' check if first member of array occupied     if watchlistarr(0).esigticker <> "" ' not first time running code >> increase array size 1         redim preserve watchlistarr(ubound(watchlistarr) + 1) ' increase array size 1     end if      ' ... code, working fine ....      ' populate array type data (also works)     watchlistarr(ubound(watchlistarr))         .esigticker = esigtickerelem         .op = lastcsvline(2)         .hi = lastcsvline(3)         .lo = lastcsvline(4)         .cl = lastcsvline(5)         .vol = lastcsvline(6)         .bartime = lastcsvline(1)     end  next esigtickerelem  ' ******* calculate average of "hi"  ****** dim myavg  myavg = worksheetfunction.average(watchlistarr.hi) '<--- getting error !  end sub 

i'm getting error @ line above.

my challenge: want average of variable of type array watchlistarr, , don't want use loop, there can 10,000 records (or more).

is there way value average function ?

should switch 2-d array ? or maybe 3-d array ?

myavg = worksheetfunction.average(watchlistarr.hi) '<--- getting error ! 

yep. code means similar this:

myavg = watchlistarr.select(item => item.hi).average(); 

where item => item.hi selector function invoked for every item in watchlistarr. problem linq / c#, not vba. vba doesn't support delegates , other funky stuff c# couldn't dream in v1.0.

but vba has control flow structures let perform action for every item in array: use for loop!

dim long, total double, count long = lbound(watchlistarr) ubound(watchlistarr)     total = total + watchlistarr(i).hi     if watchlistarr(i).hi <> 0 count = count + 1 'assuming zeroes excluded next if count <> 0 myavg = total / count 

if want use application.worksheetfunction.average, you'll need copy hi member of every item in array own array, , give that array - , require... loop... wasted cycles if loop isn't also computing average goes.

as long you're not using for each loop iterate array, you'll fine. iterating 30k items array for loop pretty instant, no worries there.


Comments

Popular posts from this blog

android - InAppBilling registering BroadcastReceiver in AndroidManifest -

python Tkinter Capturing keyboard events save as one single string -

sql server - Why does Linq-to-SQL add unnecessary COUNT()? -