So, typically we write code for an employer who pays us a salary. Sometimes as a VBA programmer one considers how to get paid for software that we write on an independent software vendor (ISV) basis. The tragic news is that VBA code is easy to break into. I should imagine that is the reason why there is very little information within the VBA 'blogosphere' as to how to protect software. It is inevitable that as VBA programmers we must learn another language, one that compiles preferably to machine code and second preference to virtual machine code. But even after learning such a language what would the code protection mechanism look like?
In short, I guess that one writes code that is callable but that there is an initialisation routine at which point one checks a licence file and if licence not valid then refuse to operate so the callable parts refuse to operate. Refusal to operate should be easy enough to implement. But how to check validity of a licence file?
Using our beloved Excel as an example, a quick Google turns up the following article from accountingweb.co.uk Microsoft Excel Licensing - what you can and can't do.... It shows the limits of install backdrops in that it limits virtual PC installs as well as forbidding Server installs. However, for the purposes of this article is it essentially a per device licence. if in doubt consult his article, Microsoft Office 2013 license limits ONE install per computer. So how is a per device enforced? It must read details of the host device and encrypt them somehow and prevent tampering
I sort of knew this already, i.e. that one need to tie a licence to a piece of hardware but which piece of hardware becomes the question.
Should I use Network Interface Card (NIC) as a licence key point?
I had heard that tieing to a NIC is one way to do this, so each NIC has a unique serial number etc. etc. etc. and if you want to enumerate your network interfaces then this Superuser article would be of use Where can I find my NIC’s GUID? or perhaps this. But questions have to be asked about whether a NIC is the right basis for a 'hardware fingerprint', after all NICs do fail and then they get swapped out, should a user have to buy another licence because their network component failed?
I would have though am motherboard id is better than a NIC id. If a NIC fails then a computer room operator swaps out the NIC but if a motherboard fails then they'd need to rebuild the whole computer, IMHO.
Whilst I have praised Tim Hall's excellent JSON parsing code the following leverages Microsoft's own parser. Tim Hall's is better when you want to amend the object, i.e. read-write. If you are happy with read-only then the Microsoft parser could be a better option but there is one caveat in that Microsoft's parser will execute any Javascript embedded in the JSON and this is a security breach. But if you know the provenance of the JSON and know it to be free from Javascript then the following code will be of use to you.
Use ScriptControl.Eval to parse a JSON object in Excel VBA
In following code, I have given the canonical example from http://json.org/example.html. Paste this code into VBA, add Tools->References->Microsoft Script Control 1.0
Function JsonLiteral() As String
Const sJSON As String = "{" _
& " ""glossary"": {" _
& " ""title"": ""example glossary""," _
& " ""GlossDiv"": {" _
& " ""title"": ""S""," _
& " ""GlossList"": {" _
& " ""GlossEntry"": {" _
& " ""ID"": ""SGML""," _
& " ""SortAs"": ""SGML""," _
& " ""GlossTerm"": ""Standard Generalized Markup Language""," _
& " ""Acronym"": ""SGML""," _
& " ""Abbrev"": ""ISO 8879:1986""," _
& " ""GlossDef"": {" _
& " ""para"": ""A meta-markup language, used to create markup languages such as DocBook.""," _
& " ""GlossSeeAlso"": [""GML"", ""XML""]" _
& " }," _
& " ""GlossSee"": ""markup""" _
& " } } } } }"
JsonLiteral = sJSON
'* Requires Tools->References to Microsoft Script Control 1.0
Dim oSC As ScriptControl
Set oSC = New ScriptControl
oSC.Language = "JScript"
Dim obj As Object
Set obj = oSC.Eval("(" + sJSON + ")")
Stop
'Look at the Locals window to see the structure of the JSON
End Function
You'll see once evaluated then the Locals object allows you to navigate the structure.
Use VBA.CallByName and hasOwnProperty to navigate parse structure
Some more examples show how you can use CallByName to get sub-objects and also elements of an array. Also shown here is hasOwnProperty which can be used for defensive programming.
Private Sub CallByNameAndHasOwnProperty()
Dim oScriptEngine As ScriptControl
Set oScriptEngine = New ScriptControl
oScriptEngine.Language = "JScript"
Dim sJsonString(0 To 1) As String
sJsonString(0) = "{'key1': 'value1' ,'key2': { 'key3': 'value3' } }"
sJsonString(1) = "[ 1234, 2345, 3456, 4567, 5678, 6789 ]"
Dim objJSON(0 To 1) As Object
Set objJSON(0) = oScriptEngine.Eval("(" + sJsonString(0) + ")")
Set objJSON(1) = oScriptEngine.Eval("(" + sJsonString(1) + ")")
Debug.Assert objJSON(0).hasOwnProperty("key1")
Debug.Assert objJSON(0).hasOwnProperty("key2")
Debug.Assert CallByName(objJSON(1), "length", VbGet) = 6
Debug.Assert CallByName(objJSON(1), "0", VbGet) = "1234"
End Sub
Caveat- Don't Overuse ScriptControl!
In addition to the warning about buried JavaScript in JSON I have another warning regarding Microsoft ScriptControl. Although it may look like a brilliant keyhole interface into the world of Javascript execution in VBA it is stuck in a time warp. I have established that Script Control is limited to Javascript EcmaScript v.3. Whilst current is version 6. Don't think you can code up some Javascript in say Microsoft Visual Studio Code and then paste into the ScriptControl with AddCode because chances are you will hit an incompatibility. Also with the ScriptControl you cannot debug code.
I experimented with ScriptControl for some time and tried to push its boundaries, initially it was exciting but it can be frustrating. Search this blog for JSON posts for any updates.
OLE stands for Object Linking and Embedding and COM was invented to facilitate OLE. Nice to see all the COM interfaces still working.
Inserting a worksheet into Word document
Open Word, create new document go to VBA IDE and paste in the following code to see how to insert a worksheet and script against the worksheet as if you were in Excel VBA.
Option Explicit
'Paste into Word VBA
Sub ObjectLinkingAndEmbeddingExample()
Dim shp As Shape
Set shp = ThisDocument.Shapes.AddOLEObject("Excel.Sheet")
shp.OLEFormat.Object.Sheets(1).Cells(1, 1) = "OLE works!"
End Sub
It would appear the gateway through to the Excel object model is given by OLEFormat.Object on the shape class.
Simulating an ActiveX command button click in code
Sometimes the gateway can require two successive calls to Object. In an Excel worksheet go to Developer mode and insert an *ActiveX* command button (not intrinsic Excel button), then double click to get to Click handler code and add the following
Option Explicit
Private Sub CommandButton1_Click()
MsgBox "CommandButton1_Click"
End Sub
Then come out of developer mode and click the button to ensure the message box is thrown. Then insert the following code in a non-class module and run it.
Option Explicit
Sub SimulateButtonClick()
'* After adding an *ActiveX* command button on the activesheet
Dim shp As Shape
Set shp = ActiveSheet.Shapes(1)
Debug.Assert TypeName(shp.OLEFormat.Object.Object) = "CommandButton"
Dim cmd As CommandButton
Set cmd = shp.OLEFormat.Object.Object
'* The next line compiles but Value is not is the Intellisense dropdown menu.
'* It mimics the clicking on the button and should throw the message box in
'* above handler code (see block above)
cmd.Value = True
End Sub
Running this code will mimic the button click. You can see how in this case you need two calls to Object to get the plumbing right otherwise you get a type mismatch error
So when writing an object with ATL for use in Excel VBA I recommend selecting dual interface from the Simple Object Wizard this gives both a binary vtable interface for early-binding clients as well as an IDispatch implementation for late-binding clients. Note, when I say 'late binding clients' I'm not talking about VBScript clients but VBA developers who'd prefer to call into a COM library with flexibility.
But there is a fly in the ointment here. COM is all about interface based programming where one is supposed to factor out the functionality of a class into separate interfaces, multiple dual interfaces will not compile in ATL because every Com class must only inherit from IDispatch once.
It isn't just the use case of factoring out functionality into separate interfaces but also the use case of shipping an additional interface to supplement, update or enhance the functionality of a class. In both use cases ATL will only allow one of these interfaces to be dual. To resolve the ATL compilation errors you will need designate one interface as being prime and only allow late-binding to that interface.
This pretty much scuppers the factoring out use-case. Developers coming from VBA will probably not grieve over this as they are used to using one interface only (VBA does have the Implements keyword but is rarely used). For the shipping updates use-case you should ship a new Com class with a new expanded all encompassing interface.
I could give some sample code but actually I have found some internet pages that illustrate the problem.
com problem inheriting twice from IDispatch ATL inheritance mistake
They also give the solution which is to make one interface the goto interface when QueryInterface is called for IDispatch by using the COM_INTERFACE_ENTRY2 macro. So the following non-compiling code
In syntax terms, late binding is when you declare a variable with As Object instead of giving its Type. So for example scripting against the Excel object ...
Option Explicit
Sub Test()
'* Early-bound - requires Type Library (Tools References)
Dim ws As Excel.Worksheet
'* Late-bound - uses IDispatch - slower but more flexible
Dim objWs As Object
Set ws = ThisWorkbook.Worksheets.Item(1)
Set objWs = ThisWorkbook.Worksheets.Item(1)
Debug.Print ws.Name
Debug.Print objWs.Name
End Sub
In the above example objWs can access all of the methods of Worksheet without reference to a Type Library (Tools References) it does this via the IDispatch interface which is a flexible dynamic type discovery interface that interrogates an object at run-time instead of at compile time.
When would you use Late Binding?
Well the above example is not a good use case. There is no need to use late binding when the library you are scripting against is written and published by a proper software house such as Microsoft. Late-binding was invented for scripting clients like VBScript. However, VBA developers can find advantage from using late-binding when working in a large organisation and co-operation between development teams is not optimal.
At some point in your career, you may find your VBA code depends on a library from another team in your large organisation. The rules of COM interfaces are clear, don't break an interface once published, new behaviour needs to ship in a new interface or also in a new object that goes with the new interface. But sh*t happens, a team changes an interface which breaks your VBA code, how is the break handled?
If you are using early binding and type libraries then you will get a compile error. Imagine that scenario, you get a support call from an important user saying your spreadsheet broke along the lines of "Compile error in hidden module:foo"
You would have to visit (or remote into) their machine, unlock the code (assumes password protection) and go figure out what went wrong. This can be quite embarrassing.
On the other hand if you are using late-binding then your code will only break on a line of code directly affected with the error message
This kind of error is trappable with On Error Goto whilst a compile error is not!
What are downsides to Late-Binding?
Well, you lose Intellisense which can be a boon to writing code but there is nothing to stop you from writing code with early-binding and then changing to late binding when going to test and production.
Also, we have to address speed. There is no doubt that the extra calls into IDispatch carry a performance penalty though for in-process DLL calls I'd say this is minimal. When you have a remote component then I would suggest not using late-binding because network calls are very expensive. For components running locally on the computer but in another process, the call is 50/50.
Summary
Although IDispatch was invented for scripting clients like VBScript it also provides an option for VBA developers to defend against breaking changes in type libraries from other teams.
I'm not religious about this issue but the having the option to late-bind requires the developers of the component you rely on to ship an IDispatch implementation and sometimes this has its own consequences.
I've just rustled up this post because I want to address an ATL design dilemma and needed to sketch out the stakes.
I've never leaked a document and never had a desire to but this story caught my interest. Colour printers also print microdots than give away the printer serial number and date and time of printing. Leakers beware!
Some good advice at the bottom of this cybersecurity post, consider converting your image to two tone black and white to either make visible or eliminate microdots.
So Excel VBA developers may wonder how to make their code run as fast as compiled VB6 and I'm, afraid they can't as VB6 is no longer supported. To increase the speed of your code you need to use C++ and then call in from VBA using COM. So you need a C++ COM technology and this is what Active Template Library is.
The Books
So I have completed reading one ATL book, Beginning ATL Programming by Richard Grimes et al (Wrox 1999) and in the final stages of reading another, Inside ATL by King and Shepherd (MSPress 1999). So it is worth putting up some revision notes so I don't forget all that I have learnt. Whilst the prose of the books was better than the dry Microsoft documentation, the MS website remains the place to link to. To follow these notes you'll need to understand key C++ features such as templates and multiple inheritance. You also need to be very familiar with the COM specification, we shall not here explain the role of IUnknown or IDispatch. We'll limit the focus to in-process DLLs and ignore .EXEs
Walkthrough
So I am walking through creating a new ATL project, my project name is ATLProject2.
Once through the new ATL project wizard one is confronted with a great many files but don't be intimidated, there are more wizards from the Class View so ensure the Class View is visible.
From the Class View go to right-click menu and take Add Class and then take ATL Simple Object which throws the ATL Simple Object Wizard. On the Names Dialog, in the C++ Short Name type "CoolCode" and the other fields are auto-generated for you. Click through the File Type Handler Options to the last dialog, Options which looks like this
So I have checked Support ISupportErrorInfo because we will want to throw rich error information from C++ to VBA. Click Finish and some code is generated for you.
What gets instantiated and what implements IUnknown::QueryInterface
Never new your class
So your class never gets instantiated with the new keyword, it can't because it has no vtable because of the ATL_NO_VTABLE macro. Instead, a creator class such creates an instance of CComObject (when not aggregated) with your class as a template.
To illustrate, it is worth looking at what happens when a client gets hold of IClassFactory and calls IClassFactory::CreateInstance, so find the definition of CComCoClass (select, F12) to get to this (abridged) code
So in the above code one can see CreateInstance being called in two use cases, (i) where there is an aggregating object and (ii) where there isn't but the code shares a common element of T::_CreatorClass::CreateInstance. It is worth knowing that T::_CreatorClass is defined by the DECLARE_AGGREGATABLE macro which was generated by your choice in the wizard to allow aggregation. This macro is defined as
#define DECLARE_AGGREGATABLE(x) public:\
typedef ATL::CComCreator2< ATL::CComCreator< ATL::CComObject< x > >,
ATL::CComCreator< ATL::CComAggObject< x > > > _CreatorClass;
Wow, that is really a complicated syntax and I won't try to explain it because that would replicate the book/documentation. Suffice to say one can see the CComObject as referred to in the class diagram above. I will give some links though ...
This class implements the IUnknown interface for an aggregated object. By definition, an aggregated object is contained within an outer object. The CComAggObject class is similar to the CComObject Class, except that it exposes an interface that is directly accessible to external clients.
CComCreator & CComCreator2
These are undocumented though referenced in a Don Box article
If not new then what?
So I mentioned above that one doesn't use new on your class. Let's suppose you have a use case where you have two com classes in your server project and the method on one returns an instance of the other. Without calling the COM API CoCreateInstance (which would be the long way round) how do you create an instance of your com class and return it to a client? The answer is use (some of) the same classes as the class factory above. So here is some sample code
STDMETHODIMP CUncoolCode::CreateCoolCode(ICoolCode ** ppCool)
{
// From Grimes et al (1999) p. 143
*ppCool = NULL;
return CComCreator< CComObject<CCoolCode> >::CreateInstance(
NULL, IID_ICoolCode, reinterpret_cast<void**>(ppCool) ) ;
}
Summary
Well, ATL is complicated if you come from a VBA background, it is advised to never change code generated by the wizards unless you totally know what you are doing. ATL demonstrates not just the power of templates but also multiple inheritance and templates. Awesome.
