One of the lesser-known features included in the Operis Analysis Toolkit (OAK) is its ability to integrate into automated Excel workflows thanks to VBA. Yes, OAK’s features are conveniently accessible via the Excel ribbon interface but some workloads benefit substantially from macro automation, especially where large numbers of files are involved.

Why use scripting with OAK?

OAK already makes many Excel operations far easier than they would be if performed manually, whether it is bulk changing names into cell references, highlighting hardcoded constants or searching for complex formulas.

However, sometimes a user needs to execute an operation across a large number of spreadsheets. Under these circumstances pointing and clicking won’t be practical, but a few lines of VBA could achieve rapid results. In fact, OAK plus a small amount of VBA scripting could achieve the same results as more specialised, far more expensive tools – at least in some use cases.

Sample use cases for OAK scripting

Combining OAK with scripting is particularly useful wherever you need to deal with a large number of workbooks. For example, utilising VBA with OAK can greatly speed up department or enterprise-wide operations that involve examining countless spreadsheets. These are some example use cases where VBA and OAK can greatly speed matters up:

• Automated summaries. A VBA script could deploy OAK Summarize over a large number of files, automatically generating reports that can be used in evaluating the scope and reach of workbooks. In fact, when scripted correctly, OAK alongside VBA can select workbooks for further investigation according to a set
• Auditing workflow. You may have an auditing workflow consisting of steps that are frequently performed, perhaps to comply with regulatory regimes. If this workflow uses OAK functionality, consider automating it by harnessing VBA to automatically apply OAK functions on a group of workbooks.
• Finding at-risk workbooks. Formula complexity and other factors can lead to workbooks that carry a degree of spreadsheet risk. OAK in combination with a VBA script can automatically highlight workbooks that contain an overuse of hard-coded constants while scoring formulas according to an OAK-derived formula risk score.

How to get started in scripting with OAK

Scripting with OAK is straightforward. OAK’s functionality is available as a VBA Type Library, accessible when you enter the VBA development environment. You need to enable the OAK Type Library first, but once OAK is available you can view OAK’s functions by filtering for “Operis_OAK” in the VBA Class Browser.

The Class Browser will give you insight into which OAK functions are available via VBA. Going forward you simply incorporate any of the listed OAK functionality into your VBA Script. The OAK help file contains a number of examples to help get you started.

Who should use OAK scripting?

OAK users who engage OAK in highly repetitive tasks involving a lot of files should consider ways in which VBA scripting can automate the process, as opposed to handling files one by one. Using VBA inside of Excel is beyond the scope of many users and you may need to seek assistance from your internal team. Alternatively, contact Operis and we will gladly advise on how OAK can help automate your spreadsheet processes.