VBA Serialize Module to JSON

Question

I have a VBA project that I am converting to C#. I need to be able to run unit tests to validate the converted C# code. All of the original application's data is stored in a single module in global variables and it would be very useful to serialize this module so that I can harvest subroutine inputs and outputs from it in my unit tests.

I've read other questions and found this github project:

https://github.com/VBA-tools/VBA-JSON

Unfortunately you must pass an object into its "ConvertToJson" function and modules do not have a reference I can pass in.

Does this library have a way to serialize modules rather than class modules?

Is there a way to create an object reference from a module?

Thanks!

score 0 · Answer 1 · answered Oct 21 '21 at 15:10

0

You could try converting your module to a class with a PredeclaredId. No need to new an object, just prefix method calls with the class name. To avoid the jiggery pokery of exporting the module, setting the predeclaredId to true and reimporting you could install the free and fantastic Rubberduck AddIn for VBA.

answered Oct 21 '21 at 15:10

freeflow

4,129
3
10
18

You've got me hopeful but my VBA knowledge isn't sufficient to understand what you're suggesting besides installing Rubberduck. Can you elaborate on your answer? – Eric Oct 21 '21 at 15:50
@Eric I'm not really familiar with this Rubberduck method, but a seemingly easy to follow guide to using it with a PredeclaredId can be found here: https://stackoverflow.com/questions/58600560/how-to-set-the-attribute-vb-predeclaredid-using-rubberduck-predeclaredid – Slaqr Oct 21 '21 at 21:26
Well, that's a pretty snarky comment. VBA has a lot of very specific information to it that doesn't apply to any other language. A person can be very good with C# and not know the specific ins and outs of VBA. As far as posting code goes, what code would be helpful? I think my question is very clear and simple without code. How do you serialize a module, which, by the way, you have not answered. – Eric Oct 24 '21 at 03:59
Were you able to follow how to convert a Module to a Class? If the vagaries of VBA are causing problems there are a number approaches that can be used, and I apologise if I'm teaching granny to suck eggs. The first is that in the VBA IDE placing the cursor on a Keyword and pressing F1 will bring up the MS help page for that keywork. This works for VBA and the office object models. Pressing F2 will bring up the object browser so that you can more easily see the methods and property offered by an object. Do remember to toggle 'show hidden members' – freeflow Oct 24 '21 at 11:08
Converting a standard module to a class module will not work for me because I would have to change two or three million references to the standard module. I have figured out how to serialize a standard module and will post that solution shortly. – Eric Oct 26 '21 at 13:46

score 0 · Accepted Answer · answered Oct 26 '21 at 13:54

There is not a graceful way to serialize a VBA standard module but I have found a way to do it using the extensibility library. (similar to reflection). Please excuse the preliminary state of the attached code; it does not write to a serialized format yet, it simply retrieves all doubles in a standard module and prints their values as long as the module only contains variable declarations. While this isn't a full or robust serialization code it has the parts that are difficult to do with a standard module. Once I've written a more complete routine I'll update.

Public Sub Serialize()

    Dim code As String
    Dim splitCode As Variant
    Dim variableName As String
    Dim i As Integer
    Dim count As Integer
    Dim Project As VBProject
    Dim module As CodeModule
    Dim value As Variant
    Dim nextClass As VBComponent
                
    Set Project = ThisWorkbook.VBProject
    Set module = Project.VBComponents("TheModule").CodeModule

    i = 1
    Do While i <= module.CountOfLines
    
        'Get the next line of the Module of interest
        code = module.Lines(i, 1)
    
        'Look for any variable types we care about
        If InStr(code, "Double") <> 0 Then
        
            splitCode = Split(code)
            variableName = splitCode(1)
        
            Set nextClass = Project.VBComponents.Add(vbext_ct_StdModule)
            nextClass.Name = "aaaTMP"
        
            'Inject the Function we need
            nextClass.CodeModule.InsertLines 1, "Public Function GetValue() As Variant"
            nextClass.CodeModule.InsertLines 2, "GetValue = " & variableName
            nextClass.CodeModule.InsertLines 3, "End Function"
        
            value = Application.Run("GetValue")
        
            MsgBox variableName & ": " & value
            Project.VBComponents.Remove nextClass
         
        End If
    
        i = i + 1
    Loop

End Sub

VBA Serialize Module to JSON

2 Answers2