Local (workbook-specific) functions without using VBA or VSTO

2
votes

I am looking for a way to create workbook-local functions in C++ (unmanaged). VBA is not appropriate for my use case. Unfortunately, that's the only way I know of to create a workbook-local function. (I'm not terribly familiar with VSTO, so there may be a way there, but I cannot used managed code for this.) I know how to create functions as XLLs, and I know how to create an automation addin that exposes functions through IDispatch. However, both of these seem to be for global functions only.

Does anyone know a way to do this?

1
vbaexcelvsto
It might help if you explained why VBA or managed code isn't appropriate. - RubberDuck
VBA can only be constructed programmatically if the "Trust access to the Visual Basic project object model" setting is True. Our client base is enormous (hundreds of thousands of users globally). We cannot ask our users to enable this due to security policies at client sites. - Michael Gunter
And we internally have a prohibition against loading the .NET framework inside Excel. - Michael Gunter
Also, these details aren't important to answering the question. :) - Michael Gunter
You mean like this? Michael, I'm afraid that VSTO is the way to go here, whatever your internal policies about loading .Net are, it's the sensible solution and the right technology for the job. Good luck mate. - RubberDuck

1 Answers

2
votes

Worksheet != Workbook

I don't know of any way to create worksheet-local functions.

In VBA to expose functions you need a standard module (.bas) without the Option Private Module option set, exposing a Public Function - that function is usable in every worksheet of the workbook that's containing it.

A worksheet is essentially a class module, and lives as an object instance: its Public Function members are therefore not usable as worksheet functions.

Functions are workbook-local, not worksheet-local.

Now, that's probably what you meant anyway, given that the add-in approach is being referred to as creating "global" functions, which are available to all workbooks.

As @RubberDuck hinted, creating code via the VBIDE library doesn't require elevation and security settings tweaking if you're extending the VBE itself. This might help (it's the entry point for the add-in @RubberDuck and I are working on). This add-in edits, deletes and adds code in the VBE for a living, and doesn't require tweaking macro security settings.