VBA code to check range for specific characters

Update 11/08/21

Well two interesting recent developments have occurred regarding this post on VBA code to check range for specific characters.

  1. Microsoft MVP Rick Rothstein kindly contacted me about much more elegant code than I have below, trying to assign 6 table columns to a range variable so the macro would operate on selected table columns (not all). So I have just the relevent part below:

    [vb]
    Set rTest = Union(.ListColumns(3).DataBodyRange, _
    .ListColumns(4).DataBodyRange, _
    .ListColumns(5).DataBodyRange, _
    .ListColumns(6).DataBodyRange, _
    .ListColumns(7).DataBodyRange, _
    .ListColumns(8).DataBodyRange)
    [/vb]

    Replace with Rick’s code…

    [vb]
    Set rTest = .DataBodyRange.Offset(, 2).Resize(, 6)
    [/vb]

    Great stuff, thank you Rick!  Rick has volunteered his time on numerous prominent blogs for perhaps 20 years+ and I know I am not alone in being grateful for what I have learned from him.

  2. I received an email from Clem who has written an awesome Add-In called Ribbon Icons which looks like below, to address the painful process I whinge about below for choosing and adding icons to your custom ribbon.  I just can’t believe someone has nailed the solution to this horrible process
    a) you can search for an icon with a keyword
    b) you can add icons you like from your browsing c) you can get the sometimes lengthy case sensitive imageMso name which you couldn’t cut and past from the dialog box.  Clem has generously made this Add-In free at Github follow this link .
    This Add-In is a joy to use and its hard to think of better of solution to this painful step in the process of customizing the ribbon.  Thank you Clem!

 

 

 

 

Original Post Mar 15 2018

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 de 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

Custom Ribbon 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  which 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 these 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 customization 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.

[vb]
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
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
[/vb]

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