ronaldj
asked on
Array Issue for An AddIn
I am in the process of writing an addin which needs to work with several workbooks open. Everything was OK until I came to the following issue...on wb1, user selects 2-3 items from a listbox (e.g., a,b,c); public array is successfuly built and info available for later processing...life is good. this works. HOWEVER, user opens wb2 (with wb1 still open), selects, e.g., a,d...array is built and for wb2 info retained and available. Well, user goes back to wb1 and a,b,c has been rteplaced by a,d. Unfortunately makes sense. Since I programmatically can identify each wb name, should I maybe write array values to wb specific TempFolder, then Read from folder when necessary...eventually Kill temp folders? It would be easier if I could create a wb specific name array but my initial attempt seems to indicate this can't be done. Thoughts please.
It may be clunky, but You could use a hidden worksheet to store all these temp values, one sheet per workbook.
Perhaps you're losing the ubound array value?.
Your app may be 'starting over' when assigning new items to the array. You should be 'adding' to the upperbound value.
(Pseudo-code)
Public ArrayCnt as long
Add to the array....
ArrayCnt = ArrayCnt + 1
YourArray(Arraycnt) = YourNewValue
To start over, you must erase the array AND reset the public variable, ArrayCnt =0 .
??
Scott C
Your app may be 'starting over' when assigning new items to the array. You should be 'adding' to the upperbound value.
(Pseudo-code)
Public ArrayCnt as long
Add to the array....
ArrayCnt = ArrayCnt + 1
YourArray(Arraycnt) = YourNewValue
To start over, you must erase the array AND reset the public variable, ArrayCnt =0 .
??
Scott C
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Dave:
What if I am prohibited from wring to the wb(s) OR adding a hidden sheet as suggested by ragnarok89 earlier? Since I posted this AM I have successfully written code which WRITES the current elements from the open wb array into a wb specific temp folder / text file. When back from dentist will work on READ code. Thanks, will keep you posted if you wpould like.
Ron
What if I am prohibited from wring to the wb(s) OR adding a hidden sheet as suggested by ragnarok89 earlier? Since I posted this AM I have successfully written code which WRITES the current elements from the open wb array into a wb specific temp folder / text file. When back from dentist will work on READ code. Thanks, will keep you posted if you wpould like.
Ron
Why would you be prohibted from writing to the workbooks?
>>It would be easier if I could create a wb specific name array but my initial attempt seems to indicate this can't be done. Thoughts please.
Anyway, I was just addressing your perceived problem with writing arrays to defined names, which in your question said would be "ideal" As a result, I have submitted a proposed solution which you stated would be "easier".
Cheers,
Dave
>>It would be easier if I could create a wb specific name array but my initial attempt seems to indicate this can't be done. Thoughts please.
Anyway, I was just addressing your perceived problem with writing arrays to defined names, which in your question said would be "ideal" As a result, I have submitted a proposed solution which you stated would be "easier".
Cheers,
Dave
you might
* use a dictionary object to store the different arrays by the wb.name as the key.
* use a variant array, where one of the dimensions is a string = wb.name
* use either dictionary or collection object to store string values that include both the wb.name and the numeric value. You would parse the string for eligible items to recover/use the numeric data.
* use a dictionary object to store the different arrays by the wb.name as the key.
* use a variant array, where one of the dimensions is a string = wb.name
* use either dictionary or collection object to store string values that include both the wb.name and the numeric value. You would parse the string for eligible items to recover/use the numeric data.
>>It would be easier if I could create a wb specific name array but my initial attempt seems to indicate this can't be done. Thoughts please.
I thought we provided some good solutions, and mine was directly in response to the original question, above, correctly. Other good alternatives were also provided.
Dave
I thought we provided some good solutions, and mine was directly in response to the original question, above, correctly. Other good alternatives were also provided.
Dave
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.