Formatting An Existing Open Spreadsheet With A COM Add-In

[Matt]

Please help! I'm new to programming Office add-ins, but have been tasked
with creating an Excel add-in that allows the user to apply formats to a
selected range of cells in an open Excel spreadsheet. I've created an VBA
add-in that does the trick, but it has grown too complex to keep as an XLA
and now I'm trying to figure out how to create a standalone add-in that has
the same functionality.

The Question: Is it possible to create a COM (or other compiled) add-in
that applies formats to a selected range in Excel when a certain menu button
is clicked or other event has occurred? If yes, how? Specifically, how do
you tell the add-in which cells to format?

Any help would be appreciated. Apologies for the basic question.

Regards,
Matt

 

[RB Smissaert]

I am sure it is possible, but as your add-in will only have to work with
Excel
why do you want to change it to a COM add-in? The complexity won't be any
less
any maybe more.

RBS

 

[Matt]

Thanks for the response! There are a couple of reasons we would like to use
a COM add-in:

1) The add-in is customizable and takes too long to save when the user
changes the settings (since the user must save the entire XLA). In the new
COM add-in, there will be a database connected to the add-in that stores the
user settings that updates in real-time.

2) The installation of an XLA appears to require that users activate the XLA
from within Excel. I'd like to remove that step and have the add-in
installer take care of all of the steps necessary to use the add-in from
inside Excel.

3) We would like to eventually add a level of cross-application
functionality (e.g. linking a spreadsheet to a chart in powerpoint) to this
add-in, however I'm less concerned with this step at this point.

4) Speed of execution. Some of the VBA tasks that this add-in performs
(e.g. format a blank spreadsheet to resemble a standard template) take too
long.

5) Code security. We're less concerned about this since the application is
not sold, but generally, it would be good to protect the code from any
would-be hackers in the company.

Hope this answers your question. I'm less worried about the complexity. In
fact, I'm a little excited to learn how to rebuild this add-in as a COM
add-in. I'm hoping to find a source of a C# or VB.NET code example that
accomplishes the task of linking to an open spreadsheet and changing the
format of a cell.

Regards,
Matt

 

[RB Smissaert]

1) Don't know your particulars, but I think it is a bad idea to let the
users save the .xla.
I would infact disable users saving the .xla by doing something like this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

Dim strComputerName As String

'this is a simple API
strComputerName = ReturnComputerName()

If Not strComputerName = "blabla" Then
MsgBox "This .xla file shouldn't be saved, other than by developers." &
_
vbCrLf & vbCrLf & _
"Saving will be cancelled.", vbExclamation, "saving add-in"
Cancel = True
End If

End Sub


If you have settings that need saving then I would do that in an .ini file.


2) It is quite simple to install the .xla with an INNO installer.

3) unless the add-in has to run from PP there should be no problem.

4) I doubt you are going to speed it up by moving to a COM add-in, but I
have no experience with COM add-ins and not sure about this.
Maybe your .xla code can be made more efficient.

5) That is the only one that definitely makes sense, although moving to a
VB6 Active dll will be simpler, plus will give some speed gain as well.


RBS

 

[Matt]

Good points. I agree that a COM add-in may not be the best solution. I'll
have to look into a VB6 Active DLL. I'd really like to use a local SQL
compact 3.5 database for the user settings, as there are thousands of options
and an INI would be unwieldy. I'm much more familiar with VB.NET than I am
with previous versions of VB from my web development days, so I'm not sure
that I want to use VB6. Is there a VB.NET solution?

Another reason to use a managed add-in is so I can build windows forms
instead of the forms that you create in the Alt+F11 VB scripting environment
provided with Excel.

Thanks,
Matt

 

[RB Smissaert]

OK, if you are into .NET then that changes matters a bit.
Don't think thousands of settings is a problem for an .ini file, but again I
have never tried that and could be wrong there. Not sure what compact 3.5
is, but
maybe have a look into SQLite if you really need a database.
There are very good, free wrappers both for .NET and VB.

RBS