excel vba - VBA calling a variable by name inside a loop -


ok here's deal: need comprehensive check in .csv file (comparing 1 in current sheet external one). decided divide list 10 equal sections (deciles). in each decile choose random value belonging section , use row number compare 2 sets of data.

where things fall apart inside function. looking way go through each decile (starting rand0) , have vba check whether values of .csv , data sheet in workbook equal. if not - function (called get_param) executed.

i dont quite understand how have vba go through function dec = 0 9 - in essence row number rand0 row number rand9 , perform inequality check (in second if function). rand & dec part not work. looking clues on how fix or on new implementation same thing.

a few more details: n number of rows in .csv file (equal couple of thousand). np number of rows in file (should equal n - if not, execute function). paramlocation designated automatically - should located in specific location.

sub check_changes_param()  dim dec integer call public_dims call deciles      set parambook = workbooks.open(paramlocation)      '==========checks if parameters.xlsm exists in correct location========== if paramlocation = ""     msgbox "the parameters.xlsm file not exist or in incorrect location. please ensure located in " & paramlocation     else         dec = 0 dec = 9             if thisworkbook.sheets("data").cells(rand & dec, 11) <> parambook.sheets("data").cells(rand & dec, 11) or n <> np                 call get_param                 exit             end if         next dec end if end sub  public sub deciles()  rand0 = int((n / 10) * 1) * rnd + 1 rand1 = int((n / 10) * 2 - (n / 10) * 1 + 1) * rnd + (n / 10) * 1 rand2 = int((n / 10) * 3 - (n / 10) * 2 + 1) * rnd + (n / 10) * 2 rand3 = int((n / 10) * 4 - (n / 10) * 3 + 1) * rnd + (n / 10) * 3 rand4 = int((n / 10) * 5 - (n / 10) * 4 + 1) * rnd + (n / 10) * 4 rand5 = int((n / 10) * 6 - (n / 10) * 5 + 1) * rnd + (n / 10) * 5 rand6 = int((n / 10) * 7 - (n / 10) * 6 + 1) * rnd + (n / 10) * 6 rand7 = int((n / 10) * 8 - (n / 10) * 7 + 1) * rnd + (n / 10) * 7 rand8 = int((n / 10) * 9 - (n / 10) * 8 + 1) * rnd + (n / 10) * 8 rand9 = int(n - (n / 10) * 9 + 1) * rnd + (n / 10) * 9  end sub

try instead:

sub check_changes_param()  dim dec integer call public_dims dim deciles variant deciles = decilesarray()  set parambook = workbooks.open(paramlocation)      '==========checks if parameters.xlsm exists in correct     location========== if paramlocation = ""     msgbox "the parameters.xlsm file not exist or in incorrect location. please ensure located in " & paramlocation else     dec = 0 ubound(deciles)         if thisworkbook.sheets("data").cells(deciles(dec), 11) <> parambook.sheets("data").cells(deciles(dec), , 11) or n <> np             call get_param             exit         end if     next dec end if end sub  public function decilesarray() variant randomize()  rand0 = int((n / 10) * 1) * rnd + 1 rand1 = int((n / 10) * 2 - (n / 10) * 1 + 1) * rnd + (n / 10) * 1 rand2 = int((n / 10) * 3 - (n / 10) * 2 + 1) * rnd + (n / 10) * 2 rand3 = int((n / 10) * 4 - (n / 10) * 3 + 1) * rnd + (n / 10) * 3 rand4 = int((n / 10) * 5 - (n / 10) * 4 + 1) * rnd + (n / 10) * 4 rand5 = int((n / 10) * 6 - (n / 10) * 5 + 1) * rnd + (n / 10) * 5 rand6 = int((n / 10) * 7 - (n / 10) * 6 + 1) * rnd + (n / 10) * 6 rand7 = int((n / 10) * 8 - (n / 10) * 7 + 1) * rnd + (n / 10) * 7 rand8 = int((n / 10) * 9 - (n / 10) * 8 + 1) * rnd + (n / 10) * 8 rand9 = int(n - (n / 10) * 9 + 1) * rnd + (n / 10) * 9 decilesarray= array(rand0,rand1,rand2,rand3,rand4,rand5,rand6,rand7,rand8,rand9) end sub

-

Comments

Post a Comment

Popular posts from this blog

amazon web services - S3 Pre-signed POST validate file type? -

i know it's possible put limit on file size content-length-range header. possible validate file type? https://docs.aws.amazon.com/amazons3/latest/dev/httppostforms.html#policyconditions i see there content-type header, if set say, audio/mp3 allow mp3 files , return error if file not mp3? i found previous question answers mention validating file size: s3 direct upload restricting file size , type you can specify content-type @ post request. you can specify @ signature, post must done content-type: { "expiration": "2007-12-01t12:00:00.000z", "conditions": [ {"acl": "public-read" }, {"bucket": "johnsmith" }, {"content-type: "audio/mp3"} ] } creating html form (using aws signature version 4) edit: @ previous question found, checking content-type.
Read more

c# - Check Keyboard Input Winforms -

i wondering if instead of doing this protected override void onkeydown(keyeventargs e) { if (e.keycode == keys.a) console.writeline("the key down."); } i set bool method , this: if(keydown(keys.a)) // whatever here i've been sitting here ages trying figure out how it. can't wrap head around it. in case wondering, plan call bool inside different method, check input. since want perform action after pressing key, using keydown event enough. but in cases suppose want check if specific key down in middle of process, can use getkeystate method way: [dllimport("user32.dll", charset = charset.auto, exactspelling = true)] public static extern short getkeystate(int keycode); public const int key_pressed = 0x8000; public static bool iskeydown(keys key) { return convert.toboolean(getkeystate((int)key) & key_pressed); } you should know, each time check key state using example iskeydown(keys.a) method returns true if ke...
Read more