Excel VBA -> COM

[DaveThompson]

Hello all,

First of all, thanks in advance for reading this post - hopefully
it'll make sense...


We've got a bit of a problem here that we hope you can help us with.
We've just developed a COM automation add-in in Visual Studio 2005 to
replace an XLA add in. To ease the conversion, we named most of the
functions exported by the COM library to the same name as those in the
XLA file.

However, our problem is that on removing the XLA file and adding in
the COM automation add-in to a workbook, the excel spreadsheets cannot
reference the new method calls which have the same name as the old
calls in the XLA.

If we create a new worksheet and add in the automation item, then the
calls work fine.

For example, the UDF call CBSpot existed in the XLA. When we remove
the XLA and add in the automation DLL CBSpot can't be resolved.
However, if we fully qualify it (PricingLib.Connect.CBSpot) then it
will work, but Excel will then automatically remove the ProgID (change
it back to CBSpot), and the call will then fail.

What we think is that somehow, the old XLA is still known of by the
workbook and that is what is referenced.

Any help would be most appreciated, since we're at the end of our
tether here!


Cheers,

Dave Thompson
Commerzbank AG, London Branch

 

[Tom Ogilvy]

Try going to each sheet and doing
Edit=>Replace
what: =
with: ZZ=

this will make all your formulas text strings. Once you have no formulas
in the workbook, save and close it.

Now open it and reverse the procedure and see if that helps.

 

[Chip Pearson]

Dave,

If you have a Public creatable class that contains the functions provided by
the VS2005 project, you should be able to call those functions in the VS2005
library directly from a worksheet cell if you load that class's ProgID as an
Automation Add-In (from the regular XLA add-ins dialog,not the COM add-ins
dialog). I believe that ProgID should be registered in HKCU for each user
rather than HKLM for all users.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[DaveThompson]

Tom,

Thanks for your input. We've just tried that, and unfortunately it
doesn't work; it just goes back to the old problem once we've found/
replaced everything.

Good idea though - any more?

Cheers,

Dave

 

[DaveThompson]

Chip,

Thanks for your input. We've already got Excel "seeing" the exposed
functions in the public COM-visible class, and we can use them if we
create a new Excel session and start typing our UDFs in cells. The
problem occurs when we load an Excel workbook that references a VBA
add-in that contains functions with the same names. Even if we remove
the XLA VBA add-in and load in our COM add-in, the Excel sheets can't
"see" the new functions from the new COM add-in. If we fully qualify
it with the ProgId (i.e. PricingLibrary.Connect.CBSpot) then it'll
work, but Excel will immediately take off the "PricingLibrary.Connect"
qualifier and it'll go back to failing with "#NAME".

To be honest with you, I'm thinking this may be a bug in Excel!

Thanks everyone for your assistance so far!

Dave

 

[RB Smissaert]

Have you tried removing the .xla from the registry?

RBS

 

[DaveThompson]

Hi RBS,

I didn't even realise that XLA files registered themselves, but a
quick sweep of the registry through regedit doesn't yield any
references to the XLA.

Cheers,

Dave

 

[Dave Peterson]

I've never used COM addins, so this may not work--but this technique has worked
when someone creates a UDF in a specific workbook, then decides to move that UDF
to an addin.

In this situation, the formula continues to want to find the UDF in the original
workbook.

Saved from a previous post:

After I moved the code from one workbook's project to the other and saved both
files (one as an addin), I did this in the "regular" workbook (.xls) (with the
..xla still open).

Insert|Name|define
myFunc
(refer to any cell)
Add

Then I deleted that name.
insert|name|define
myfunc
delete

Then I did edit|replace
what: = (equal sign)
with: = (equal sign)
replace all

Excel was smart enough to reevaluate this function from the xla.

==========
Maybe you'll find that creating|deleting a name will work for you.

 

[DaveThompson]

Hi Dave,

Thanks for the reply, but unfortunately this doesn't work either. I
think we're going to have to go with copying the sheets manually to
another workbook and then copying any VBA code by hand. Not an ideal
solution by any stretch of the imagination, but one of the only
solutions I can see to Excel's "quirks".

Thanks,

Dave

 

[DaveThompson]

Hello all,

Thanks for your help, but the problem has now been solved by simply
changing the names of the new functions (by adding an extra character)
and then renaming all references programatically.

The problem with Excel "seeing" the old XLA hasn't been solved; the KB
article at http://support.microsoft.com/kb/286305/en-us ("PRB:
Automation Add-In Function Binds to Excel Built-In Function with the
Same Name") contains some pointers to the problem but annoyingly the
solution is simply to rename the functions.

Cheers all,

Dave