Check range for specific characters
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.
What an ordeal it all was for 3 reasons.
- 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.
- 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.
- 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, _ .ListColumns(8).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 Else 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 :)" Else 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 <pre>
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.