VBA - Python Com Class with attributes

Keen readers of this blog will know from time to time I give Python COM gateway classes which are callable from VBA. Up until now I do not think I have given an example of a class that has attributes (or a constructor). Time to rectify this.

Open Visual Studio 2017 and add a new Python Applciation project and then copy and paste in the code below. Save the file, calling it whatever you want mine is called PythonExampleClassWithAttributes.py

1. class ExampleComClassWithAttributes(object):
2.  
3.     _reg_clsid_ = "{EF38F5B8-6E86-4D8E-A93E-C5B6C05CF378}"
4.     _reg_progid_ = 'PythonLib1.ExampleComClassWithAttributes'
5.     _public_methods_ = ['AMethod']
6.     _public_attrs_ = ['Foo','ws','ReadOnlyProp']
7.     _readonly_attrs_ = ['ReadOnlyProp']
8.  
9.     def __init__(self):
10.         self.ReadOnlyProp = "Not writable!"
11.  
12.     def AMethod(self):
13.         return "AMethod returns"
14.  
15. def TestExampleComClassWithAttributes():
16.     # This test routine gives each feature a spin 
17.     # (except ws for which see VBA client example)
18.     test = ExampleComClassWithAttributes()
19.     test.Foo = 54
20.     print(test.Foo)
21.     print(test.AMethod())
22.     print(test.ReadOnlyProp)
23.  
24. def RegisterThis():
25.     print("Registering COM servers...")
26.     import win32com.server.register
27.     win32com.server.register.UseCommandLine(ExampleComClassWithAttributes)
28.  
29. if __name__ == '__main__':
30.     RegisterThis()
31.     TestExampleComClassWithAttributes()

Running the above code from Visual Studio 2017 should give a command window output of the following...

Registering COM servers...
Registered: PythonLib1.ExampleComClassWithAttributes
54
AMethod returns
Not writable!
Press any key to continue . . .

_public_attrs_ and _readonly_attrs_

the new features in this code are the two class level attributes of _public_attrs_ and _readonly_attrs_. _public_attrs_ is an array of attributes that are to be exposed by Python to COM. That's all you need to get attributes up and running; very economical with lines of code!

You may be wondering where are the property procedures? And also wondering that without them how do you make properties read only? To make properties read only you add them to the _readonly_attrs_ array. In the above code the property named "ReadOnlyProp" is read-only, attempting to write to that property will error.

def __init__(self)

I don't think I have given a Python class constructor example on this blog before either. In the above code I give one, it is the block of code headed def __init__(self) on lines 9-10. Because I made "ReadOnlyProp" read-only I somehow need to set the value, in the code above I set it in the constructor.

Client VBA Code

So here is the client VBA code which can be pasted into a standard module.

modExampleComClassWithAttrs Standard Module

1. Option Explicit
2.  
3. Sub VBATestExampleComClassWithAttributes()
4.     Dim objExampleWithAttrs As Object
5.     Set objExampleWithAttrs = VBA.CreateObject("PythonLib1.ExampleComClassWithAttributes")
6.     Debug.Print objExampleWithAttrs.AMethod
7.  
8.     Set objExampleWithAttrs.ws = ThisWorkbook.Worksheets.Item(1)
9.     Debug.Print objExampleWithAttrs.ws.name
10.     objExampleWithAttrs.Foo = 54
11.     Debug.Print objExampleWithAttrs.Foo
12.     Debug.Print objExampleWithAttrs.ReadOnlyProp
13.  
14. End Sub

So one feature above not found in the Python test code is that I am setting one attribute 'ws' to be a object reference to a worksheet and so I need the Set keyword, notice how in the Python code I do not need to give this. Python is less fussy it seems, but with it the responsibility for the programmer to take care and test their code.

Final Thoughts

I have to say I feel quite liberated to be able to define a class that can carry state, i.e. has attributes, without having to add a class module for each class to the VBA project. In my university computer sciences courses I was firmly taught object orientation (OO). And to do good OO can require many classes, some of them potentially quite small. The Python files can hold as many classes as you need. No need to fill your VBA projects up with mini-classes.

VBA - Redis - COM Interop - Use VB.NET to call Redis from VBA

Here I'm going to introduce Redis which is an open-source in-memory key-value store. Redis is a often used as a database cache to make cloud based applications more scalable by relieving the load on a relational database. I will give VB.NET code that calls into the TCP interface of Redis and then I'll give a COM component equivalent that it is callable from VBA.

Click here for separate Youtube window

But What is Redis?

The best place to start is its Wikipedia entry which I suggest you read. The name Redis means REmote DIctionary Server.

Worth stressing, is that Redis runs in its own process and so is shared across a whole machine. That is to say, a single instance is accessible by all processes on that machine. Moreover, network programming allows remote access from a different machine. That Redis runs in its own process means it's data durability will outlast any Excel.exe sessions they may have closed. This can help VBA programmers develop applications with greater data resiliency.

Redis in the Cloud

Redis is offered as part of a portfolio of components for developers of clouds based solutions; it is available on Amazon Web Services, Microsoft Azure and Heroku.

Redis was voted most loved database in the Stack Overflow Developer Survey in 2017 and 2018.

How to Install Redis On Windows 10

Redis running on Ubuntu running on Windows 10 Subsystem for Linux

I have upgraded Windows 10 with the Anniversary update and have been enjoying the Windows Subsystem for Linux allowing me to run an instance of Ubuntu. So, I downloaded the tarball from the Redis Home Page, unpacked the source code therein with the unix tools of the command line and began building with make tools. That was fun, it chained a whole load of downloads and subsequent makes but I got a working version in the end. So I am using an instance of Redis running on Ubuntu running on Windows 10 Subsystem for Linux.

On my Ubuntu install I go to directory

/mnt/c/users/simon/downloads/redis/redis-5.0.3

and then type

$src/redis-server

but you may well have chosen to install to a different location. The gotcha here is that I have to run the executable from the parent directory (for what reason I do not know).

Windows native Redis install

For a native Windows Install there is a Stackoverflow Q&A for this which has been viewed very many times, How do I run Redis on Windows?. There is an answer suggesting that a division of Microsoft, MS Open Tech has some MSI install files for Redis here at their github page. I've not tried these but I presume Microsoft builds work.

Redis Clients

A full list of Redis clients is here.

Use redis-cli to get some initial data

I will give some code later but I want to get some initial data into my Redis instance and I am going to run another instance of Ubuntu to run the unix command line interface client, redis-cli.

KEYS

KEYS <pattern> will retrieve a list of keys which match the pattern, asterisk is a wildcard. Once your Redis store is quite full, KEYS is ill advised because it is slow running but for us right now we can use this.

SET

We can set an expiry for SET but I won't to keep things simple. I will set foo=BAR with the following

SET foo BAR

GET

We some data set (see above) I can read the data with

GET foo

which should return

"BAR"

and KEYS should now have an item to return

KEYS *

returns

1) "foo"

So now we have foo=BAR as a test datum we can progress to programatically access Redis via it's interface which is not HTTP but TCP, as we will see.

How to Communicate with Redis

The next question is how to connect and communicate with Redis via code. It would appear that the bare bones Redis installation does not have not an HTTP interface (though an ecosystem of add-ons do offer that option). The default method of calling Redis is detailed on this page, Redis Protocol specification. There is a serialization protocol called RESP (not to be confused with REST!) which details the sequence and formats of bytes of messages and message responses. More on RESP later.

In the section headed Networking layer this key passage reads

A client connects to a Redis server creating a TCP connection to the port 6379. While RESP is technically non-TCP specific, in the context of Redis the protocol is only used with TCP connections (or equivalent stream oriented connections like Unix sockets).

So, there is no HTTP interface but there is a TCP/IP sockets interface. As VBA developers, we have some code to write.

The vRedis VB.NET client

So I downloaded the VB.NET Redis client known as vRedis which is beautifully factorized into interfaces and implementations but which I nevertheless compressed into a simple console program to show the key elements ...

VB.NET console application code to call Redis

So the following is code for a VB.Net console application which shows the minimum amount of code to connect and call Redis' TCP interface.

Imports System.Net.Sockets
Imports System.Text

Module Module1

    Sub Main()
        Dim stream As NetworkStream
        Dim client As TcpClient
        Dim bytes() As Byte
        Dim result As String
        Try
            client = New TcpClient()
            client.Connect("127.0.0.1", 6379)
            stream = client.GetStream()

            bytes = Encoding.UTF8.GetBytes("GET foo" & vbCrLf)
            stream.Write(bytes, 0, bytes.Length)
            stream.Flush()
            ReDim bytes(client.ReceiveBufferSize)
            stream.Read(bytes, 0, bytes.Length)
            result = Encoding.UTF8.GetString(bytes)
            result = Left(result, InStrRev(result, vbCrLf))

            Console.WriteLine("Result:" + result)
        Catch ex As Exception
            Console.WriteLine("Error:" + ex.ToString())
        End Try

    End Sub

End Module

So, quite simple really with .NET, you need only two special classes, NetworkStream and TcpClient. We assume that Redis is running locally hence the 127.0.0.1 IP Address and 6379 is the default port number.

Quickest Way For VBA Developers is to ship a COM interface to the .NET code

So the situation for VBA developers is not so rosy. The quickest way for VBA developers to call Redis is to take the above .NET code and house in a COM Dll (Assembly) and implement a COM interface so that it is callable from VBA. There is plenty of C# COM Dlls on this blog but today for a first I give a VB.NET COM Dll Interop Assembly

1. Open Visual Studio 2017 with administrator rights (admin required to register COM dll)
2. In the Add New Project select Visual Basic
3. Select the Windows Desktop node on the left hand side, on the right hand side select Class Library
4. Call the Project Name RedisCOMClient
5. Select Add New Item from the Project menu. The Add New Item dialog box is displayed.
6. Select COM Class from the Templates list, and then click Add. Visual Basic adds a new class and configures the new project for COM interop.
7. Rename the ComClass1.vb file to be RedisCOMClientClass.vb and copy in the code following below (it looks similar to that above but has all the COM housing)
8. In the Project properties for RedisCOMClient on the Compile tab, ensure 'Register for COM interop' is checked.
9. Build the RedisCOMClient Project

Imports System.Net.Sockets
Imports System.Text


Public Class RedisCOMClientClass

#Region "COM GUIDs"
    ' These  GUIDs provide the COM identity for this class 
    ' and its COM interfaces. If you change them, existing 
    ' clients will no longer be able to access the class.
    Public Const ClassId As String = "c7be1643-365e-449a-8514-a5e39ea25fe1"
    Public Const InterfaceId As String = "ed0154b7-e19a-405f-9732-2d0a7e57a4da"
    Public Const EventsId As String = "70aa4927-406d-4a0e-a403-9acb708573d8"
#End Region

    ' A creatable COM class must have a Public Sub New() 
    ' with no parameters, otherwise, the class will not be 
    ' registered in the COM registry and cannot be created 
    ' via CreateObject.
    Public Sub New()
        MyBase.New()
    End Sub

    Public Function SendAndReadReponse(ByVal sCommand As String) As String
        Dim stream As NetworkStream
        Dim client As TcpClient
        Dim bytes() As Byte
        Dim result As String
        Try
            client = New TcpClient()
            client.Connect("127.0.0.1", 6379)
            stream = client.GetStream()

            bytes = Encoding.UTF8.GetBytes(sCommand)
            stream.Write(bytes, 0, bytes.Length)
            stream.Flush()
            ReDim bytes(client.ReceiveBufferSize)
            stream.Read(bytes, 0, bytes.Length)
            result = Encoding.UTF8.GetString(bytes)
            SendAndReadReponse = Left(result, InStrRev(result, vbCrLf))

        Catch ex As Exception
            '* TODO consider wrapping error
            '* In the meantime just throw to caller
            Throw New Exception("Trapped error: " + ex.Message)
        End Try

    End Function
End Class

And some VBA client code ...

Sub test()

    '* Early binding requires Tools->Referecnces to RedisCOMClient.Dll
    'Dim obj As RedisCOMClient.RedisCOMClientClass
    'Set obj New RedisCOMClient.RedisCOMClientClass

    '* Late binding
    Dim obj As Object
    Set obj = VBA.CreateObject("RedisCOMClient.RedisCOMClientClass")
    
    Debug.Print obj.SendAndReadReponse("GET foo" & vbCrLf)

    Debug.Print obj.SendAndReadReponse("SET baz barry" & vbCrLf)
    Debug.Print obj.SendAndReadReponse("GET baz" & vbCrLf)

    Debug.Print obj.SendAndReadReponse("KEYS *" & vbCrLf)

End Sub

VBA - C# - Bang Syntax Part 3 - C# Interop JSON with Bang Syntax

So it's not just Script Control based JSON parsers that can benefit from the bang ! syntax, we can use C# and COM interop to export Newtonsoft's very popular .NET JSON parser for use in VBA. When defining the interop interface if we add DispId(0) then enable the bang ! operator which calls the default method to get compact syntax. Here is client VBA...

Sub Test()

    Dim sJSON As String
    sJSON = VBA.Replace("{ 'name':'John', 'age':30, 'cars':{ 'car1':'Ford','car2':'BMW','car3':'Fiat'} }", "'", """")

    Dim oCJSONParser As CJSONParser
    Set oCJSONParser = New CJSONParser
    
    Dim oRoot As CJSONToken
    Set oRoot = oCJSONParser.ParseJSONString(sJSON)

    Dim oCars As CJSONToken
    Set oCars = oRoot!cars                  '* equivalent of 'Set oCars = oItem.GetToken("cars")
    
    Dim oCar2
    oCar2 = oCars!car2                      '* equivalent of 'Set oCar2 = oCars.GetToken("car2")
    
    '* or chain syntax
    oCar2 = oRoot!cars!car2
    
    Stop
End Sub

And here is the C# code for a .NET library assembly (i.e. a DLL) with Register for Interop checkbox checked and ComVisible(true) in AssemblyInfo.cs

using Newtonsoft.Json.Linq;   //Nuget Newtonsoft.Json.11.0.2
using System.Runtime.InteropServices;


namespace Foo
{

    public interface IJSONParser
    {
        IJSONToken ParseJSONString(string sJSON);
    }

    [ClassInterface(ClassInterfaceType.None)]
    [ComDefaultInterface(typeof(IJSONParser))]
    public class CJSONParser : IJSONParser
    {
        private JToken m_oJObject;

        IJSONToken IJSONParser.ParseJSONString(string sJSON)
        {
            m_oJObject = JToken.Parse(sJSON);
            CJSONToken oToken = new CJSONToken(m_oJObject);
            return oToken;
        }
    }

    public interface IJSONToken
    {
        [DispId(0)]
        object GetToken(string sKey);
    }

    [ClassInterface(ClassInterfaceType.None)]
    [ComDefaultInterface(typeof(IJSONToken))]
    public class CJSONToken : IJSONToken
    {
        private JToken m_oToken;

        public CJSONToken(JToken token)
        {
            m_oToken = token;
        }

        object IJSONToken.GetToken(string sKey)
        {
            JToken oToken = m_oToken[sKey];

            if (oToken is JValue)
            {
                JValue jv = (JValue)oToken;
                return jv.Value;
            }
            else
            {
                CJSONToken token = new CJSONToken(oToken);
                return token;
            }
        }
    }
}

C# - VBA - COM - For image processing ditch WinAPI/GDI and use .NET instead

So I had cause to load an image file and query the colour of a pixel, so I needed some image processing code. I had encountered some code before but did not capture it for this blog. Investigating today I was depressed by the obscure way the Windows API worked in this regard, then to find that the Windows API declaration would need to change for 64-bit versions of VBA I rebelled. Instead, I reached for the .NET image classes and chose to export them from a C# class library using .NET/COM interop. Life is much simpler now.

Below is some code that calls into the C# class library, the source for which can be found on this counterpart blog post. Together these programs allow a picture to written to Excel cells like this...

Option Explicit

Sub Test()
    
    DumpPictureToCells "N:\stackoverflowicon.png", False
    'DumpPictureToCells "N:\number.png", True
    
End Sub


Sub DumpPictureToCells(ByVal sFileName As String, ByVal bIsAlphaMask As Boolean)
    Dim oBitMap As ImageToByteArray.BitMap
    Set oBitMap = New ImageToByteArray.BitMap
    
    Dim bSU As Boolean
    bSU = Application.ScreenUpdating
    Application.ScreenUpdating = False
    
    
    Sheet1.Cells.Clear
    
 
    oBitMap.LoadImage sFileName

    Dim x As Long, y As Long
    For x = 0 To oBitMap.Width - 1
        For y = 0 To oBitMap.Height - 1
            Dim col As ImageToByteArray.Colour
            Set col = oBitMap.GetPixel(x, y)
            
            Dim rng As Excel.Range
            Set rng = Sheet1.Cells(y + 1, x + 1)
            
            If bIsAlphaMask Then
                rng.Interior.Color = RGB(256 - col.A, 256 - col.A, 256 - col.A)
            Else
                rng.Interior.Color = RGB(col.R, col.G, col.B)
            End If
        Next
    Next

    Application.ScreenUpdating = bSU
End Sub

Private Function ResizeCellsToBeSquare()
    
    Dim sngColWidth
    sngColWidth = 2.14 '* based on experimentation
    
    Dim lColLoop As Long
    For lColLoop = 1 To Sheet1.UsedRange.Columns.Count
        Dim rngCell As Excel.Range
        Set rngCell = Sheet1.Cells(1, lColLoop)
        
        rngCell.EntireColumn.ColumnWidth = sngColWidth
        
    Next

End Function

P.S. During the WinAPi investigation I came across this excellent website mvps.org