hi, im working with vba and excel, and running through a list of items over and over.
i know this is quite generic, but i'm not having a coding issue itself,i'm trying to determine which methods should i use in different situations. please bear with my idiocy
let me see if i can summarize.
example of a list of items:
Article Stock
1 3
2 5
3 2
1 -1
I) let's say i have to run through that list and sum every article's stock; afterwards, i will put the resulting stock in every line, even if it's repeated
alternative 1: if i use a Range, (ex: for each r in range); what excel will do?
a. if, when loading, excel loaded the file to the RAM, then it should be fast enough.
b. on the contrary, excel should constantly go to look for the information on the hard disk, that's slower.
the most reasonable response is a. but i wonder what happens when the file is too large, it's not in a local harddisk or it's a shared excel file.
alternative 2: load the range into an array and rewrite it with the updated list. Of course this requeres to load it first and then rewrite it, but if excel didn't load the excel file to the memory should be faster, in a very long file.
if 1.a is true, then alt. 2 should be always wrong.
case II : the same thing, but loading from recordsets. this means there is not pre-written data on the excel file.
alternative 1: use the recordset for the calculations, and then write the result to a range.
well, maybe i'll have other limitations regarded to that object, but let's say i'll only need to use the movefirst, and findnext, and edit
alternative 2: paste the recordset to a range and use the range for the calculations; then copy the result to a new range
alternative 3: copy the recordset to an array, and use it for the calculation. this would be the equivalent of alt. 2 of case I. i don't think this one is right.
ok i don't want to do this even longer. if anyone can think of an answer of other variations to study, i'll be glad
---------------
Arides
Can God write a code so complex he couldn't understand?
i know this is quite generic, but i'm not having a coding issue itself,i'm trying to determine which methods should i use in different situations. please bear with my idiocy
let me see if i can summarize.
example of a list of items:
Article Stock
1 3
2 5
3 2
1 -1
I) let's say i have to run through that list and sum every article's stock; afterwards, i will put the resulting stock in every line, even if it's repeated
alternative 1: if i use a Range, (ex: for each r in range); what excel will do?
a. if, when loading, excel loaded the file to the RAM, then it should be fast enough.
b. on the contrary, excel should constantly go to look for the information on the hard disk, that's slower.
the most reasonable response is a. but i wonder what happens when the file is too large, it's not in a local harddisk or it's a shared excel file.
alternative 2: load the range into an array and rewrite it with the updated list. Of course this requeres to load it first and then rewrite it, but if excel didn't load the excel file to the memory should be faster, in a very long file.
if 1.a is true, then alt. 2 should be always wrong.
case II : the same thing, but loading from recordsets. this means there is not pre-written data on the excel file.
alternative 1: use the recordset for the calculations, and then write the result to a range.
well, maybe i'll have other limitations regarded to that object, but let's say i'll only need to use the movefirst, and findnext, and edit
alternative 2: paste the recordset to a range and use the range for the calculations; then copy the result to a new range
alternative 3: copy the recordset to an array, and use it for the calculation. this would be the equivalent of alt. 2 of case I. i don't think this one is right.
ok i don't want to do this even longer. if anyone can think of an answer of other variations to study, i'll be glad
---------------
Arides
Can God write a code so complex he couldn't understand?
