VBA XML Helper Class

VBA Xml Helper class for creating XML files. MSXML is used and late bound since availiable versions may be different on different machines. Most systems should have this component available from the operating system or Office installation. MSXML 6.0 is the latest version of MSXML available from Microsoft.

The class supports simple CRUD XML operations. XML can be created from class objects, loaded from a string fragment or loaded from a file on disk. Elements are automatically wrapped in CDATA based on values written.

    Xml Created from Helper Objects :
    Dim cX As clsXmlHelper

    Set cX = New clsXmlHelper
    cX.AttributeValue("sex", _
        cX.AddElement("Pebbles", _
            cX.AddElement("Wilma", _
                cX.AddElement("Rubble") _
            ) _
        ) _
    ) = "Female"

Results:

    Xml Loaded from disk and updated :
    Dim cX As clsXmlHelper

    Set cX = New clsXmlHelper
    cX.LoadFromFile "c:$wipXmlHelper.xml"
    If cX.IsValidXml Then
        If Not cX.SelectSingleNode("/CATALOG/CD[PRICE=9.90]/PRICE") Is Nothing Then
            cX.ElementValue = "22.99"
        End If
        If Not cX.SelectSingleNode("/CATALOG/CD[PRICE=22.99]") Is Nothing Then
            cX.AttributeValue("ONSALE") = "True"
        End If
        cX.SaveXml "c:$wipXmlHelperOut.xml"
    End If

Results:

<CATALOG>
	<CD>
		<TITLE>Empire Burlesque</TITLE>
		<ARTIST>Bob Dylan</ARTIST>
		<COUNTRY>USA</COUNTRY>
		<COMPANY>Columbia</COMPANY>
		<PRICE>10.90</PRICE>
		<YEAR>1985</YEAR>
	</CD>
	<CD ONSALE="True">
		<TITLE>Hide your heart</TITLE>
		<ARTIST>Bonnie Tyler</ARTIST>
		<COUNTRY>UK</COUNTRY>
		<COMPANY>CBS Records</COMPANY>
		<PRICE>22.99</PRICE>
		<YEAR>1988</YEAR>
	</CD>
</CATALOG>

VBA Userform Calendar Control

Recently I needed a date picker for a user form.

The MonthView and DateTimePicker controls are part of a group of ActiveX controls from Microsoft found in MSCOMCT2.OCX.

To use the control I would have to worry about distribution, installation and registration. Ain’t nobody got time for that, so I rolled out my own class to create a calendar in a user form’s frame.

Instantiating the control is straightforward :
Set m_cCalendar = New clsCalendar
m_cCalendar.Initialize Me.fraCalendar

The calendar’s appearance can be customized (day back and fore color, title fore color, etc.)
The class hosts a single event, fired when a day is double clicked : DateSelected

I used a class from my previous post to handle double click, mouse up and mouse down events dynamically.

Userform Calendar Control
Userform Calendar Control

Adding Event Handlers To Dynamically Added UserForm Controls

It’s fairly easy to add any type of MSForm control to a UserForm at runtime.

e.g :
Set ctl = Me.Controls.Add(“Forms.TextBox.1”, “TextBox1”, True)

Here I am adding a Text Box control to a UserForm, naming it “TextBox1”, and making it visible. In Classic VB control arrays make it easier to create and reference dynamically added controls, whereas VBA does not implement arrays of controls.

Lack of control arrays makes it even more difficult to handle dynamically added control events. Attached is an example xlsm with a class for attaching userform control event handlers. I added most of the common userform controls, and implemented default events. Extending this class to implement other controls/events should be fairly straightforward.

Dynamic Events