Tag Archives: macro

Excel

Super Short Tips: Working with pivot tables in VBA

Sometimes you just need to write macros for pivot tables. It’s a fact of life.

Maybe your pivot table is dynamically populated with data and you need to make some custom changes once the data is in the table, but you can only do that after the file is opened in Excel and the pivot table refreshes. Perhaps your plagued with the error that happens when you open a file with a pivot table directly from Internet Explorer. Whatever your reason, you need to write some VBA for pivot tables.

Here’s my super short list of tips for working with pivot tables in VBA:

#1 – Record a macro of yourself to get started

If you know what you want to do, but you’re not sure what the VBA code should be, record a macro of yourself doing the desired action. Not only will this give you a hint about where to start in your VBA, but you’ll also verify the exact method calls for your version of Excel and your pivot table version. All of my pivot table macros start with me performing operations in Excel and then generalizing the VBA from there.

#2 – Base your VBA on pivot fields

Pivot fields come from the column names in the data source for your pivot table. Even as the data is changing, the pivot fields remain constant because the construction and layout of the pivot table depends on the pivot fields. The fact that they remain constant can be very useful when writing VBA.

If you create macros that are based on particular pivot table items, you can’t be sure that those values will be there when the workbook is populated. For example, you want to make sure that all the groups for a particular row label are collapsed when the user first encounters the pivot table. You can manually collapse each of these groups. Problem: you create a macro that collapses groups “A”, “B”, “C”, etc separately. What if group “A” isn’t in your data set when the report is generated? Excel will throw an errror.

You can take the chance that group “A” may or may not appear in your data set, or you can create a macro that focuses on the pivot field that group “A” belongs to, rather than the individual entries.

ActiveSheet.PivotTables("PivotTable1").PivotFields("PivotField1").ShowDetail = False

This will collapse all the groups within ‘PivotField1’.

In short, avoid making macros that are dependent on specific data, because you can’t be sure that the VBA will execute without error.

#3 – VBA for pivot tables in Excel 2003 is different than in Excel 2007/2010

Excel 2003 had our best friend, the Pivot Table Wizard. Everything for the pivot table was done through a wizard menu, so the VBA in Excel 2003 follows suite. Starting in Excel 2007, Excel reworked how users interacted with pivot tables, and the Pivot Table Wizard was banished for eternity. Thus, VBA in Excel 2007/2010 is drastically different.

If you have end-users who are going to be opening a report with pivot table VBA in Excel 2003, 2007, and 2010, you’ll want to detect what version of Excel is being opened, then execute the 2003 or 2007/2010 based on this.

To determine what version of Excel the VBA is executing in, check Application.Version. This will return a string representing the version of Excel (e.g. 10.0, 12.0, 14.0 etc.)

#4 – Pivot tables have versions too

Just as there are differences between XLS and XLSX files in terms of what features can be supported, there are differences between pivot tables created in XLS files vs those created in XLSX files. What’s more, there are also differences between pivot tables created in Excel 2007 and 2010. It’s important to note what version of pivot table you’re targeting.

Luckily you can check the PivotTable.Version property to see which of the versions your pivot table is.

So there you have it – my cheat sheet for creating VBA for pivot tables.

Excel

Super Short Tips: Creating Workbooks with Excel VBA

Let’s say you want to write an Excel macro that will create a new workbook and save it to a specific location and then close the new file. This is really easy with Excel VBA:

Sub AddNewWorkbook()
     Set NewWb = Workbooks.Add
     With NewWb
          .SaveAs Filename:="C:\Path to directory\NewFile.xls"
          .Close
     End With
End Sub

You run this once and it works perfectly. You run it again and Excel tells you that “A file named ‘C:\Path to directory\NewFile.xls’ already exists in this location. Do you want to replace it?”

Well, of course you do. Why is Excel bothering you with these trivial questions?

So you want to supress the Excel alert. To do this, just set Application.DisplayAlerts to False before you save the file, and then turn it back on after you’re done:

Sub AddNewWorkbook()
     Set NewWb = Workbooks.Add
     Application.DisplayAlerts = False
     With NewWb
          .SaveAs Filename:="C:\Path to directory\NewFile.xls"
          .Close
      End With
     Application.DisplayAlerts = True
End Sub

Easy-peasy.

This technique is also handy for supressing the oh-so-lovely Do you want to save the changes you made to workbook X? alert. Microsoft has some additional tips for hiding the saving changes alert in this KB article: How to suppress “Save Changes” prompt when you close a workbook in Excel.

Excel

Macro Security Settings in Excel 2007 and 2010

What is the problem?

A customer was exporting Excel reports by OfficeWriter to the XLSM format and was alarmed when he opened his output and saw a security warning:

Some active content may have been disabled:

 

 

Obviously, anyone who hadn’t experienced the Excel Trust Center Security Warning before could easily have been alarmed by this.

What is going on?

The security prompts are caused by the settings in Excel’s Trust Center, which was introduced in Excel 2007. Basically, when a workbook is opened in Excel 2007 or 2010, it is automatically run through the Trust Center feature for security reasons. Excel’s behavior for handling workbooks with macros will change depending on the settings selected in the Trust Center.

Some notes about Trust Center security warning messages
  • Some active content may have been disabled indicates that there are multiple types of active content that have been disabled, not just macros. If the workbook contains just macros, the security warning will read Macros have been disabled.
  • In Excel 2010, the security messages are the same, but the coloring makes the warning more obvious. For more information about the difference in the security warning between Excel 2007 and Excel 2010, check out this article.

To get to the Trust Center: Office button > Excel Options > Trust Center > Trust Center Settings. And on my left, Macro settings:

 

For example, say the ‘Disable all macros with notification’ option is selected. This is the default macro security level for Excel 2007/2010 and this setting will prompt the user to enable macros anytime the workbook is opened. The end-user can change the security setting to ‘Enable all macros’, and then macros will run without the prompting.

For more general information about Macro security settings, please refer to this Microsoft article: http://office.microsoft.com/en-us/excel-help/change-macro-security-settings-in-excel-HP010096919.aspx

I am developing a workbook with macros. How can I avoid having the Trust Center prompt my end-users?

A note about macro security settings
Enabling macros to run without prompts could be potentially dangerous if a workbook contains malicious macros. The security prompt is designed to prevent macros from running unless the user decides they are safe to run. Each of the following suggestions will allow macros to run without prompting, which may harm your computer if the macros are malicious

Since the Trust Center’s settings are determined on a per-user basis, depending on which security settings the user has selected, the behavior the end-user experiences may vary. Here are some ideas for avoiding the Trust Center security prompt:

Digitally sign the macros (recommended)

One of the macro settings is to ‘Disable all macros except digitally signed macros’. This means that if the macros are signed by a Trusted Publisher, Excel will run the macros without any security prompts. All unsigned macros will still prompt the user to enable them, but they will not be prompted for workbooks that contained macros signed by the Trusted Publisher.

This Microsoft article explains how to digitally sign a macro project: http://office.microsoft.com/en-us/excel-help/digitally-sign-a-macro-project-HA001231781.aspx

After you have signed the project, the end-user will need to add you as a Trusted Publisher. This is explained below.

The first time the user opens a workbook with signed macros, they will be given the following options:

  1. Help protect me from unknown content (which is to leave the macro disabled)
  2. Enable this content (enable for one-time use)
  3. Trust all documents from this publisher (enable all macros from this publisher).

Instruct the end-user to select Trust all documents from this publisher, which will add the publisher who signed the macros, you, as a Trusted Publisher. All subsequent workbooks with macros signed by you will not display security prompts when opened on the end-user’s machine.

For more information about Trusted Publishers, please refer to this Microsoft article: http://office.microsoft.com/en-us/excel-help/add-remove-or-view-a-trusted-publisher-HA010034138.aspx?CTT=5&origin=HA010167227

Use a trusted location (not recommended in some cases)

If a workbook is opened from a trusted location, it does not go through the Trust Center. This means that regardless of the Trust Center settings, Excel will run the macros in the workbook without any security prompts. Trusted locations should always be secure and trustworthy.

If your end users are opening the workbooks directly from the browser cache (e.g. the workbook is streamed to the client from a web application), DO NOT tell them add their browser cache as a trusted location. The browser cache is an easy target for hackers who can force the browser to download potentially comprimised workbooks. This is not to say that trusted locations are never recommended. For instance, your end-users could specifically designate another location as trusted, and copy any files to that location before opening them, which is the secure way to use trusted locations to avoid the security prompts.

Here is a Microsoft article that discusses trusted locations: http://office.microsoft.com/en-us/excel-help/create-remove-or-change-a-trusted-location-for-your-files-HA010031999.aspx?CTT=5&origin=HA010167227

Enable all macros (not recommended)

This option runs all macros without prompting. This is NEVER recommended because it potentially exposes the end-user to malicious macros, even if your macros are safe.

Macro Settings in Excel 2003

If you are using Excel 2003, the macro settings will be different. Excel Hints has a good overview of those settings here: http://excelhints.com/2010/08/06/adjust-the-security-level-in-excel/.

Excel, Programming

Things I’d like in an IDE

I wish Resharper had a feature which pointed out where exceptions are being created but aren’t being thrown (ie, new Exception() instead of throw new Exception()). I also wanted a feature which automatically sets breakpoints where exceptions are thrown, although after thinking about that for a bit I’m not sure about that. I do really like the syntax checking for the most part, even if it messes up sometimes. And I still do go back to Emacs for anything repetitive; I haven’t seen anything in Resharper or VS that allows for Emacs-like macros.