Evaluate defined names in .xlam files

[nymm]

Hello,

I'm writing an MS Excel plug-in with:

• a number of user forms
• a number of worksheets containing data
• a custom control on the ribbon to launch the plug-in

When the user clicks the ribbon control, the application first loads the data on the (hidden) worksheets into dynamic arrays. To do this, I use defined names which refer to the cell or range containing the data.

A greatly simplified example:

Please Login or Register  to view this content.

This code works like a charm as long as the file is an ordinary workbook (.xlsx). As soon as I convert it to a plug-in (.xlam), Excel throws a run-time error when trying to evaluate the defined name, like so:

Run-time error '13':
Type mismatch

I have no idea what is causing this, and more importantly, no clue on how to solve it.
Any suggestions will therefore be greatly appreciated!

Kind regards!

nymm

PS: Attached to this post is the example file described above. Open it to see the code working as intended. Save and then open it as an .xlam to see it go wrong.

[royUK]

Why have you got data in an addin. Addins are usually used to hold code.

[Bob Phillips]

TRy this

Please Login or Register  to view this content.

[nymm]

Thank you for the swift reply!

The code needs the data to work, in various ways:

• The interface is multilingual. Instead of duplicating all forms, or cluttering up the code with a plethora of UI-text, I've moved all language-dependent data to a table on a worksheet. Upon opening the plug-in, the code identifies the user's language (by reading the language settings of MS Office), and reads the corresponding UI-text into an array. The code then retrieves all language-dependent data from this array by referring to the index.
• The application's purpose is to calculate financial data, based on a number of user-submitted parameters. The calculation method itself is always the same, regardless of the user input, but the initial figures to be used in the calculation vary, based on the time period for which the user requests data. Again, for reasons of efficiency and clarity, I've coded a single calculation subroutine, which refers by index to an array containing the initial figures that apply for the requested period. Whenever the user changes the requested period, this array is reinitialized, and the correct figures are loaded from a table on a worksheet.

I hope this clarifies my reasoning.

[nymm]

Great! That works for the supplied example. I've tried to apply your solution to my actual project, though, and there I've encountered a new problem.

The problem is that not all defined names in my project refer to a range.

The LanguageLenght name, for instance, refers to =COUNTA(LanguageData!$H:$H)

Using the solution you supplied, I predictably get the following error:

Run-time error '1004':
Application-defined or object-defined error

The original code, Evaluate(ThisWorkbook.Names("LanguageLength").Value), doesn't give any errors, but doesn't return the correct value either. Instead, the corresponding integer variable is set to 1.

[Bob Phillips]

Try this then

Please Login or Register  to view this content.

[nymm]

Hi,

I've had no success implementing your solution, unfortunately. Instead I've replaced all defined names that refer to a formula (like =COUNTA(LanguageData!$H:$H)) with names that refer to a cell containing that same formula. By no means an elegant solution, but so far, it seems to work.

I've been thinking though: There might be a much more efficient way of loading my data into arrays than what I'm currently doing:

Please Login or Register  to view this content.

What I'm wondering: Is it possible (and would it be better) to make use of the fact that Excel tables (and their columns) are named ranges themselves?


Kind regards,


nymm

[snb]

I had to change the named range to cel B1:

This suffices (using the essence of named ranges)

Please Login or Register  to view this content.

But my preference (no need to define a 'range' of 1 cell):

Please Login or Register  to view this content.

The languagedata can be retrieved and put into an array sn:

Please Login or Register  to view this content.

[nymm]

No doubt my own doing, but I ran into more errors trying to implement your suggestions. I'm going to leave well enough alone for now, and mark this thread as solved.

Thank you all for your help!

Kind regards,

nymm