Installing a .BAS function to run in every workbook

[David Thielen]

Hi;

We have a .BAS function that we want to install with our Excel AddIn
and have it set to run on every workbook on that user's computer. What
this macro does is for the formula:
=autotag("<wrut select='select FirstName from Employees'
nickname='First Name'/>")

it displays:
FirstName

So we want this running in all workbooks because it only comes in to
play when using our system and this way people don't have to go load
it.

1) How do we install the file to get the behavior?

2) If this cannot be done, what is the suggested way to provide this
as an option to apply to a workbook. Keep in mind our customers are
not developers.

3) Is there any security setting we need to set to make this run, ie
so the user does not have to click to enable the macro?

thanks - dave

david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm

 

[Jie Wang [MSFT]]

Hi David,

Let me answer the "How to" part of the questions.

To programmatically add VBA items to your workbook, you can call the VBE OM
exposed by Excel.Application.

The following C# sample code shows how to do that, you need to add a
reference to the Microsoft.Vbe.Interop assembly (from Office PIA) to
compile & run.

private void InstallMacro()
{
try
{
// Get the active VBA project via the Excel.Application object.
Microsoft.Vbe.Interop.VBProject prj =
Application.VBE.ActiveVBProject;
Microsoft.Vbe.Interop.VBComponent mod;

// Add a new module
mod =
prj.VBComponents.Add(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdM
odule);
mod.Name = "MyModule";

// Add code from string. To add from a file, call AddFromFile.
mod.CodeModule.AddFromString(
"Sub HelloWorld()\r\n" +
" MsgBox \"Hello World!\"\r\n" +
"End Sub\r\n");
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Setup VBA Error",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}

And in your add-in code, you can call the InstallMacro followed by
Application.Run to execute the macro:

InstallMacro();

// It's nightmare to call the Run method because C# doesn't support
// optional parameters.
Application.Run("HelloWorld",
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);


There are security settings you need to change before get it working.

The first one is "Trust access to the VBA project". This must be enabled or
you'll get an COMException (HRESULT = 0x800A17B4) when accessing the
ActiveVBProject property. To turn on access, please follow these steps.

Office 2000 doesn't have this check.

Office 2003 and Office XP
1. Open the Office 2003 or Office XP application in question. On the Tools
menu, click Macro, and then click Security to open the Macro Security
dialog box.
2. On the Trusted Sources tab, click to select the Trust access to Visual
Basic Project check box to turn on access.
3. Click OK to apply the setting. You may need to restart the application
for the code to run properly if you automate from a Component Object Model
(COM) add-in or template.

Office 2007
1. Open the 2007 Microsoft Office system application in question. Click the
Microsoft Office button, and then click Application Options.
2. Click the Trust Center tab, and then click Trust Center Settings.
3. Click the Macro Settings tab, click to select the Trust access to the
VBA project object model check box, and then click OK.
4. Click OK.

Or you can programmatically change the registry settings at:
Office 2003: HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel\Security
Office 2007: HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Security

Add or modify the DWORD value named AccessVBOM. Set to 1 means allow
access, 0 means disallow access.

The Macro Security setting is located in:
Office 2003: HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel\Security
Office 2007: HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Security

There is a DWORD value named VBAWarnings. Meaning of the values:

VBAWarnings=1 Enable all macros (not recommended; potentially dangerous
code can run)
VBAWarnings=2 Disable all VBA macros with notification (application
default)
VBAWarnings=3 Disable Trust Bar warning for unsigned VBA macros
(unsigned code will be disabled)
VBAWarnings=4 Disable all macros without notification

For corporate users, this setting can also be controlled via policy, for
more information:
http://technet.microsoft.com/en-us/library/cc178946.aspx

Changing these security settings to a low standard may bring potential
vulnerability into the system. So please take careful consideration over
the changes.

If you have any further questions regarding this issue, please kindly let
me know.

Best regards,

Jie Wang ([email protected], remove 'online.')

Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 2 business days is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions. Issues of this
nature are best handled working with a dedicated Microsoft Support Engineer
by contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[David Thielen]

Hi;

Thank you, I will try this later this week when I have time. Two
follow on questions:

1) Using the below approach can I make the VBA project signed for
cases where a user will only run signed apps?

2) I do not want to change their security settings - I agree that
would be bad. But I want to set my VBA app as approved. Is there a way
to do that? Or will they just be prompted the first time I try to run
it?

thanks - dave

Hi David,

Let me answer the "How to" part of the questions.

To programmatically add VBA items to your workbook, you can call the VBE OM
exposed by Excel.Application.

The following C# sample code shows how to do that, you need to add a
reference to the Microsoft.Vbe.Interop assembly (from Office PIA) to
compile & run.

private void InstallMacro()
{
try
{
// Get the active VBA project via the Excel.Application object.
Microsoft.Vbe.Interop.VBProject prj =
Application.VBE.ActiveVBProject;
Microsoft.Vbe.Interop.VBComponent mod;

// Add a new module
mod =
prj.VBComponents.Add(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdM
odule);
mod.Name = "MyModule";

// Add code from string. To add from a file, call AddFromFile.
mod.CodeModule.AddFromString(
"Sub HelloWorld()\r\n" +
" MsgBox \"Hello World!\"\r\n" +
"End Sub\r\n");
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Setup VBA Error",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}

And in your add-in code, you can call the InstallMacro followed by
Application.Run to execute the macro:

InstallMacro();

// It's nightmare to call the Run method because C# doesn't support
// optional parameters.
Application.Run("HelloWorld",
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);


There are security settings you need to change before get it working.

The first one is "Trust access to the VBA project". This must be enabled or
you'll get an COMException (HRESULT = 0x800A17B4) when accessing the
ActiveVBProject property. To turn on access, please follow these steps.

Office 2000 doesn't have this check.

Office 2003 and Office XP
1. Open the Office 2003 or Office XP application in question. On the Tools
menu, click Macro, and then click Security to open the Macro Security
dialog box.
2. On the Trusted Sources tab, click to select the Trust access to Visual
Basic Project check box to turn on access.
3. Click OK to apply the setting. You may need to restart the application
for the code to run properly if you automate from a Component Object Model
(COM) add-in or template.

Office 2007
1. Open the 2007 Microsoft Office system application in question. Click the
Microsoft Office button, and then click Application Options.
2. Click the Trust Center tab, and then click Trust Center Settings.
3. Click the Macro Settings tab, click to select the Trust access to the
VBA project object model check box, and then click OK.
4. Click OK.

Or you can programmatically change the registry settings at:
Office 2003: HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel\Security
Office 2007: HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Security

Add or modify the DWORD value named AccessVBOM. Set to 1 means allow
access, 0 means disallow access.

The Macro Security setting is located in:
Office 2003: HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel\Security
Office 2007: HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Security

There is a DWORD value named VBAWarnings. Meaning of the values:

VBAWarnings=1 Enable all macros (not recommended; potentially dangerous
code can run)
VBAWarnings=2 Disable all VBA macros with notification (application
default)
VBAWarnings=3 Disable Trust Bar warning for unsigned VBA macros
(unsigned code will be disabled)
VBAWarnings=4 Disable all macros without notification

For corporate users, this setting can also be controlled via policy, for
more information:
http://technet.microsoft.com/en-us/library/cc178946.aspx

Changing these security settings to a low standard may bring potential
vulnerability into the system. So please take careful consideration over
the changes.

If you have any further questions regarding this issue, please kindly let
me know.

Best regards,

Jie Wang ([email protected], remove 'online.')

Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 2 business days is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions. Issues of this
nature are best handled working with a dedicated Microsoft Support Engineer
by contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm

 

[Jie Wang [MSFT]]

Hi David,

To answer your questions:

1) Using the below approach can I make the VBA project signed for
cases where a user will only run signed apps?

As far as I know, there is no way to sign a VBA project via the OM. You
must sign it manually.

Even if there is a way to sign it programmatically, you still can't do that
because the signing will happen on user's computer, and I'm sure you don't
want to distribute your own private key which is required to sign the
project to your users.

2) I do not want to change their security settings - I agree that
would be bad. But I want to set my VBA app as approved. Is there a way
to do that? Or will they just be prompted the first time I try to run
it?

In Office 2007 you can also config "Trusted Locations". The documents
opened from the Trusted Locations will be trusted and the Macros will be
allowed to run.

To config the Trusted Locations manually:

1. Press the Office Button on Excel main Window. Then choose Excel Options
from the menu.
2. In the Excel Options dialog, choose Trust Center. Then click the Trust
Center Settings button.
3. In the Trust Center dialog, choose Trusted Locations. Then you can
add/modify/remove Trusted Locations.

To config the Tursted Locations via admin policy:
http://technet.microsoft.com/en-us/library/cc178948.aspx

To config the Trusted Locations directly in Registry:
HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Security\Trusted
Locations

Besides your follow up questions, I have some additions to my yesterday's
reply:

1. Some AntiVirus software might be extremely sensitive and unhappy when
you try to insert VBA code into a document via the OM - looks like the way
how Office Macro Viruses duplicate and spread themselves - so the code
might actually be blocked and even killed by AntiVirus software in some
cases. I don't have any data to prove how often this happens but I think at
least we should take this scenario into consideration.

2. I was using the code "Application.VBE.ActiveVBProject" to get the VBA
project object. This is buggy. The active project doesn't necessarily to be
current workbook's VBA project. So the correct way to access a workbook's
VBA project is by accessing Workbook's "VBProject" property.

Hope this helps.

Regards,

Jie Wang ([email protected], remove 'online.')

Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 2 business days is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions. Issues of this
nature are best handled working with a dedicated Microsoft Support Engineer
by contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[David Thielen]

Would http://www.jkp-ads.com/Articles/DistributeMacro00.asp be a
better way to do this?

thanks - dave

david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm

 

[Jie Wang [MSFT]]

Hi David,

I agree that using the Excel Add-in (XLAM file for 2007 and XLA for
97-2003) is a better approach - you can write your own functions in it and
then lock & sign it - without making changes to the COM add-in code. Thank
you for reminding me!

And then we can deploy the Excel Add-in file to Excel startup folder or
user startup folder (depends on the requirement), so the functions defined
in the Add-in VBA project will be available to all workbooks at runtime.

I also found the developer reference for the Excel Add-ins, which contains
information on creating & loading add-ins and running code automatically
when an Excel Add-in is loaded or unloaded:
http://msdn.microsoft.com/en-us/library/aa140990(office.10).aspx

Hope this will help you come out a better solution.

Best regards,

Jie Wang ([email protected], remove 'online.')

Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 2 business days is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions. Issues of this
nature are best handled working with a dedicated Microsoft Support Engineer
by contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.