Excel Add-In - A value used in the formula is of the wrong datatype

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

Excel Add-In - A value used in the formula is of the wrong datatype
 
Larry Sater


Guest


I started using Add-In Express 2 weeks ago on a project and love it! Tonight I was doing some code cleanup and I'm sure I didn't modify the XLLModule internals (at least I hope), but now when I try to run my custom functions in Excel I get "A value used in the formula is of the wrong datatype). I have been in and out of Excel as I've been developing it a hundred times without a problem until now. I have 2 functions:

public static decimal CAPGETVALUE(string scenario, string netPrincipalCategory, string dataPiece, string dateContext)

and

public static decimal GLGETVALUE(string scenario, string account, string dateContext)

I can pass them strings in my Excel formula and I still get the error.
I have tried changing arguments to objects, Unregistering and Resgistering add-in, restarting PC.

Anybody who could help I would sure appreciate it.

Thanks.
Posted 17 Nov, 2016 21:40:13 Top
Andrei Smolin


Add-in Express team


Posts: 18829
 Joined: 2006-05-11
Hello Larry,

Thank you for the kind words!

Comment out all code in these functions and let them return a simple value. Do you get the issue in this case?


Andrei Smolin
Add-in Express Team Leader
Posted 18 Nov, 2016 06:35:26 Top
Larry Sater


Guest


To simplify things I created a new function TEST, see below.

public static decimal TEST(object testArg)
{
return 0;
}

In Excel, =TEST(7)

I put a breakpoint on "return 0;" run it from VisualStudio it does not hit breakpoint and same error from Excel's side (Value ... wrong datatype). Outside of Visual Studio, just Excel - same error. I'm pretty sure I have the parameter value setup properly.

I also Unregistered and Registered again.
Posted 18 Nov, 2016 08:51:51 Top
Andrei Smolin


Add-in Express team


Posts: 18829
 Joined: 2006-05-11
Larry,

Try to replace "decimal" with "int".


Andrei Smolin
Add-in Express Team Leader
Posted 18 Nov, 2016 08:53:48 Top
Larry Sater


Guest


Sorry, same error.
I could easily recreate my solution, but I'd kind of like to know what is going on in case this happens again.
Posted 18 Nov, 2016 09:08:57 Top
Larry Sater


Guest


Okay, I think I found something - but I don't understand.
I had a static classes (AddInCommon) defined inside XLLContainer XLLModule.cs. I had the code for AddInCommon in the same project as my Add-in project. In my cleanup last night I moved (AddInCommon) to a separate project and added a reference to them in my Add-In. When I take away those references TEST now works.


public static AddInCommon _AddInCommon = new AddInCommon();

So I'm probably not doing something correctly here. I want to reference a class in my add-in and keep it around without having to re-instantiate every time the function is called.

Hope this makes sense.
Posted 18 Nov, 2016 09:31:16 Top
Andrei Smolin


Add-in Express team


Posts: 18829
 Joined: 2006-05-11
Larry,

Could you please send me the project for testing? You can find the support email address in {Add-in Express installation folder}\readme.txt. Please make sure your email contains a link to this topic.


Andrei Smolin
Add-in Express Team Leader
Posted 18 Nov, 2016 09:37:07 Top
Andrei Smolin


Add-in Express team


Posts: 18829
 Joined: 2006-05-11
Larry,

Thank you for sending me the file. You need to start with deleting the initializers of the properties declared in the XLL module.

The result should look like this: 

            public static A _A;
            public static G _G;
            public static C _C;


You need to initialize (set to null) them in the OnInitialize event of the add-in module. After this you'll find out that you need to give your Common assembly a strong name.

I don't know what causes the issue but I know that an initializer causes loading the assembly providing the type. Obviously this breaks something in your case.

If, at any time later, I find a better explanation, I'll post it here.


Andrei Smolin
Add-in Express Team Leader
Posted 18 Nov, 2016 10:36:44 Top
Larry Sater


Guest


Thank you very much! That got my code hooked back in. One more question if I may. When do I need to register/unregister the Add-In? Is it a one time thing for each add-in I create or do I need to do it when I create a new function? Or is it just registering Add-In Express binaries?

I really appreciate how quickly you answered my questions and got me back going. I need to demo this later this afternoon and I was kind of in a panic!
Posted 18 Nov, 2016 11:59:28 Top
Andrei Smolin


Add-in Express team


Posts: 18829
 Joined: 2006-05-11
Hello Larry,

Registering means creating proper registry keys. To create the keys, Add-in Express creates an instance of the module to retrieve required information.

For a COM add-in, you need to re-register the add-in when - at design time - you create/remove COM-related things such as Custom Task Panes, Outlook Property pages, and additional modules. As to XLL, registering it once is usually enough; you will need to re-register it if you add/remove additional modules.


Andrei Smolin
Add-in Express Team Leader
Posted 21 Nov, 2016 06:00:36 Top