Save/Embed arrays in an addin
So for single variables if I want to save it in VBA I just create a constant.
Is there something I can do to save an array or list in the document without making them populate cells?
It's only a few hundred items so I don't want to put it in a hidden worksheet or the file size will be bulked up and I will have to draw from it each time which is wasteful.
I was thinking maybe an XML file in the xlsb but I am not very good with markup languages.
Sub Reputation() Dim Problem as Variant Dim Reputation as Integer For Each Problem in Forum.Threads If Problem.Title = "*[Solved]*" and Solver.Name = "Leon V (AW)" Then Reputation = Reputation + 1 Next Problem End Sub
There are number of ways.
1. Store it in external text file and read from it upon workbook open event. Or some other trigger.
2. You could hard code it in VBA. But I don't see any benefit from it.
Ex:
There are other methods, but this will depend on your specific preference and need.Please Login or Register to view this content.
?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
― Robert A. Heinlein
The text file is essentially what I am looking for in XML the focus being it can be stashed inside the .xlam.zip rather than a separate file that has to be kept with it. It is for what functions users have access to so it can't just be a text file in the open that anyone can edit.
There are several ways.
Host text file in cloud for consumption by the code. Or just go with my 2nd suggestion.
Alternately, host the file in shared folder designated as read only on network location (if every user is on same domain).
There are other flavors, but same principle will hold, whichever method you wish to use.
You can store data in a "custom XML part" (google it). But it would be far simpler to use a hidden worksheet. Would that really take up a lot more size than XML, which is also bulky? I don't know the answer but I would try a worksheet first, and only resort to an alternative if I really needed to. If you're concerned about the time it takes to read from a worksheet, make sure you read the whole table into an array in a single VBA statement. I'd be surprised if that isn't fast enough. (And I'm not at all sure that reading from a file or XML part would be any quicker.)
(I used a custom XML part recently, but only because it was Word, not Excel, so I didn't have the option of using a worksheet.)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks