arrays - Defining an `n` length nested loop in VBA -


building portfolio asset allocator, assets a, b, c, d, , want expand n assets. 4 assets, can run 4 nested loops. issue create n nested loops, , apply constraint, detailed below, , middle nest, allocates array current asset distribution. constraint (a + b + c + ... + n) <> 100 i.e. 100% allocation across assets.

the asset step sizes arbitrary (5 or 1) there minimum , maximum bounds on each n assets.

the asset sum 100 constraint knocks out many of iterations.

what interesting in question, allocarray requires position of each {a, b, c, ... , nasset} i.e. location of each nested loop. think makes exciting puzzle.

below nassets = 4 i.e. {a,b,c,d}: 

minmaxstepparam = range("a1:c4")    min max step    5 100 5 b    5 100 5 c    5 100 5 d    5 100 5   dim sim long: sim = 1  dim allocarray() variant redim allocarray(1 nsim, 1 nasset + 1)  dim double dim b double dim c double dim d double  = amin amax step astep     b = bmin bmax step bstep     'if (a + b) > 100 goto endb ' not required middle nest bound catches         c = cmin cmax step cstep         'if (a + b + c) > 100 goto endc ' ditto             d = dmin dmax step dstep                         'constraints                         if (a + b + c + d) <> 100 goto endd                         debug.print sim; a; b; c; d                         allocarray(sim, 1) = sim                         allocarray(sim, 2) =                         allocarray(sim, 3) = b                         allocarray(sim, 4) = c                         allocarray(sim, 5) = d                         sim = sim + 1 endd:             next d endc:         next c endb:     next b next  printarray allocarray, activeworkbook.worksheets("output").[a1]  sub printarray(data variant, cl range)     cl.resize(ubound(data, 1), ubound(data, 2)) = data end sub

i have produced dynamic loop, there error. can vba handle using array locations min, max , step of loop? there alternative?

sub configurearraydylan()  dim param() variant: param = range("m3:o6")  ' 4x3 with...min max step... {5 100 5; 5 100 5; 5 100 5; 5 100 5}. dim nasset long: nasset = ubound(param) ' count {a, b, ... , d } = 4 debug.print nasset  dim asset double dim value double dim sim long: sim = 1  dim nsim long: nsim = 1 dim nsimstep long = 1 nasset step 1     nsimstep = (1 + (param(i, 2) - param(i, 1)) / param(i, 3))  '(1 + (amax - amin) / astep) *     debug.print i; nsimstep     nsim = nsim * nsimstep next debug.print nsim  dim allocarray() variant redim allocarray(1 nsim, 1 nasset + 1)  asset = 1 nassets step 1     value = param(asset, 1) param(asset, 2) step param(asset, 3)         debug.print value; ' failure here         allocarray(sim, asset) = value         sim = sim + 1     next value next asset debug.print sim;  printarray allocarray, activeworkbook.worksheets("test").[l18] 'range("d30").resize(numrows, numcols).value = allocarray  end sub


Comments

Post a Comment

Popular posts from this blog

asynchronous - C# WinSCP .NET assembly: How to upload multiple files asynchronously -

i using winscp .net assembly uploading files. want upload multiple files asynchronous way. have created method works single upload. public class uploadingdata { private sessionoptions _sessionoptions; private session _session; //connection etc private void onconnected() { foreach (var fileinfo in localfileslist) { var task = task.factory.startnew(() => uploadfilesasync(fileinfo)); } } private async task uploadfilesasync(string file) { string remotefilepath = _session.translatelocalpathtoremote(file, @"/", "/test_data_upload"); var uploading = _session.putfiles(file, remotefilepath, false); //when done await task.run(() => thread.sleep(1000)); } } please suggest me correct way. thanks you can use backgroundworker class this: string[] images = new string[50]; foreach (var path in images) { backgroundworker
Read more

aws api gateway - SerializationException in posting new Records via Dynamodb Proxy Service in API -

i getting "__type": "com.amazon.coral.service#serializationexception" as reply in postman & in test console in api gateway trying post record directly dynamodb using api proxy services.. referring aws article - https://aws.amazon.com/blogs/compute/using-amazon-api-gateway-as-a-proxy-for-dynamodb/ here's mapping { "tablename": "tablenamegoeshere", "item": { "id" : "$context.requestid" "eventname" : "$input.path('$.eventname')", "timestamp" : $input.path('$.timestamp'), "answers": "$util.parsejson($input.path('$.answers'))" } } update: did asked ... , worked if try add array of json objects gives me above same error - here's trying now. please - couldnt find on google well #set($inputroot = $input.path('$')) { "tablename": "answer", "item": {
Read more

asp.net - Problems sending emails from forum -

i have been redesigning site has message board, has been written guy looked after site. it has few problems , written in .asp , uses .mdb files database. you can see whole thing here . feel free post if helps, test thing have set using original files , new database. the basic problem when posts message, supposed email sent them, have posted it. when replies, originator supposed message, replier has been posted. i’d have thought @ point in process there should script/file in postmessage.asp? the postmessage.asp page below. <% option explicit %> <!-- #include virtual="common/adovbs.inc" --> <html> <head> <meta http-equiv="content-type" content="text/html; charset=windows-1252"> <meta name="generator" content="microsoft frontpage 4.0"> <meta name="progid" content="frontpage.editor.document"> <title>new page 2</title> <base target="_self"
Read more