Browse Category


Maximizing Excel / VBA Automation Performance


When automating Excel operations with VBA, there are many ‘tricks’ which can be implemented to significantly improve performance. As the automations run in a non-interactive fashion, features that primarily exist for the benefit of an end user can be temporarily disabled resulting in performance gains. The following Excel settings fall into this category:

Excel Application Properties

  • Calculation
  • EnableEvents
  • ScreenUpdating
  • EnableAnimations
  • DisplayStatusBar


One of the best features of a spreadsheet is its magical ability to instantly update data contained in the worksheet cells. While these real-time updates are useful when users are interactively working in the Excel document, this feature robs performance and isn’t necessary when executing automation processes. By changing the Application.Calculation property, we can toggle the calculation logic between Automatic, Manual, and SemiAutomatic.

Toggling between Manual and Automatic during your VBA code execution will typically result in significant performance gains.

Figure 1 – Disable Automatic Calculations in Excel

Figure 2 – Enable Automatic Calculations

To illustrate the potential performance difference, I created a simple VBA routine to generate a 22×22 grid of numbers. Formulas are contained on the bottom of the sheet which sum up the numbers in the grid. The numbers on the grid are updated multiple times based on a user supplied value. When this grid is generated with / without automatic calculations enabled / disabled, there is a significant performance impact. *

Figure 3 – Calculation Manual vs Automatic

As the size of the grid/number of calculations required increases, the performance will continue to decline. Temporarily disabling the calculation method will significantly boost performance on sheets that make use of formulas. (e.g. just about every spreadsheet on the planet)

*IMPORTANT NOTE – Performance metrics are going to vary on a machine by machine basis depending on the system’s hardware. As a general rule, these settings will almost always improve performance. The amount of improvement will be inversely proportional to the machine’s hardware. (e.g. the slower the machine, the larger the improvement) Even if the improvement isn’t the greatest on your machine, you should still consider implementing these steps if other users will work with the excel files.


In Excel, events can trigger actions and/or programming logic to be executed. For instance, when you change a value in a cell, a “changed” event triggers. Events are one of the ways in which Excel “automagically” knows to update formula based cells when the underlying data is altered. Disabling events is just as easy as adjusting the calculation mode:

Figure 4 – Disable / Enable Excel Events

Using the same 22×22 grid of numbers from the previous example, testing was performed contrasting the difference between enabling / disabling events. Disabling events resulted in a 50% improvement in processing time on a modern quad core i7 machine!


The ScreenUpdating setting in Excel is relatively self-explanatory. When this is enabled, the worksheet display area will be refreshed after any cell values are altered. When users are directly interfacing with Excel, this absolutely needs to be enabled; however, when executing scripts, this is not needed. Updating this setting is also straight forward:

Figure 5 – Disable / Enable ScreenUpdating

Using the same 22×22 grid of numbers from the previous example, testing was performed contrasting the difference between enabling / disabling screen updating. Disabling ScreenUpdating resulted in a modest 10% improvement in processing time on a modern quad core i7 machine with a high end graphics card.

EnableAnimations / DisplayStatusBar

The final two items, EnableAnimations and DisplayStatusBar, typically do not result in significant gains; however, they can help if you need to squeeze every possible ounce of performance out of your Excel/VBA.

EnableAnimations is another user interface related performance setting. This setting only disables certain visual elements as opposed to all screen updates. (e.g. user interface animations for certain actions) Typically, this setting will not make a significant impact; however, it is better than nothing in situations where you cannot leverage the ScreenUpdating setting. Like many of the previously discussed settings, this is another On/Off (True/False) setting:

Figure 6 – Disabled / Enable Application Animations

The DisplayStatusBar is yet another user interface setting that may result in minimal gains. Unless your Excel application relies heavily on the status bar, it is not likely this is worth the effort. This is also an On / Off (True / False) setting:

Figure 7 – Disable / Enable Status Bar


Additional Tips / Suggestions

When leveraging these settings, keep the following in mind:

  • Cumulative Effects –The performance settings can be combined for cumulative improvements! In the previously discussed grid test, enabling all performance settings results in an amazing improvement. On a modern quad core i7 computer, the test went from 4 seconds to less than 1 second!
  • Global Scope – Be cognizant that these settings are global settings and will persist across workbooks, instances of Excel, and will even persist after you close and restart Excel! Be sure to consider this when designing your solutions.
  • Restoring original settings – While these settings are great for performance, they are not great for end users! If your code fails to restore the original values, end users will be negatively impacted! (End users and IT teams alike may remove you from their Christmas mailing lists!)

As some of the settings, such as Calculation State, are more than an On/Off setting, it is recommended that you read the current setting and store it in a variable to ensure that you can correctly reset the application setting to its original state.

  • Code Robustness – To ensure that the settings are consistently reset, it is imperative that you follow good coding practice and account for errors/exceptions in your code. If you do not implement proper error handling in your code, an end user will eventually run into an unhandled exception resulting in the code ending in an incomplete state. (e.g. your VBA code tries to delete a file and encounters an error, such as ‘permission denied’) The ‘On Error’ statement should be utilized in your code to provide a way for your code to handle errors gracefully.

The attached Excel workbook contains sample code as well as the Sample Grid test which has been referenced in this write-up:

Figure 8 – Excel Performance Demo Application

Oracle EPM / Windows Prerequisite Tuning [Part 1]

If you are installing Oracle EPM in a Windows environment, there are a plethora of settings you need to tweak per Oracle guidance.  To greatly simplify this process, I made a script that performs all of the steps for you, saving you a pile of time / effort.  There are a few steps that you’ll need to do manually; however, this will allow you to perform these steps in a fraction of the time *and* ensure that your servers are configured in a consistent manner.

NOTE:  Part 2 of this post will actually go into what these settings do.  For now, just enjoy a free script.

@echo off

@REM ----------------------------------------------------------------------------------------------------
@REM - Name: Infra_Win_Prereq.bat
@REM - Author : Charles Beyer
@REM - Creation Date : 11/2/2015
@REM - Last Modified DAte : 7/17/2017
@REM - DESC: The purpose of this script is to configure Oracle required default settings for Oracle
@REM -       Hyperion Enterprise Performance Management products running on Windows 2012, 2008, 2003
@REM -
@REM - Inputs :
@REM            %username% - Windows system variable containing the currently logged on user
@REM            %COMPUTERNAME% - Windows system variable containing the current machine name
@REM - Outputs : Command line success/fail responses
@REM -
@REM - NOTES: There are a few steps that cannot be performed via command line
@REM -         Those items are in the script and will be called out.
@REM -         User should perform the manually
@REM -         The logged on user while running this script should be the EPM Service Accout as some of the
@REM -         config items are a "per user" setting.
@REM -
@REM - Items Configured in this script are:
@REM -       DHCP Check (Should be using Static IPs)
@REM -       Time Sync w/ Domain Controller
@REM -       UAC Disable
@REM -       Local Security Policy Permission Settings
@REM -           (*NOTE* You need ntrights.exe for these steps. Available on Windows Resource CD of online download)
@REM -       Disable DEP
@REM -       Windows Firewall Disable
@REM -       Group Policy Updates (Disable force registry unload)
@REM -       Windows Power - Set to Max performance
@REM -       Adjust Advanced Display Settings to 'Best Performance'
@REM -       TCP Tuning Settings (Part 1)
@REM -       Worker Threads
@REM -       Disable Delete TEMP on logoff / Disable 'per user' temp folders
@REM -       TCP Tuning (Part 2)
@REM -       Remove TMP / TEMP User Environment variables
@REM -       Disable Anti-Virus reminder (I can't disable anti-virus software, just display message to add exceptions, etc.)
@REM -       Disable Internet Explorer Enhanced Security

@REM -----------------------------------------------------------------------------------------------------

@REM -------------------------- DHCP Check --------------------------------------
@REM Check for any Dynamic IP addresses as we should be used STATIC
@ECHO Checking for Dynamic IPs.
@ECHO If DHCP is shown as enabled, please investigate further.
@ECHO Static IPs are expected.
ipconfig /all | find "DHCP Enabled."

@REM Add space between command output

@REM -------------------------- Time Sync w/ Domain ---------------------------------
@REM Sync time to Domain Server
@ECHO Configuring Date/Time to be synchronized with Domain Controller
w32tm /config /syncfromflags:domhier /update
net stop w32time
net start w32time

@REM Add space between command output

@REM -------------------------- UAC Disable ---------------------------------
@REM Disable UAC
@ECHO Disabling UAC
reg add HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System /v EnableLUA /t REG_DWORD /d 0 /f

@REM Add space between command output

@REM -------------------- Local Security Policy Permissions --------------------
@ECHO Please review Location Security Policy Permissions and assign the service/install account
@ECHO 'Adding Logon as service' right
ntrights +r SeServiceLogonRight -u %username% -m \\%COMPUTERNAME%

@ECHO Adding 'Logon as batch job' right
ntrights +r SeBatchLogonRight -u %username% -m \\%COMPUTERNAME%

@ECHO Adding 'Act as part of the OS' right
ntrights +r SeTcbPrivilege -u %username% -m \\%COMPUTERNAME%

@ECHO Adding 'Bypass Traverse Checking'
ntrights +r SeChangeNotifyPrivilege -u %username% -m \\%COMPUTERNAME%

@REM Add space between command output

@REM -------------------- Disable DEP --------------------
@REM Disable Data Execution Prevention (DEP)
@ECHO Disabling Data Execution Prevention (DEP)
bcdedit /set nx AlwaysOff

@REM Add space between command output

@REM -------------------- Disable Firewall --------------------
@REM Disable Firewall
@ECHO Disabling Firewall for EPM
netsh advfirewall set currentprofile state off

@REM Add space between command output

@REM -------------------- Upgrade Group Policy --------------------
@REM Update Group policy to Disable force unloaded of user registry upon logoff
@ECHO Updating Group policy to Disable force unload of user registry upon logoff
reg add "HKLM\SOFTWARE\Policies\Microsoft\Windows\System" /v DisableForceUnload /t REG_DWORD /d 1 /f

@REM Add space between command output

@REM -------------------- Set Power Option to High Perf --------------------
@REM Set Power Option to High Performance
@ECHO Setting Power Option to High Performance
powercfg /S SCHEME_MIN

@REM Add space between command output

@REM -------------- Update Adv Settings for Best Performance --------------
@REM Update Advanced Settings / Adjust for Best Performance (UI)
@ECHO Updating Advanced Settings / Adjust for Best Performance (UI)

reg add "HKCU\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\VisualEffects" /v VisualFXSetting /t REG_DWORD /d 2 /f

@REM Add space between command output

@REM ------------- Registry Updates (TCP / Session Mgr / Term Svr) -------------
@REM Add Windows Registry settings for TCP Performance, Additional CPU Thread settings, Disable Delete Temp Directories on Exit
@ECHO Updating Registry settings for TCP, CPU, Temp/User
reg add "HKLM\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters" /v TcpTimedWaitDelay /t REG_DWORD /d 30 /f
reg add "HKLM\SYSTEM\CurrentControlSet\Control\Session Manager\Executive" /v AdditionalCriticalWorkerThreads /t REG_DWORD /d 64 /f
reg add "HKLM\SYSTEM\CurrentControlSet\Control\Session Manager\Executive" /v AdditionalDelayedWorkerThreads /t REG_DWORD /d 20 /f
reg add "HKLM\SYSTEM\CurrentControlSet\Control\Terminal Server" /v DeleteTempDirsOnExit /t REG_DWORD /d 0 /f
reg add "HKLM\SYSTEM\CurrentControlSet\Control\Terminal Server" /v PerSessionTempDir /t REG_DWORD /d 0 /f

@REM Add space between command output

@REM ------------------- Network Performance Settings -------------------
@REM TCP Performance Settings
@ECHO Apply TCP Performance Settings

netsh int tcp set global autotuninglevel=disabled
netsh int tcp set global chimney=disabled
netsh int ipv4 set dynamicport tcp start=1025 num=64000
netsh int ipv4 set dynamicport udp start=1025 num=64000
netsh int ipv6 set dynamicport tcp start=1025 num=64000
netsh int ipv6 set dynamicport udp start=1025 num=64000

@REM Add space between command output

@REM ------------------- Remove TEMP USER Env Variables -------------------
@REM Delete User TEMP variables
@ECHO Deleting Current User defined TEMP/TMP Environment Variables
@ECHO NOTE: You may see ERROR / Unable to find key messages. These indicate the
@ECHO TMP / TEMP environment variables have not been set for the current user.
reg delete HKCU\Environment /F /V TEMP
reg delete HKCU\Environment /F /V TMP

@REM Add space between command output

@REM ----------------- Display Virus Exception Reminder ----------------
@REM Virus software warning
@ECHO IF virus scanning is active on this machine, be sure to do the following:
@ECHO - Disable Scanning on Hyperion servers during software installation
@ECHO - Enable Scanning after install, but exclude the \Oracle folders.

@ECHO All steps complete, please review for any errors.

@REM ----------------- Disable IE Enhanced Security ----------------------
@REM This disables the IE enhanced security

@REM Disable for users (0 = disabled, 1 = enabled)
reg add "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Active Setup\Installed Components\{A509B1A7-37EF-4b3f-8CFC-4F3A74704073}" /v IsInstalled /t REG_DWORD /d 0 /f

@REM - Disable for Admin Users (0 = disabled, 1 = enabled)
reg add "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Active Setup\Installed Components\{A509B1A8-37EF-4b3f-8CFC-4F3A74704073}" /v IsInstalled /t REG_DWORD /d 0 /f

Kscope13 is approaching!

With Kscope13 around the corner, I thought I’d share last year’s presentations:

For Kscope13, I’m presenting a follow-up to my API session which will see us complete a .NET desktop app from scratch to consuming web services via a tablet app.  Should be interesting for those looking to expand their product’s functionality via the APIs.

To see a full break down for Kscope 13, please go here :

See you there!

Enumerating Financial Reports via Workspace database

Disclaimer : This post will talk about direct database access. Do not perform unless you are sure you know what you are doing and I’m not liable! I’m just passing along information I’ve learned in case it helps you out….
Recently, we decided that we wanted to review all of our reports to determine which ones we could drop and which ones we should keep. Over the past 7 years, things have accumulated and we literally have thousands of reports gasp between all of the backups, Work In Process, and flat out duplication. In order to handle the review of this we wanted to assign prople to review specific sections of the reports. I was asked to provide a nice excel ‘report’ that would break down the report / folder hierarchy so that we could assign it.
That is when I realized that I really couldn’t locate a tool / report that would generate such a report listing!
In the old 7.2.5 version of Reports, it was relatively easy to get this information as all of the report information (including actual report content) was stored in a single database table.
With System 9 and Workspace, this is no longer true. With Workspace, the files are stored on the server’s file system in a manner consistent with the hierarchy of your reports/folders. Intially I thought I would be able to use this file system to build my report using simple DOS commands. (i.e. dir /s > reportlist.txt) Unfortunately though, the file names of the reports and folders are not ‘human readable’. (In order to decipher it, you’d have to go into the workspace database and x-ref these names to a table, etc)  Fortunately; however, you can build a report listing via SQL. The script below recursively builds a report with the following output :

(NOTE: Actually the script includes more columns of output; however, for display purposes here, I’m only showing the name and description)

Report Name     Description
AnnualReports NULL
===Actg-YContractual Obligations – Multiple Entities Contractual Obligations
===Annual Validation Summary Annual Validation Summary
===Customers and End User Markets NULL
====== 001 – End Use Markets Book End Use Markets Book
====== 002 – Top Customers Book Top Customers Book
====== 010 – Customers Book Reports – Corp NULL
========= Revenue by Top Customers – All Sectors – Pr Yr Comp Customer Revenues
========= Revenue by Top Customers – All Sectors – Pr Yr Comp -TEST Customer Revenues
====== 020 – Markets Book Reports – Corp NULL
========= Revenue by End User Markets – All Sectors End Use Markets
====== 030 – All Sectors – End Use Markets – Pr Yr Comp NULL
========= End Use Markets – Pr Yr Comp Customer Revenues


SQL 2005+ Query
USE BIPLus_prod;





— Anchor member definition



WHERE d.PARENT_FOLDER_UUID = ‘0000011b42b29e00-0000-0b4d-c0a8e22a’ UNION ALL

— Recursive member definition

SELECT d1.PARENT_FOLDER_UUID, d1.CONTAINER_UUID, d1.NAME, d1.DESCRIPTION, Level + 1, cast(Sort + ‘|’ + d1.Name as nvarchar(1024)), d1.CREATION_DATE, d1.LAST_MODIFIED_DATE




— Statement that executes the CTE


How this works :
The table V8_CONTAINER holds the main part of the report / folder entry data. In this table you will find Names, Creation Dates, Modified Dates, Owner name, UUID, and Parent UUID information. In order to build a hierarchy, you will need to pay careful attention to the UUID fields. CONTAINER_UUID is the internal name of the object in Workspace. (i.e. non-user friendly name) PARENT_FOLDER_UUID is a reference back to the item’s parent.
With this in mind, if you know the UUID of your starting point, you can simply recurse from there to get a listing of all folders and subsequent reports. In the script above I start off looking for an entry that is the starting point of our reports that we wanted to audit. For your purposes you will need to change this to what your starting point is.

NOTE : If you want to get everything in the table, use REPORTMART. REPORTMART is the parent to everything in the table.
I hope my walk through wasn’t terribly boring and helpful. I apologize for only providing a script that works in SQL Server; however, that is what we use at our site and there should be enough explanation here for someone relatively proficient with Oracle to write a similar script if they desire.

Synchronizing FDM (9.3.1) Applications w/ T-SQL

At my current location we have 1 main and 2 supporting FDM (9.3.1) applications. The main app is used to load production G&L data, and the supporting apps are used for loading our Budget and Forecast data and all are separated to ensure that a mistake in a multi-load template is minimized.
The problem we have with this setup is that we have to do 3x the maintenance work when it comes to users and locations.

While the users and locations are the exact same in each application, there are some differences; however: a.) Data Maps in Budget / Forecast are simply * to * as we use multi-load templates that have hte actual HFM account/entity names in them whereas production maps account for each company’s GL b.) Categories are different as we limit each FDM app to only the exact Category (Scenarios) required. c.) Periods are different as we limit each FDM app to only the needed periods required.

The solution for me was to implement a SQL direct copy routine for the information I need to move. This helps as I can : a.) automate this task b.) copy only what I want c.) Perform the task relatively quickly.

While the Workbench offers the ability to import / export components, I ran into trouble with it not correctly importing all of the data and it also restrcted my flexibility. Copying the entire database was not viable either as there were pieces I did not want to update….
The script below illustrates how to perform a copy via SQL. *bold*Please note that if you want to use this (or base a script off of this) that there’s absolutely no warranty and its use at your own risk.bold I’m fairly comfortable that this works fine (as I’ve tested this in our Dev), but you should test it on your own as well.

Also note, that this script does not copy ALL tables as I didn’t need them all for my purpose.

The script performs the following :

– Clears data from Target Database – Data Archives – Data Maps – Data
– Logs – Import Groups/Items – Validation Groups/Entities – Users / Partition Security – Partition Hierarchies / Links – Partitions (locations)
– Copies data from Source Database: – Import Groups / Items – Validation Groups / Items – Partitions (locations) – Partition Hierarchy / Links – Users / Partition Security
– Updates Parent Location setting on all Data Load locations (This is since I want all of my data load locations in the Budget / Forecast apps to use a * to * map. I added a new location in the Prod Database which is defined with the * to * mappings and when I copy everything over, I want the locations to use this map instead of their production G&L map.