At this point I'd like to recap this series and describe what remains to be told of Excel Industrialisation.
This is the power of the VBE. From within VBA code you can create and manipulate VBA . In this example we have added a constant that is created dynamically from the module name and method name. But we can do a lot more within VBA - we can clean up code, remove unused variables and constants, add line numbers, add powerful error handling capabilities and a considerable range of other useful task.
The key point is that the source code is controlled at the module level, rather than by versioning a specific spreadsheet. This more granular control means that code can be managed and supported in a much more efficient manner. Code can be created within modules and a check-in process can ensure that each module is updated to the latest version. No more manual search and replace for a vast accumulation of spreadsheets on a series of network shares.
A reference is a way for the Excel run-time environment to know where to find code - think of it as a way to #include code if you think in C++. A problem is that references exist to files that are outside of the control of the Excel run-time. This leads to some nasty manifestations of "Dll Hell" for the Excel user. This is something that can be examined through the use of VBE.
But there is a way to try and track down errors that's poorly documented by Microsoft but which is very helpful. That's the Erl property.
When engaging in Excel Industrialisation we need to keep track of external dependencies that impact on the functioning of a spreadsheet. We have already looked at Addin references, in this post we cover the case of RTD Servers.
The Excel RTD Server has a pretty light footprint in terms of the data that is exposed to the Excel Object Model.
A DDE Server is an old technology that is still used by a number of vendors to push real time data into Excel. It has mainly been replaced by RTD but is still in use by many firms for internal applications and for some legacy applications. In some quite particular use cases DDE outperforms RTD and so it will remain in use for some time yet.
The requirement for Excel Industrialisation is to provide an operating paradigm such that a portfolio of Excel spreadsheets can be supported and managed using code rather than people. This post covers the case of enumerating all of the DDE servers that are included with an Excel Workbook.
COM Addins can, as the name suggests, be created in any language that supports COM. In the past I have done so in VB6 which was very easy. The move to dot net meant that there was a need for more code to get from the COM world to the CLR. And the process of deploying VB6 based COM Addins in Excel 2013 has been further complicated by Microsoft removing some components needed at run time.
As we have seen within the Enumerating RTD Servers post, from a ProgID we can find the actual code that is executed, so that is not shown in the VBA code in this example. And of course, from a GUID of a COM object it's trivially simple to find the code.
Here we are looking at OLE Objects. OLE - "Object Linking and Embedding" was an integration technology that Microsoft advocated from the mid 1990s onwards.
One of the more interesting legacy methods on interacting with Excel is through the macro language that was supplied in older versions - Excel 4 XLM. The challenge of using XLM is simply that it has been deprecated for so long it's pretty hard to actually find documentation or find anyone who remembers how to use it. However, I have found XLM lurking around in dark corners of firms.
Another way to interact with Excel is through WorkbookConnections from external data sources to an Excel Workbook.
Excel 2013 included a number of changes - one of the big changes is the addition of Excel.Model. In essence, this allows the user to treat data within the Excel spreadsheets as though it is contained within database tables. This allows the use to create validation and a number of other useful techniques.
The use of "Goto" is generally deprecated and will catch every error that occurs. This means that the ErrHandler code has to be able to handle every error that may occur in the code. For a long piece of code with many possible errors it becomes burdensome to actually manage the code that does the work and then the code that handles errors. It's a platform limitation.
The implementation here is one that uses Microsoft Message Queuing to report to a central hub (not shown here). The beauty of MSMQ is that the error message can be captured even when the computer is not connected to a network, so there is no limp implementation that only works when you are connected to a network.
There is a slight challenge in the sense that this would normally be done using a user interface such as github rather than from within the VBA development environment. Happily there is a simple way to integrate Git with VBA.
The key functionality needed is delivered in the Windows Script Host Object Model (typically at C:\Windows\System32\wshom.ocx). This contains IWshRuntimeLibrary.WshShell which is where most of the functionality used to interface to git is located.
The key functionality needed is delivered in the Windows Script Host Object Model (typically at C:\Windows\System32\wshom.ocx). This contains IWshRuntimeLibrary.WshShell which is where most of the functionality used to interface to git is located.
So, what is left to cover? Future posts will include:
Adding line numbers to code to improve error handling
Cleaning up code by finding unused code, unused variables and unused constants
Running audit reports on a portfolio of spreadsheets
And a few other topics.
Comments
Post a Comment