Write to module from Addin

Add-in Express™ Support Service
That's what is more important than anything else

Write to module from Addin
 
nwein




Posts: 577
Joined: 2011-03-28
This whole new topic is new to me - is it possible (and if so how) to write and interact with VBA modules from my Add-in (either COM or XLL)? I'm referring to Excel in this case.
If it is possible, then it seems like if I'd want to save my workbook it'll have to be as an xlsm due to the fact that I have written VBA code (or something like that, although I don't intend on it being macros just data). Is it possible to keep the file in whatever format the user select and still have that code in it or would I need to create some sort of XLAM (similar to Microsoft's Solver)?
Basically I'm trying to store Add-in related data on the sheet/workbook itself.
Do you have any guidelines or some clues as to how I should get started on it?

This is for Excel 2010 32-bit, ADX 7.1.0450

Thanks in advance
Posted 30 Jan, 2013 12:26:15 Top
Andrei Smolin


Add-in Express team


Posts: 18787
Joined: 2006-05-11
Hello Nir,

You can create VBA macros using the VBIDE object model; Add-in Express adds such a reference when creating a new project. Note that you must have the flag below checked (Office 2010):

File | Options | Trust Center | Trust Center Settings | Macro Settings | Trust access to the VBA project object model.

I've tested renaming a .XLSM to .XLSX and opening it: this produces "Excel cannot open the file '{file name}' because the file format or file extension is not valid. Verify that the file has not been corupted and that the file extension matches the format of the file". Note that you can bypass this by using the .XLS format.

Another way to store data in Excel is to use a hidden worksheet: theWorksheet.Visible =
Excel.XlSheetVisibility.xlSheetVeryHidden, not xlSheetHidden!!! This setting does not let the end user make the worksheet visible using the Excel UI options. An experienced user may use a VBA macro, of course.


Andrei Smolin
Add-in Express Team Leader
Posted 31 Jan, 2013 06:01:53 Top
nwein




Posts: 577
Joined: 2011-03-28
Thanks Andrei, I guess I've removed the VBIDE reference as I didn't see any need for it in my project before.
I'll give it a shot, although the hidden sheet solution sounds more compelling (and probably easier).
Posted 31 Jan, 2013 10:08:00 Top