Check range for specific characters

If you like this post please share it!

If you are working with output from other systems (or preparing input for other systems) you might need to find certain characters that cause problems.  In my case I was creating XML code for a custom ribbon from Excel formulas and XML has a few disallowed characters.  So I wrote some handy code.

Briefly my project was writing XML code for my own custom ribbon group to handle my assortment of tools I use in my daily work.  I learned from the Ribbon page from MVP Ron du Bruin’s site which time and time again I find has such good solid. practical approaches to problems.  So I decided that the XML code was best created from an Excel worksheet with formulas feeding off a user friendly Table showing the Start and End rows of Groups and Menu Dropdowns as well as the macro buttons along with the labels and icons wanted.  The ‘code’ but really Excel formula output, could then be easily copied as text to the XML file using the  Custom UI Editor.   To the right of the Table below was a column of all the XML format output needed where a fairly complex CHOOSE formula creates the code depending if it is Group, dropdown menu or button related. XML Making Table

What an ordeal it all was for 3 reasons.

  1.  NEVER, and I mean NEVER have the two applications (i.e. Custom UI Editor and Excel) open at the same time because if you do you find changes you made in Excel and absolutely 100% saved get lost.  I thought I was going nuts opening up my Add-In and finding my saved changes gone from the latest version I know I saved in.  
  2. Those images (imageMso) for the macro buttons or menu dropdowns are a real ‘pain in the ass’ to use no other way to say it.  In 2018 you are scribbling down long case sensitive image names from galleries – you can’t easily get a list from somewhere to copy and paste from. 
  3. XML doesn’t allow certain characters I found out the hard way.

I could also bang on about how easy and efficient it was to customize the Menu in Excel 2003 for my own small business and clients.  All could be done simply enough within VBA and it was so efficient to do and maintain especially from John Walchenbach’s wonderful Menu Maker tool now gone from his site.  Why would Microsoft not cater for Ribbon Customization from within the Excel Application?  Instead users have to struggle with this cobbled together approach using XML, 2007 Office Icons and various XML editors.  Anyway, it’s been 11 years now since the ribbon and therefore ribbon customization hassles came in and I know it’s not cool to keep moaning about it.  Now that the job is done I am pleased with it.  Perhaps the final result I should blog on in the future.

So  back to the code that is this blog’s topic.  As above XML disallows certain characters.  From Stack Overload it seems to be these: &  < >  .  My use of “&”  mainly was causing me problems.

So I got the idea for this code I think from an Allan Wyatt newsletter where his code was cleaning out any characters not in a normal set.  So I wanted the macro to look at a range and look for these disallowed characters that would cause the ribbon customisation to fail and report the number and which characters had been found.  To be honest it’s overkill to have such a tool but it was fun to do and might suit other situations and I learned how to create a range variable for multiple Table columns.

Sub FindDisallowedXMLChrs()
 Const NOTALLOWED = "&<>"
 Dim c As Range
 Dim rTest As Range
 Dim rngCell As Range
 Dim lIssueCount As Long
 Dim sIssuesFound As String
 Dim intChar As Integer
 Dim strCheckChar As String</pre>
With Sheet2.ListObjects("tblxmlsetup")
Set rTest = Union(.ListColumns(3).DataBodyRange, _
.ListColumns(4).DataBodyRange, _
.ListColumns(5).DataBodyRange, _
.ListColumns(6).DataBodyRange, _
.ListColumns(7).DataBodyRange, _
End With

For Each rngCell In rTest
For intChar = 1 To Len(rngCell.Value)
strCheckChar = Mid(rngCell.Value, intChar, 1)
If InStr(NOTALLOWED, strCheckChar) Then
lIssueCount = lIssueCount + 1
If lIssueCount = 1 Then
sIssuesFound = strCheckChar
sIssuesFound = sIssuesFound & " " & strCheckChar
End If
End If
Next intChar
Next rngCell
If lIssueCount = 0 Then
MsgBox "This test found No disallowed XML characted in the range :)"
MsgBox "This test found " & lIssueCount & " disallowed XML characters in the Table Range " & Sheet2.ListObjects("tblxmlsetup").Name & " " & rTest.Address & " as follows:" & vbNewLine & sIssuesFound
End If
Set rTest = Nothing
End Sub

I didn’t worry about the speed related code you should use if the range is going to be large like turning off automatic calculation as well as DisplayAlerts, ScreenUpdating and EnableEvents. The Constant declaration defining the disallows characters is an approach I often use in VBA as it allows easy maintenance and self documents. The other interesting aspect to this routine was the need to search in 6 columns of my table. Strangely the Excel developers haven’t given us a method to define this more easily – I used the Union method to create rTest which is 6 of the Table’s columns with the dynamic number of rows there.

XML disallowed characters results

Tagged with: , , , , ,