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