Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Array Issue for An AddIn

Posted on 2011-03-24
10
Medium Priority
?
384 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:ronaldj
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 8

Expert Comment

by:ragnarok89
ID: 35208387
It may be clunky, but You could use a hidden worksheet to store all these temp values, one sheet per workbook.
0
 
LVL 20

Expert Comment

by:clarkscott
ID: 35208408
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


0
 
LVL 42

Accepted Solution

by:
dlmille earned 1000 total points
ID: 35209386
You can most definately create a defined name and store an array - in fact, until I got to that point in your comment, that's what I was going to suggest:

The below code stub is from another question I recently answered, and I was doing just that - storing an array into a defined name, for later retrieval.
Sub storeControlSettings(sControl As String)
Dim sBuildControlName As String
Dim sControlSettings(1 To 6) As Variant ' set to the number of control settings to be stored
       
    Set oControl = ActiveSheet.OLEObjects(sControl)
    
    'store the settings to retain, so they can be reset on demand, thus avoiding Excel's resizing "problem"
    'create array of settings to be stored, with order dictated by CONTROL_OPTIONS for consistency/documentation
    
    sControlSettings(1) = oControl.Height
    sControlSettings(2) = oControl.Left
    sControlSettings(3) = oControl.Locked
    sControlSettings(4) = oControl.Placement
    sControlSettings(5) = oControl.Top
    sControlSettings(6) = oControl.Width
    
    
    sBuildControlName = "_" & sControl & "_Range" 'builds a range name based on the control name
    
    Application.Names.Add Name:="'" & ActiveSheet.Name & "'!" & sBuildControlName, RefersTo:=sControlSettings, Visible:=False 'Adds the control's settings to the defined names area and hides the range name
    
End Sub

Open in new window

Enjoy,

Dave
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:ronaldj
ID: 35210039
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
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35210073
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
0
 
LVL 46

Expert Comment

by:aikimark
ID: 35211947
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.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35796930
>>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
0
 
LVL 24

Expert Comment

by:Tracy
ID: 35877394
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question