Add-ins have worksheets. You just can’t see them. But you can store information on them and it’s a good way to store settings, preferences, and other data. When you want to get to that data, you can go to the Properties for ThisWorkbook and change the IsAddin property to False. Now you can see the worksheets and change the data if necessary.
When you’re done, go back to the VBE and change the IsAddin property back to True before you save your changes. Don’t forget that part; it’s important.
I have a list of vendor codes stored on a worksheet in an addin. I need to see the list, but not change it. I didn’t want to go through all the IsAddin rigmarole, so I did this in the Immediate Window.
|
1 |
wshvendors.ListObjects(1).ListColumns(1).DataBodyRange.value |
That part returns a two-dimensional array of all the values in the first column
|
1 |
application.transpose(...) |
That turns a two-dimensional array into a one-dimensional array.
|
1 |
?join(...,",") |
That turns an array into a string with commas between the values. In retrospect, I should have used
|
1 |
?join(application.transpose(wshvendors.ListObjects(1).ListColumns(1).DataBodyRange.value),vbnewline) |
to get each code on its own line. Here’s a way to put it into a range, if that’s where you’re going with it anyway.
|
1 |
wshvendors.ListObjects(1).ListColumns(1).DataBodyRange.Copy workbooks.Add.Worksheets(1).cells(1,1) |
Another approach to viewing the worksheets (rather than changing the IsAddin parameter) is to develop the addin as an .xlsm workbook – and at the last minute (like when I publish and release the addin) save it as an .xlam workbook – then the worksheets are always visible while developing, but hidden when used in production.
Likewise, I often find myself using the immediate window to check cell values even when they are in view. For ranges of more than one cell, I’d usually enter into the immediate window initially for the intellisense and then drag to the watch window – this way you don’t need the join part to see the results.
Another option to see values on a hidden sheet is to copy to a new workbook using just: wshvendors.Copy. This also means you don’t have to unlock the addin (or better still to hack into someone elses locked worksheets :)
I’m with Lori on wshvendors.Copy. I just added that as a feature to an addin where I want the users to be able to “export” a listbox’s contents (the listbox is fed by a structured table in the addin.)
I used to see issues with saving to an addin when there was more than one Excel instance open. I’d get a copy of the addin saved to a seemingly random location (maybe the last location the user had saved to or opened from).
wshVendors.Copy – duh. So much easier than my method.
I hadn’t thought about the watch window. I wish I could expand the array in the watch window with the keyboard, but I haven’t figured out how.
Dick, doesn’t the enter key work for expanding the array – at least if it’s a 1d array?
Not sure if there’s any easy keyboard way for adding the watch though – one possibility could be to enter the following sequence of keystrokes in the line below of the immediate window:
Yes, enter does work. I thought I had tried that but must not have.
For adding the watch, I was using Alt+V+H, then use the right-click key and A (for Add). Alt+D+A is even better.