Use VBA to make a folder shareable

So some poor guy got heavily downvoted asking, reasonably, how to use VBA to make a folder shareable. Here is the answer, I cannot post on SO because the question is on hold.

Option Explicit

Sub TestRajeshS()
    '* For Rajesh S
    '*
    '* How to make a folder shareable
    '* needs admin permissions!
    '* Answer to https://stackoverflow.com/questions/45525238/how-can-i-make-the-folder-sharable
    
    '* copyright
    '* based on https://blogs.msdn.microsoft.com/imayak/2008/12/05/vbscript-for-creating-and-sharing-a-folder/#
    '* Owner - Imayakumar J.   Date - December 5 2008
    '* end of copyright
    
    '----------------------------------------------------
    'Create folder
    '----------------------------------------------------
    
    Dim filesys As Object
    Set filesys = CreateObject("Scripting.FileSystemObject")
    
    Dim sFolderName As String
    sFolderName = "n:\ShareThis"
    
    If Not filesys.folderexists(sFolderName) Then
        filesys.createfolder sFolderName
    End If
    
    '---------------------------------------------------------
    ' Check if another shar with the same name exists
    '---------------------------------------------------------
    
    Dim strComputer As String
    strComputer = "."
    
    Dim objWMIService As Object
    Set objWMIService = GetObject("winmgmts:" _
        & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
        
    Dim colShares As Object
    Set colShares = objWMIService.ExecQuery _
        ("Select * from Win32_Share Where Name = 'MYSHARENAME'")
    
    Dim objShare As Object
    For Each objShare In colShares
        objShare.Delete
    Next
    
    '-----------------------------------------------------
    ' Share the created folder
    '-----------------------------------------------------
    
    Const FILE_SHARE = 0
    Const MAXIMUM_CONNECTIONS = 25
    strComputer = "."
    Set objWMIService = GetObject("winmgmts:" _
        & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
        
    Dim objNewShare As Object
    Set objNewShare = objWMIService.Get("Win32_Share")
    
    Dim errReturn As Variant
    errReturn = objNewShare.Create _
        (sFolderName, "MYSHARENAME", FILE_SHARE, _
            MAXIMUM_CONNECTIONS, "Sample share created with Microsoft Scripting Runtime.")
    
    If errReturn = "0" Then
        Debug.Print "Success"
    Else
        '* did you forget to run as admin?
        Debug.Print "Task Failed - did you forget to run as admin"
    End If
    
    '---------------------------------------------
    ' Script End
    '-------------------------------———————
    

End Sub

Some notes on Ivan Ristic's OpenSSL Cookbook

So Ivan Ristić has donated some chapters of OpenSSL documentation free which is welcome and we thank him for this. From the point of view of an Excel developer, I'm interested in digital signatures, shipping a C++/C# component with a digitally signed licence file to prevent those who have not paid but who have acquired a copy of the executable from running the software.

So, I will give some notes and quotes.

Key Generation

Key algorithm

For SSL keys everyone uses RSA and ECDSA keys are yet to be widely supported by CAs

Key size

When generating a key use a longer length than the default. Bit for bit Eliptic Curve keys are stronger.

Today, 2048-bit RSA keys are considered secure, and that’s what you should use. Aim also to use 2048 bits for DSA keys and at least 224 bits for ECDSA

Passphrase

Using a passphrase with a key is optional, but strongly recommended ... passphrases should be viewed only as a mechanism for protecting private keys when they are not installed on production systems. In other words, it’s all right to keep passphrases on production systems, next to the keys.

Command Line Key Generation

RSA Keys

To generate private key use the following command, new file is fd.key. Don't forget your passphrase!

c:\OpenSSL-Win64\bin\openssl genrsa -aes128 -out fd.key 2048
Generating RSA private key, 2048 bit long modulus
.......+++
..........................................................................+++
e is 65537 (0x10001)
Enter pass phrase for fd.key:
Verifying - Enter pass phrase for fd.key:

To generate corresponding public key use the following command, new file is fd-public.key. Don't forget your passphrase!

c:\OpenSSL-Win64\bin\openssl rsa -in fd.key -pubout -out -fd-public.key
Enter pass phrase for fd.key:
writing RSA key

I skipped showing output because RSA keys are long hence the attraction of elliptic curve keys.

Elliptic Curve Keys

Here how to generate an elliptic curve key.

> openssl ecparam -genkey -name secp256r1 | openssl ec -out ec.key -aes128
using curve name prime256v1 instead of secp256r1
read EC key
writing EC key
Enter PEM pass phrase:
Verifying - Enter PEM pass phrase:

and to show its output

C:\OpenSSL-Win64\bin> openssl ec -in ec.key -text -noout
read EC key
Enter PEM pass phrase:
Private-Key: (256 bit)
priv:
00:a8:bc:2b:6e:9a:15:98:b5:5b:66:56:4e:8c:54:
ab:1a:df:85:25:60:d4:39:6e:b0:88:4f:ee:ea:fd:
e9:f5:93
pub:
04:8e:8b:a6:6f:97:8b:a7:30:59:72:7d:e1:f2:2e:
bd:7e:7e:ee:61:29:3e:a9:3d:41:2f:da:d0:71:67:
30:63:f8:86:dd:42:bd:0a:7b:67:7b:a1:93:12:61:
d0:aa:14:4e:c4:5e:97:64:7d:ae:75:97:c4:66:42:
87:14:08:d6:01
ASN1 OID: prime256v1
NIST CURVE: P-256

Referencing David DeRosa for extracting public key only

C:\OpenSSL-Win64\bin> openssl ec -in ec.key -pubout -out ec-pub.key
read EC key
Enter PEM pass phrase:
writing EC key

And to show the output of this reduced file use this

C:\OpenSSL-Win64\bin>openssl ec -in ec-pub.key -pubin -text -noout read EC key
Private-Key: (256 bit)
pub:
04:8e:8b:a6:6f:97:8b:a7:30:59:72:7d:e1:f2:2e:
bd:7e:7e:ee:61:29:3e:a9:3d:41:2f:da:d0:71:67:
30:63:f8:86:dd:42:bd:0a:7b:67:7b:a1:93:12:61:
d0:aa:14:4e:c4:5e:97:64:7d:ae:75:97:c4:66:42:
87:14:08:d6:01
ASN1 OID: prime256v1
NIST CURVE: P-256

Obviously keep the file with the private key secret [and not on a blog :) ]. You distribute the public key because that is what your client side digital signature validation code will use.

DSA Keys

I skipped the DSA section as I'm not interested in it.

Signing a licence file

So we give an example licence file which articulates the hardware fingerprint (unique to each machine), customer and the software components sold (or being evaluated). And we have chosen JSON as our file format.

{  
   "hardwareFingerprint":{ 
      "volC":"4715-932C"
   },
   "customer":{
      "name":"W Churchill"
   },
   "softwareLicences":[  
      {  
         "name":"spellChecker",
         "licenceType":"90 day evaluation",
         "expiryDate":"2017-Nov-05"
      },
      {  
         "name":"Chess",
         "licenceType":"paid",
         "expiryDate":"Never"
      }
   ]
}

Links

OpenSSL Cookbook

Cryptography - VBA code for Wikipedia's RSA example

So, following on from studying some cryptography I can give some VBA code which implements RSA or at least the example given on the RSA Wikipedia article. So the two primes p and q are given; calculating n is easy (multiply); we can use worksheet function lcm for the totient;e is given;I hunted around for some logic for "module multiplicative inverse" and finally we have the components for the private and public keys.

Also shown is some code to encrypt a message (very short one character "A" represented by ASCII 65) and decrypt it back to the original. To encrypt a larger message needs an arithmetic vehicle larger that that available in VBA, you'll see I'm declaring variables as Currency to get as many bits as possible but even this has limits when raising a number to a very high power exponentional.

Anyway, simple version that allows VBA programmer to step through the wikipedia example is given here ...

Option Explicit
Option Private Module

Private Type udtPublicKey
    n As Currency
    e As Currency
End Type

Private Type udtPrivateKey
    n As Currency
    d As Currency
End Type

'***************************************************
'               .__
'  _____ _____  |__| ____
' /     \\__  \ |  |/    \
'|  Y Y  \/ __ \|  |   |  \
'|__|_|  (____  /__|___|  /
'      \/     \/        \/
'***************************************************

Private Sub Main()

    Dim p As Currency
    Dim q As Currency
    Dim n As Currency
    Dim lambda_n As Currency
    Dim e As Currency
    Dim d As Currency


    p = 61
    q = 53
    n = p * q
    lambda_n = Application.Lcm(p - 1, q - 1)
    e = 17
    Debug.Assert IsCoPrime(e, lambda_n)
    
    d = ModularMultiplicativeInverse(e, lambda_n)
    Debug.Assert e <> d

    Dim uPrivate As udtPrivateKey
    uPrivate.d = d
    uPrivate.n = n
    
    Dim uPublic As udtPublicKey
    uPublic.e = e
    uPublic.n = n
        
    '* m is the message to encrypt, it needs to be a number
    '* 65 is ASCII for "A"
    Dim m As Currency
    m = 65
    
    '* c is the encrypted message
    Dim c As Currency
    c = Encrypt(m, uPublic)
    
    '* m2 is the decrypted message
    Dim m2 As Currency
    m2 = Decrypt(c, uPrivate)
    
    '* and the decrypted message should match the original
    Debug.Assert m2 = m
     
End Sub


Private Function Encrypt(ByVal m As Currency, _
                    ByRef uPublic As udtPublicKey) As Currency
    If m > uPublic.n Then Err.Raise vbObjectError, , _
            "#text is bigger than modulus, no way to decipher!"
    
    Dim lLoop As Long
    Dim lResult As Currency
    lResult = 1
    For lLoop = 1 To uPublic.e
    
        lResult = ((lResult Mod uPublic.n) * (m Mod uPublic.n)) Mod uPublic.n
    Next lLoop
    Encrypt = lResult
End Function

Private Function Decrypt(ByVal c As Currency, _
                    ByRef uPrivate As udtPrivateKey) As Currency
    If c > uPrivate.n Then Err.Raise vbObjectError, , _
            "#text is bigger than modulus, no way to decipher!"
    Dim lLoop As Long
    Dim lResult As Currency
    lResult = 1
    For lLoop = 1 To uPrivate.d
        lResult = ((lResult Mod uPrivate.n) * (c Mod uPrivate.n)) Mod uPrivate.n
    Next lLoop

    
    Decrypt = lResult
End Function

Private Function IsCoPrime(ByVal a As Currency, ByVal b As Currency) As Boolean
    IsCoPrime = (Application.Gcd(a, b) = 1)
End Function

Private Function ModularMultiplicativeInverse(ByVal e As Currency, _
                    ByVal lambda_n As Currency)
    Dim lLoop As Currency
    For lLoop = 1 To lambda_n
        If lLoop <> e Then
            Dim lComp As Currency
            lComp = lLoop * e Mod lambda_n
            If lComp = 1 Then
                ModularMultiplicativeInverse = lLoop
                Exit Function
            End If
        End If
    Next
SingleExit:
End Function

Cryptography - Glossary

Cryptography is a massive subject and there is tons of stuff on the Internet. However, I'd love a little notebook of terms. So here I will begin my own personal list. I'll use HTML anchors to each entry for extras URL granularity.

AD CS abbr. Active Directory Certificate Services, provides customizable services for creating and managing public key certificates used in software security systems employing public key technologies.

AES abbr. Advanced Encryption Standard, also referenced as Rijndael .

affine cipher n. a type of monoalphabetic substitution cipher. Each letter is enciphered with the function (ax + b) mod 26, where b is the magnitude of the shift.

alphabet n. a collection of symbols, also referred to as characters.

ANSI abbr. American National Standards Institute is a private non-profit organization that oversees the development of voluntary consensus standards for products, services, processes, systems, and personnel in the United States.

assymetric adj. describes cryptosystems where the encryption key differs from decryption key.

BF abbr. Blowfish, is a symmetric block cipher. (USA)

bijection n. In mathematics, a bijection, bijective function or one-to-one correspondence is a function between the elements of two sets, where each element of one set is paired with exactly one element of the other set, and each element of the other set is paired with exactly one element of the first set. There are no unpaired elements.

bit n. a character 0 or 1 of the binary alphabet.

block cipher n. a cipher which acts on the plaintext in blocks of symbols.

CA abbr. certification authority.

Camellia n. Camellia (cipher), is a symmetric key block cipher.

Cast n. CAST-128, is a symmetric key block cipher.

CBC abbr. cipher block chaining is a mode of operation for a block cipher.

certification authority n. a certificate authority or certification authority (CA) is an entity that issues digital certificates.

Caesar cipher n. a translation cipher for which b=3, used by Julius Caesar.

CFB abbr. Ciphertext Feedback is a mode of operation for a block cipher.

character n. an element of an alphabet.

cipher n. 1. a map from a space of plaintext to a space of ciphertext.
               2. a pair of algorithms, one for encryption and one for decryption.

ciphertext n. the disguised message.

CMVP abbr. cryptographic module validation program.

CMS abbr. cryptographic message syntax is the IETF's standard for cryptographically protected messages.

CNG abbr. Windows Cryptography API: Next Generation (CNG) replaces CryptoAPI 1.0 as the recommended Windows API cryptographic suite.

coprime adj. No common factors other than 1. Also called "relatively prime" or "mutually prime".

CRL abbr. certificate revocation list.

cryptanalysis n. the science (complementary to cryptography) concerned with the methods to defeat cryptographic techniques.

cryptographic hash function n. a special class of hash function that has certain properties which make it suitable for use in cryptography.

cryptographic nonce n. In cryptography, a nonce is an arbitrary number that may only be used once.

cryptography n. the study of mathematical techniques for all aspects of information security.

cryptology n. the study of cryptography and cryptanaylsis.

CSP abbr.Cryptographic Service Providers

CTR abbr. Counter-mode encryption, a block cipher mode of operation that uses incrementing IV counter for the key stream source.

cryptosystem n.

1. a suite of cryptographic algorithms needed to implement a particular security service, most commonly for achieving confidentiality (encryption). 2. (Mathematics) can formally be defined by a collection of sets, "plaintext space","ciphertext space","key space","set of encryption functions","set of decryption functions". See Cryptosystem.

decipher v.tr. to convert ciphertext into plaintext.

decode v.tr. to convert the encoded message back to its original alphabet and original form.

DRBG abbr. Deterministic Random Bit Generators

DES abbr. Data Encryption Standard is a symmetric-key block cipher published by the NIST.

digital certificate n. A digital certificate uses public-key cryptography to sign data and to verify the integrity of the certificate itself. Public key cryptography is a system based on pairs of keys called public key and private key.

DH abbr. Diffie Hellman.

DSA abbr. Digital Signature Algorithm.

ECB abbr. Electronic Code Book is a mode of operation for a block cipher.

ECC abbr. elliptic curve cryptography.

ECDH abbr. elliptic curve Diffie–Hellman , an ECC algorithm endorsed by NIST for key exchange.

ECDSA abbr. Elliptic Curve Digital Signature Algorithm , an ECC algorithm endorsed by NIST for digital signature.

EDE abbr. encrypt decrypt encrypt.

EFS abbr. Encrypting File System on Microsoft Windows is a feature introduced in version 3.0 of NTFS[1] that provides filesystem-level encryption.

elliptic curve cryptography n. is an approach to public-key cryptography based on the algebraic structure of elliptic curves over finite fields. ECC requires smaller keys compared to non-ECC cryptography (based on plain Galois fields) to provide equivalent security.

encipher v.tr. to convert plaintext into ciphertext.

encode v.tr. to convert a message into a representation in a standard alphabet, such as to the alphabet {A, . . . , Z} or to numerical alphabet.

encryption n. the process of disguising a message so as to hide the information it contains; this process can include both encoding and enciphering .

exhaustive key search n. Exhaustive key search, or brute-force search, is the basic technique of trying every possible key in turn until the correct key is identified.

FIPS abbr. Federal Information Processing Standard.

FIPS 140-2 Publication 140-2, (FIPS PUB 140-2),[1][2] is a U.S. government computer security standard used to approve cryptographic modules. The title is Security Requirements for Cryptographic Modules.

GCM abbr. Galois Counter Mode is an efficient and performant mode of operation for symmetric key cryptographic block ciphers.

GMAC abbr. Galois Message Authentication Code, see GCM.

GCD n. greatest common divisor.

hash function n. any function that can be used to map data of arbitrary size to data of fixed size.

HMAC n. the Keyed-Hash Message Authentication Code (HMAC)

IDEA n. International Data Encryption Algorithm is a symmetric-key block cipher

IETF abbr. Internet Engineering Task Force.

injection n. (Mathematics) a one-to-one mapping.

injective adj. (Mathematics) of the nature of or relating to an injection or one-to-one mapping. See Injective function.

IPSec abbr. Internet Protocol security (IPSec) is a framework of open standards for helping to ensure private, secure communications over Internet Protocol (IP) networks through the use of cryptographic security services.

IV abbr. Initialization vector, also call "salt","nonce" (number used once), used to ensure uniqueness of the key stream.

KAT abbr. Known Answer Test.

Kerboros n. is a computer network authentication protocol that builds on symmetric key cryptography and optionally may use public-key cryptography during certain phases of authentication.

keystream n. a stream of random or pseudorandom characters that are combined with a plaintext message to produce an encrypted message (the ciphertext).

MAC abbr. Message Authentication Code sometimes known as a tag, is a short piece of information used to authenticate a message—in other words, to confirm that the message came from the stated sender (its authenticity) and has not been changed.

message digest n. a cryptographic hash function containing a string of digits created by a one-way hashing formula.

MD5 abbr. a message digest. MD5 is considered (by some) essentially "cryptographically broken and unsuitable for further use".

MIC abbr. Message Integrity Code, substitute for MAC and sometimes message digest.

NIST abbr. National Institute of Standards and Technology (USA)

NSA abbr. National Security Agency. (USA)

OAEP abbr. Optimal asymmetric encryption padding is a padding scheme often used together with RSA encryption.

OCSP abbr. Online Certificate Status Protocol

OFB abbr. output feedback is a mode of operation for a block cipher.

OpenSSL OpenSSL is a general purpose cryptography library that provides an open source implementation of the Secure Sockets Layer (SSL) and Transport Layer Security (TLS) protocols.

Passphrase n. A passphrase is a sequence of words or other text used to control access to a computer system, program or data. A passphrase is similar to a password in usage, but is generally longer for added security.

PEM abbr. Privacy-enhanced Electronic Mail. PEM is a de facto file format for storing and sending cryptography keys, certificates, and other data, based on a set of 1993 IETF standards defining "privacy-enhanced mail.".

permute v. submit to a process of alteration, rearrangement, or permutation.

PKCS abbr. Public-Key Cryptography Standards

PKI abbr. Public Key Infrastructure (PKI) is a set of roles, policies, and procedures needed to create, manage, distribute, use, store, and revoke digital certificates and manage public-key encryption.

plaintext n. the message to be transmitted or stored.

post-quantum cryptography n. refers to cryptographic algorithms (usually public-key algorithms) that are thought to be secure against an attack by a quantum computer.

protocol n. an algorithm, defined by a sequence of steps, precisely specifying the actions of multiple parties in order to achieve an objective.

public key n. A public key is created in public key encryption cryptography that uses asymmetric-key encryption algorithms. Public keys are used to convert a message into an unreadable format. Decryption is carried out using a different, but matching, private key. Public and private keys are paired to enable secure communication.

RC2 n. RC2 (from Ron's Code) is a symmetric-key block cipher.

Rijndael n. Portmanteau of Belgian cryptographers Vincent Rijmen and Joan Daemen and a synonym for AES.

RIPEMD abbr. RACE Integrity Primitives Evaluation Message Digest.

RSA abbr. RSA is one of the first practical public-key cryptosystems and is widely used for secure data transmission. RSA is made of the initial letters of the surnames of Ron Rivest, Adi Shamir, and Leonard Adleman.

Salt n. In cryptography, a salt is random data that is used as an additional input to a one-way function that "hashes" a password or passphrase. Salts are closely related to the concept of nonce. The primary function of salts is to defend against dictionary attacks or against its hashed equivalent, a pre-computed rainbow table attack.

SEED abbr. SEED is a block cipher developed by South Korea.

SHA abbr. secure hash algorithm (SHA) .

S/MIME abbr. Secure/Multipurpose Internet Mail Extensions (S/MIME).

SSL abbr. SSL (Secure Sockets Layer) is the standard security technology for establishing an encrypted link between a web server and a browser. This link ensures that all data passed between the web server and browsers remain private and integral.

symmetric adj. describes cryptosystems where the encryption key is the same as the decryption key.

stream cipher n. a cipher which acts on the plaintext one symbol at a time.

string n. a finite sequence of characters in some alphabet.

substitution cipher n. a stream cipher which acts on the plaintext by making a substitution of the characters with elements of a new alphabet or by a permutation of the characters in the plaintext alphabet.

suite B n. is a set of cryptographic algorithms promulgated by the National Security Agency as part of its Cryptographic Modernization Program.

TLS abbr. TLS (Transport Layer Security) is a successor to Secure Sockets Layer protocol, or SSL. TLS provides secure communications on the Internet for such things as e-mail, Internet faxing, and other data transfers. There are slight differences between SSL 3.0 and TLS 1.0, but the protocol remains substantially the same.

TPM abbr. Trusted Platform Module is an international standard for a secure cryptoprocessor.

translation cipher n. an affine cipher for which a=1.

transposition cipher n. a block cipher which acts on the plaintext by permuting the positions of the characters in the plaintext.

Triple DES n. a symmetric-key block cipher, which applies the Data Encryption Standard (DES) cipher algorithm three times to each data block.

TRNG abbr. True Random Number Generator, based on a pure source of entropy ("noise").

X.509 abbr. X.509 is a standard that defines the format of public key certificates.

Links

Introduction to Cryptography

Handbook of Applied Cryptography (HAC)

Maths is fun - injective-surjective-bijective

RSA Laboritories : WHAT IS EXHAUSTIVE KEY SEARCH?

Data Encryption Standard

OpenSSL Cookbook Ivan Ristić Free Chapters

NIST Digital Signatures

Basic Blockchain Programming - keys as property

CodeGuru: Windows Cryptography API: Next Generation (CNG)

Windows Dev Center: Cryptography

Windows Dev Center: Cryptography API: Next Generation

MSDN: .NET Framework: System.Security.Cryptography

TechTarget SearchSecurity

How to choose an AES encryption mode (CBC ECB CTR OCB CFB)?

crackstation.net

Sharepoint Excel Services for server-side Excel calculations

For many years Excel was limited to client-side, i.e. desktop installation, operation. However, I did work on many projects where we launched an instance of Excel on a server as a calculation agent despite Microsoft recommending against because this option is fraught with unforeseen consequences. For example, message boxes get thrown but on a server there is no user to see and dismiss them. Microsoft invented Sharepoint for clients who really want to run Excel on the server.

The downsides of Sharepoint are cost, it is not cheap in itself and also it requires a Windows Server licence. If your employer is pro-Unix then this will be a deal-breaker. The other downside of Sharepoint is that your VBA code will not run and will have to be converted to C# or some other managed .NET language.

Nevertheless, the Sharepoint 'market share' of the Excel solution space will grow and a blog on Excel Development should address it.

I've yet to reach recommendations for this technology so this first blog post will solely be a collection of links for more reading.

Wikipedia is always good place to start, here is article on Excel Services

Office Support: Getting Started with Excel Services and Excel Web Access

Book: Wrox - Professional Excel Services

Technet: Overview of Excel Services in SharePoint Server 2013

Technet: Administer Excel Services in SharePoint Server 2013

safaribooksonline: Chapter 1. An Introduction to Excel Services

Technet Forums: VBA won't work for workbooks rendered in a browser from a Sharepoint server (Excel Services)

Office Dev Center: Understanding Excel Services UDFs

Office Dev Center: Walkthrough: Developing a Managed-Code UDF

MSDN: Creating Custom Solutions with Excel Services

Some sample C# code for a server-side Excel Services UDF.

// From https://dev.office.com/sharepoint/docs/general-development/step-2-creating-a-managed-code-udf
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Office.Excel.Server.Udf;

namespace SampleUdf
{
    [UdfClass]
    public class Class1
    {
        [UdfMethod]
        public double MyDouble(double d)
        {
            return d * 9;
        }  

        [UdfMethod(IsVolatile = true)]
        public DateTime ReturnDateTimeToday()
        {
            return (DateTime.Today);
        }
    }
}

However, if you thought getting a UDF to run on both the client and the server in a unified code base would be a breeze then check out this web page which shows how to do it. It seems a major undertaking requiring C#, C++/CLI (formerly Managed C++), creating code to handle a managed Add-in and a custom shim. I expect many to be dissuaded from using SharePoint because of the complexity there but I also expect Microsoft to unify the client side and server interfaces in the future.

Whilst I have yet to reach recommendations on Sharepoint and Excel Services one has to wonder why rendering a workbook in a browser which requires a Windows Server, Sharepoint Server and Internet Information Server is better than keeping a workbook of a network file server (running on Linux) and opening in Excel. I guess the question boils down to client licences versus server licences.

Digital Signatures - a succinct maths formula/symbolic description

I cam across this beautifully succinct description of how digital signatures work written in maths formula/symbolic terms. Original thread is here.

1. Alice publishes her public key PK and keeps her private key K safe.
2. Alice produces an original bitstring S.
3. Alice computes S'=f(S, K), which is a fixed-length bitstring.
4. Alice publishes S+S'.
5. If Carol wants to know if Alice is in fact the author of S, all she needs to do is compute g(S, S', PK), which returns true if S is the bitstring that was signed with S' and if it was K that was used to produce the signature. Assuming that only Alice knows K, this is enough to prove Alice's authorship of S.
6. If Bob wants to impersonate Alice, stealing S' is useless because S' can only be used to authenticate S. Stealing PK is also useless because it can only be used to authenticate, not to sign. Altering S or S' doesn't work because it will cause the authentication to fail. Bob's only option is to steal or attempt to crack K.

If one were to licence software how would one do it?

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?

Unique Hardware ID / Hardware Fingerprint

If not NICs then what, NICs in a combination of other devices? This question has been tossed around on stackoverflow.com at Reliable way of generating unique hardware ID and here. One Q&A even discusses interrogation code of a hardware fingerprint. CodeProject.com gives us some sample code as to how a licencing scheme might work here, here, here and here. A C# implementation is given here.

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.

Parse JSON in Excel VBA

Advert: Come to the Foo Bar! See a whole working application based on Excel VBA, JSON, JavaScript, Angular with help of a beauty of C# COM Addin. The Foo Bar where the beers are easily replenished with Angular.

So I did quite a lot of work on StackOverflow answering my own questions about JSON parsing. I really ought to deposit some key code here on my blog as well.

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.

UPDATES and FURTHER READING

Update: Safer JSON parsing with Json.Parse (still via ScriptControl)

Object Linking and Embedding still going strong

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

ATL Notes 2 - Only Inherit from IDispatch Once

Continuing on ATL (Active Template Library) ...

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

BEGIN_COM_MAP(CAudioRecorder) 
        COM_INTERFACE_ENTRY(IAudioDevice) 
        COM_INTERFACE_ENTRY(IAudioRecorder)   <<<< ERROR 1 
        COM_INTERFACE_ENTRY(IDispatch)        <<<< ERROR 2 
END_COM_MAP()

should be changed to the following to direct QueryInterface calls for IDispatch to IAudioDevice

BEGIN_COM_MAP(CAudioRecorder) 
        COM_INTERFACE_ENTRY(IAudioDevice) 
        COM_INTERFACE_ENTRY(IAudioRecorder)   
        COM_INTERFACE_ENTRY2(IDispatch,IAudioDevice)        
END_COM_MAP()

When Is Late Binding Advantageous for VBA Developers?

What is Late Binding and when would you use it?

Syntax of Late Binding

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.

Hat tip to Cybersecurity - Printer Microdots

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.

ATL Notes 1 - Inheritance Hierarchy

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.

// CCoolCode

class ATL_NO_VTABLE CCoolCode :
 public CComObjectRootEx<CComSingleThreadModel>,
 public CComCoClass<CCoolCode, &CLSID_CoolCode>,
 public ISupportErrorInfo,
 public IDispatchImpl<ICoolCode, &IID_ICoolCode, &LIBID_ATLProject2Lib, 
                                       /*wMajor =*/ 1, /*wMinor =*/ 0>
{
public:
 CCoolCode()
 {
 }

DECLARE_REGISTRY_RESOURCEID(IDR_COOLCODE)


BEGIN_COM_MAP(CCoolCode)
 COM_INTERFACE_ENTRY(ICoolCode)
 COM_INTERFACE_ENTRY(IDispatch)
 COM_INTERFACE_ENTRY(ISupportErrorInfo)
END_COM_MAP()

// ISupportsErrorInfo
 STDMETHOD(InterfaceSupportsErrorInfo)(REFIID riid);


 DECLARE_PROTECT_FINAL_CONSTRUCT()

 HRESULT FinalConstruct()
 {
  return S_OK;
 }

 void FinalRelease()
 {
 }

public:



};

OBJECT_ENTRY_AUTO(__uuidof(CoolCode), CCoolCode)

So it's worth showing the inheritance hierarchy And we can give information as to what each class in the hierarchy does

ClassName	Purpose
CComObjectRootBase	Holds the reference count member
CComObjectRootEx	Handles reference counting based on the threading model
CComSingleThreadModel	Passed as template parameter to CComObjectRootEx. This means reference counting need not be thread safe.
CComCoClass	Implements IClassFactory with the help of a creator class.
ISupportErrorInfo	Drives rich error information familiar to VBA devs.
IDispatchImpl	If you selected a Dual interface (I recommend) then you get an implementation of IDispatch driven off the type library hence the parameters
CCoolCode	Your class and your logic but never gets directly instantiated
CComObject<CCoolCode>	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

template <class T, const CLSID* pclsid = &CLSID_NULL>
class CComCoClass
{
public:
 DECLARE_CLASSFACTORY()
 DECLARE_AGGREGATABLE(T)
 typedef T _CoClass;

        ...

 template <class Q>
 static HRESULT CreateInstance(
  _Inout_opt_ IUnknown* punkOuter,
  _COM_Outptr_ Q** pp)
 {
  return T::_CreatorClass::CreateInstance(punkOuter, __uuidof(Q), 
                  (void**) pp);
 }
 template <class Q>
 static HRESULT CreateInstance(_COM_Outptr_ Q** pp)
 {
  return T::_CreatorClass::CreateInstance(NULL, __uuidof(Q),
                  (void**) pp);
 }
};

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 ...

ClassName	Purpose
CComObject	This class implements IUnknown for a nonaggregated object.
CComAggObject	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.

Miscelaneous Links

As always surfing around in preparation of a blog post throws up some interesting links that are worth saving.
How ATL 7 uses attributes to save lines ATL 3 code
Microsoft Documentation ATL
MSDN ATL