College of Engineering


Excel Ramblings*


Prof. Lee Townsend


Using Dependent Form Control Combo Boxes and Validation Cells
with Named Ranges for Cross Platform Excel Development:

Using Dependent Form Control Combo Boxes and Validation Cells

Creating procedure call flow information in VBA

Creating a mind map of procedure calls in VBA

Creating an organization chart of procedure calls in VBA using SmartArt

Combining the mind map and organization chart capabiliites into a single code

Latest flow chart code with mind maps and organization charts (7/31/14)

How to make a combo box on a form have restricted data entry similar to that of a list validation cell

How to generate a filtered Table to be used as a Pivot Table source file

A Flow Chart Update - Listing all trees at once


Other Cross Platform Issues:

More PC and Mac difference - colorindex and zoom

Optional printing to both PC and Mac OS defaults or PDF



Some of my favorite Excel web sites

Put your rosters in Excel

Make your own graph paper using Excel

Use Excel 2010/2011 VBA to perform FFTs and IFFTs on the Mac andf PC

The CETA Scheduler User Guide as of March 22, 2014

How to quickly get a folder listing on a Mac

Make an Excel file of the list of your iOS apps


Return To Top

Using Dependent Form Control Combo Boxes and Validation Cells with Named Ranges for Cross Platform Excel Development

April 5, 2014


I am developing cross platform Excel code to be used by fellow faculty, chairs, and deans. I found that Validation Cells did not offer a good user experience on the PC so found a solution as described below. Following are screen shots of the two worksheets I use in the example workbook.

On the left is the main page. Cells A1:C3 are the results of making choices with the Form Control Combo Boxes, also known as DropDown boxes. Hidden under the Combo Boxes are the associated Data Validation cells. The selections in the bottom Combo Box depends on the selection made in the middle Combo Box. The sentence in row 13 is formed by using the Data Validation cell informtion.

On the Lists page is where all the named ranges are stored.

The Details

Debra Dalgleish, author of the amazing Excel tutorial site,, gave me the idea of using Combo Boxes along with Data Validation cells in my Excel programs in her Excel Tips, However, due to my cross-platform constraint, I could not use her method as presented. Instead of using ActiveX controls, which are not implemented on the Mac, I used the Form Control Combo Boxes, usable on both platforms. I develop Excel codes in Excel 2011 on the Mac but most users of my codes use Excel 2010 on a PC. On the Mac, Data Validation cells offer an easy to use interface. However, on the PC, they only show eight rows at a time and the font is quite small. DropDown boxes still have somewhat small text but no row constraint. Screen shots of both are shown in the next image. The Mac screen showing the use of Data Validation cells is on the left and the PC screen showing the use of the Combo boxes is on the right.

The Excel 2010 example was generated using Excel 2010 inside of CrossOver on my Mac.

In addition, Debra's site shows how to have dependent Data Validation cells by using named ranges and the INDIRECT function I implemented the same concept in the DropDown boxes approach.

Download Source Files

The code is available as an Excel xlsm file. For those of you who prefer to not download files containing macros, the only macro used is available as a .bas file and the workbook is available as an .xlsx file, i.e. no macros.


Return To Top___________________________________________________________________________________________________________________

Creating a mind map and a SmartArt organization chart of procedure calls in VBA

Part I June 27, 2012

In the process creating a somewhat complicated Excel VBA workbook I found I needed a code that would give me a procedure flow chart, i.e. what procedure calls or references what procedure. With alot of help from a couple of sites (URLs are in the code itself), I put together the following code (global declarations and code module). Note that the code does not handle user-defined classes as I do not use them.

To use the code, open the VBA editor in Excel. Import both bas files into an Excel VBA project then run the first subroutine, genProcedureFlowChart_Main() at about line 14 of the module. If the sheet MacroFlowChart does not exist, the code creates it. From then on as you develop your code further, you can run this code to keep straight who's calling whom. If you want to see it work, download my test file with lots of code and modules in it. BBEdit and TextWrangler (Mac text editors) will show line number if you copy and past module contents into it. Note that the information is now in a format that can be used in mind-mapping software or XML databases.

I am honored that Debra Dalgleish presented my code in her Contextures Blog.

Note - you are free to use my codes. You are not free to sell any of it. Most of it is derived from code found at,, and Please feel free to contact me with questions or errata.

Return To Top

Part II August 9, 2012

The end of the above project is to take the list of calls and turn them into a mind map. Then you can follow the procedure flow logic visuallly. I made a small example that shows you what this part looks like. It is not yet connnected to the above code as I need an iterative solution instead of's recursion method of producing the tree. In my larger testbed, the recursive solution ran out of stack space. I have, however, included classes as well as programatically created charts and forms.

The overall flow of this part of the code development looks like

Tree to mindmap

The orange area contains the list of procedures and which procedures they call. The code below takes that list and creates a mind map file that can be read my almost all mind mapping programs available. I use Freeplane. Its associated schema is on the Freeplane website.

Before generating the tree, my worksheet looks like the following.
Before tree is Created

After the code is run, the tree is filled in and the mind map file created. The code in the mind map file is to the right of the tree. The map itself is created in the same folder as your workbook with the same name but has the .mm extension.
Full tree with mind map code

The code to generate the above result is given in two files to be imported into the VBA editor: sheetSetUp.bas and Tree2MM.bas. The code in the first module generates a new sheet in a workbook, then sets up the pre-tree worksheet. Run the first procedure in sheetSetUp.bas: createFormattedSheet. That routine will ask you to do a SaveAs on the workbook as the name of the workbook becomes the name of the mind map file. Once that setup code has been run, the VBA editor hides itself. Then you can make the tree and associated mind map file by clicking the Make the Tree button which runs the code in the other module. Thanks to for providing the code that recursively generates the tree from the list of calls.

The above method avoids your downloading code with macros in them. As far as I know, my testbed file, ToUHSite.xlsm, has no nasties in it so feel free to download it if you don't want to go through the hassle of setting up your own page.

I occasionally update my flowchart code as I try to use it in different codes I am developing. Here is the latest version (01/26/13).
makeMMTree.bas, genProcedureFlowChart.bas, Globals4FlowChart.bas

Newer versions that include org charts are available below. See the "Flowcharts from the CETA Scheduler June 8, 2014" section.

Return To Top

Part III May 28, 2014

I am still trying to document a fairly complicated code and found the mind maps created above are interesting but not terribly useful for tracking procedure calls while undergoing code development. It required too much distraction for me to load up another application and get its file. I prefer to just stay in Excel.

In my documentation code, I will create a version that, when you click a button, instead of executing the code, the code in the documentation workbook will create an organization chart of the code calling sequence for you in Excel itself. I found very little documentation on the web about how to create organization charts in Excel VBA using SmartArt. The two codes I did find were on the following web sites. The second reference I also found on many other sites. The first uses non-recursive code and the second, recursion.

Thanks to the examples from those sites, I was able to find the code needed to include organizationn charts in my previous tree code based on, replacing the code to generate the mind map with that of creating the organization chart. The results follow. In my example files I chose to create a family tree as it would be easier to recognize names than procedure calls. Note that the chart is single parent as I am really using it to create a visual graphic of code documentation.

Before generating the tree and organization chart, my cleared worksheet looks like the following.

When I press the Make the Tree button, the code fills in the empty cells.

Beneath this chart is the organization chart. Originally it was SmartArt but I found it more convenient to convert it to a picture as I did not need the interactive aspects of SmartArt, just an organization chart, here presented as a form of family tree.

The choice of which type of chart to create is given in the main program by the following VBA code.

Set oSALayout = Application.SmartArtLayouts(88). See addendum below. The numbers in Excel 2011 are different from those in Excel 2010.

I also found a format I liked that was horizontal. To generate the following on the Mac,. I used Set oSALayout = Application.SmartArtLayouts(95) .


The code and spreadsheet are available in two formats. The full xlsm file is Tree_OrgChart_For_Website.xlsm. If you prefer to build the macro-enabled workbook yourself, then you can download the xlsx file, Tree_OrgChart_For_Website.xlsx, and the macro code, TreeOrgChart.bas, separately, then import the macro into the macro-free workbook. You can find the mindmap file at

Return To Top

Part IV June 6, 2014

I have now incorporated the mind map code into the org char code. The resulting mindmap is

The new code downloads follow: TreeOrgChartandMM.bas, Tree2OrgChartandMM.xlsm, and Tree2OrgChartandMM.xlsx.

Addendum June 8, 2014

Thanks to Debra Dalgleish ( for finding yet another PC vs. Mac difference.
1) The Heirarchy Chart numbers differ. I have generated a document showing the charts with PC numbers and Mac numbers.
2) When the code creates a picture from the SmartArt, the PasteSpecial command differs on the PC and the Mac. I tested all three PC formats on the Mac and Excel 2011 did not complain. Hence I will have the code default to PNG since then PasteSpecial works on both.

I have updated both the above org chart codes to become cross platform.

I am honored once again that Debra Dalgleish presented my latest work in her Contextures Blog.

Flowcharts from the CETA Scheduler June 8, 2014

I have updated my code from above in Part II to include org charts which I am using as flowcharts for my code

makeMMTree.bas, genProcedureFlowChart.bas, Globals4FlowChart.bas - Last updated 07/31/14.

Having just used the code extensively, I found that the mind map file sometimes looks like it is missing branches. I have a procedure tree with 95 lines of calls in columns G, H, I, J, etc.on the Tree2MM worksheet. However, I found that the tree branches are just not expanded in Freeplane. Hover over on the little white circles at the end of lines then click on the plus sign that become visible to the expand the tree. Another note: some of the routines I am looking at, such as the one just mentioned, make huge org charts so when I think the mapping is taking too long, I hit ESC, then turn off the option to create an org chart. Then the tree with associated mind map file is then built quite rapidly. When I turned org chart generation back on, the result was unreadable. The mind map, on the other hand was very readable and useful. Each graphic format has its use. Note that I had to append the file type of .txt to the mind map file to make it accessible. Download the file then remove the .txt to restore its file type to .mm.

Using the modules in your code:
Import the files into the VBA editor of your code. To run the code that creates the two new sheets full of data, run the procedure called genProcedureFlowChart_Main at the top of the genProcedureFlowChart.bas module.

You will need to activate the Extensibility VBA tool to access the VBA Extensibility Model. On the Mac, the tool dialog box is found in the VBA editor under Tools/References.

The current module allows me to use the existing Class Search data, which is a list of current course offerings at UH, present it in both graphical and textual formats, and modify the schedule locally. I am mostly through with the project. As indicated above I need validation cells for a good Mac experience and form control Combo Boxes for a good PC experience. The Scheduler code is very complicated. The validation cell version works fine but I now need to implement the Combo Box, a.k.a. DropDown, version for cross platform compatibility, in my latest addition to the functionality of the code. As the module development progressed, code became obsolete, cells moved, etc. I thought I would show you a couple of flow charts I created today so I can figure out how and where I need to integrate the DropDowns with the final validation cell usable code. When the full code is ready later this summer, I will post it here with a user guide. In the meantime, feel free to contact me about the flow chart code if you wish to implement it. Note that I do not address user created classes as I do not use them.

Two of the more complicated flow charts from my code follow. The top line is the module name and bottom line is the procedure name. The SmartArt organization chart typically needs adjustment before copying it into a picture. I therefore added a button on the Tree2MM sheet to do the conversion once I have resized the SmartArt to reduce white space and to adjust the titles so they are readable.


All my codes are a work in progress. They are also all open source so feel free to contribute ideas, code, etc. for improving on my work. If you use my work in your own code, please give me credit as I have given others credit in the code itself. If you unearth errors, please let mw know ASAP so I can address the issue(s).

Return To Top

June 19, 2014 - More Mac/PC differences


While working on my cross platform code I happened to choose a colorindex of 32. When I checked the code in Excel 2010, I got quite a surprise! I ran the following code on both Mac and PC. Below are the results.

Sub colorTheCells() ' in column I
Dim iLoop As Integer
Dim iRow As Integer

For iLoop = 0 To 56
iRow = iLoop + 2
Cells(iRow, 9).Interior.ColorIndex = iLoop
Next iLoop

End Sub

The Mac version (Excel 2011) is on the left and the PC version (Excel 2010) is on the right.

Some good color sites I have found are


Another difference I see is that I use 100% zoom on the Mac for all my development. When I run the same code on the PC, I have to set the zoom to 65% to see essentially the same size on my screen. I now have my Workbook_Open() procedure set to run the following code.

Sub runZoom()
Dim wsCurrent As Worksheet
Dim wsStartup As Worksheet
Dim sep As String

sep = Application.PathSeparator

Application.ScreenUpdating = False
Set wsStartup = ActiveSheet

Select Case sep

Case "\" 'it's a PC
For Each wsCurrent In Worksheets
ActiveWindow.Zoom = 65
Next wsCurrent

Case ":" 'it's a Mac
For Each wsCurrent In Worksheets
ActiveWindow.Zoom = 100
Next wsCurrent

Case Else
MsgBox "The OS is neither for a Mac or a PC. "

End Select

Application.ScreenUpdating = True

End Sub

Return To Top

July 29, 2014 - optional printing to the OS default printer or to a PDF file.

My Scheduler needs the ability to write to a default printer to generate handouts as well as generating PDFs that can be combined using Acrobat then sent to interested parties. Of course, there are cross platform issues. The details of the issues are in the file I wrote to demonstrate using the printing options. An image of the Main sheet follows.

As usual, I provide the xlsm file as well as the code and interface separately for those who do not like to download macro-enabled files. The main workbook is
Printing PDFs on Mac and PC.xlsm. The macro-free version is Printing PDFs on Mac and PC.xlsx. The code module is PDFPrint.bas. The code to run the setup is in the ThisWorkbook code.

Private Sub Workbook_Open()
End Sub

Once you have imported the bas file and updated ThisWorkbook, save it as an xlsm file. Then quit Excel itself on the Mac or just close the file on the PC. Reopen the file to start using it. There are issues on the Mac so you need to clear out Excel's memory.

Below is a screen capture of the pdf sample printout.


Form Validation

I found I needed to use the concept of a list validation cell on a form. I could not find any information on the web that dealt with list validation cells, only data format restrictions for combo boxes on a form. My form fills a combo box with a named range. The solution was simple. When I press the OK button to return to the spreadsheet, I look for an error in the Match function. If the combo box entry is not in the list, it won't be found by Match. If that's the case, the code sends you back to choose from the list in the combo box. You can also just cancel the form.

There is a way to restrict data entry as a property of the combo box. For the combo box if Matched Entry is True and Required, you get the following error if there is a problem when you leave the combo box.

I could find no way to change the error message so it was easier to understand. In addition, I only care when the form is exited, not when the Combo Box is exited. Thus I came up with using the Match worksheet function. The cmdOK_Click code associated with the OK button follows.

Private Sub cmdOK_Click()
Dim rowNumber As Integer

' chosenNumber = -1 is set when the form loads.
If chosenNumber = -1 Then
MsgBox "Please select a number."
Exit Sub
End If

' do a check to make sure the number is in the list
On Error GoTo Oops
rowNumber = WorksheetFunction.Match(chosenNumber, Range("Numbers"), 0)
ActiveSheet.Cells(7, 7) = chosenNumber
Unload Me
Exit Sub


MsgBox "Your choice is not in the list. Please select a number from the list."
End Sub

The xlsm file: FormValidation.xlsm

Load the spreadsheet using an xlsx file with form and code data: FormValidationInfo
This link takes you to another URL with screen shots of all the form element properties, the xlsx file, and the VBA code.

Return To Top

How to Generate a filtered Table to be used as a Pivot Table source file.

August 22, 2015

Last spring my chair asked me how many sections of a math course should we offer in the fall based on previous enrollments. That request started a landslide of my learning Tables and Pivot Tables in Excel. I found that Pivot Tables, clearely the solution, have a drawback for my type of data. The filters show all unique entries in a table column. They are not dependent on other filters in the Pivot Table. That's fine for business where you can group dates into months, years, etc. However, dates are not part of my table. I have nothing to group. I have fourteen semesters worth of data. In that table the unique entries of various fields are
10 Colleges
222 Subjects
3286 Courses
1987 Instructors
306 Rooms

Clearly I needed to somehow filter the table to be usable by one college without looking at another college's data. The result is that I have developed the code to filter the master table with 47311 rows of data to create a college table containing only data relevant to that college. Actually, the code I developed used a smaller data set for development found at My application
1) reads a typical advanced autofilter criteria Table limited to six rows for the testing process,
2) creates the single column Table entry in the master data Table for filtering,
3) filters that column to TRUE,
4) copies the visible rows to another worksheet, and finally
5) makes a new Table with just the copied rows.

Much more detail is given on the Notes sheet in the workbook. You can download the workbook, TablesForPivotTables.xlsm, or download the macrofree workbook, TablesForPivotTables.xlsx, and the code module, FilterTable.bas to import into VBA.

The files with all the testing removed are TablesForPivotTables_Trimmed.xlsm, TablesForPivotTables_Trimmed.xlsx, and FilterTable_Trimmed.bas.

Please feel free to use the application subject to the legal section at the bottom of this page.* I would love to know of any improvements you can suggest. Also, please let me know if something is not working. Be careful updating the criteria table. To add a column, type the header column in the cell next to the existing header row. When you press Return or Enter, the new column will appear an you can copy/paste into the TableBodyRange. To remove a column, delete the entire column.

UPDATE. It's actually much easier just to do an advanced autofilter with the criteria table (thanks, Debra of So, you can look at the codes as just examples of creating formulas for Table entries. It's a whole new way of thinking for me.

Return To Top

More Flow Chart

I have discovered that I would like a quick way to find out buttons are calling a particuler routine. When I do a Find in the VBA editor, the information on this list is not included. Therefore I have added a sheet to the flow chart. When filled, the sheet goes through the entire list of button calls and lists their procedure calls. The button to fill the list is on Tree2MM at the top of column G. Below are links to the updated codes. I have made all former references to get the latest version.

makeMMTree.bas, genProcedureFlowChart.bas, Globals4FlowChart.bas - Last updated 06/13/16. The new list does not treat duplicate button texts correctly. Different sheets have buttons with the same text but call different routines. A fix will be on its way soon. 06/25/16

Return To Top

List your iOS Apps in Excel

Long ago I found out how to make a PDF listing of my iOS apps. However, I really wanted to create an Excel spreadsheet of the list. The attached files show you how to do that on a Mac.

The method is to
1) Print the file list from iTunes to a PDF.
2) Export the resulting PDF to a Word file.
3) Clean up the Word File.
4) Save it as a UTF-8 text file.
5) Import the text file into Excel.
6) Clean up the spreadsheet.

The PDF gives you a full description of the above process. There are two VBA code files as well. DebugHelp.bas and the main code TheExcelCode.bas. I am including the former should you need to keep reloading the original text file and the latter creates the cleaned up Excel worksheet.

March 25, 2017


Return To Top

Some of my favorite Excel web sites

Excel spreadsheets and VBA development:

Note that I have no financial or other business interest in any site or book referenced below. I just find them useful.

For Excel tutorials my favorite sites are

I'm sure there are more but these are currently (June 2016) my goto sites for information.

Here are links to two super resource lists.

My favorite search result page is followed by the above list .

Engineering focusses Excel pages I have found are
For great animations go to It's a little out of date (2012) but great nonetheless.
For spreadsheets dealing with technical problems, check out
Newton Excel Bach, not (just) an Excel Blog

A great site for learning how to use Solver. His video tutorials are great.
Solver Video Summary

Another great site for code snippets in VBA as well as other languages like
Python, SQL, C#, Javascript, HTML, CSS, Actionscript, Flex, Per,l Ruby, PHP, C, C++, XML

Walkenbach wrote superb books on Excel
He has some nice examples at

Walkenbach's Power Programming book talks about some of the topics Pearson addresses.. Another good book that addresses some of the concepts is VBA Developer's Handbook (April 25, 2001).

Microsoft's help page for Visual Studio

Another useful site I found is VBA User Guide, Methods and Properties.

If I have missed a goodie, please let me know. My email address is ltownsend at hartford dot edu.

If dealing with the file system, you can use Application.PathSeparator. It returns ":" for the Mac and "\" for the PC. I use it in Select/Case constructs. All code I write must be cross-platform compatible since I develop on the Mac in Excel 2011 but the faculty that use my codes are typically using Excel 2010 on a PC.

A superb list of Excel Functions including a brief explanation of each.

Return To Top


How to quickly get a folder listing on a Mac

For years I have used the app PrintWindow and loved it. It gives a folder/file list with all the info for a given folder. My frustration has been that the first column of the resulting Excel file uses spaces at the beginning to identify sub folders. Today I found the solution. It was so obvious I can't believe I didn't think of it. The trick is to use TRIM to get rid of spaces in the cell, including the first one. Use LEFT to grab the first character in the modified cell. Then find the location of that character in the original cell. Finally, subtract 1 because you want to know the location of the last space before the first character, not the location of the first character.


Here is my testbed to show that it works. A is always the first character in the string, B is the second, etc. I used Courier as the font so you could see the locaation of characters in the string.

Trim the cell. Get the first non-blank character in the string. Find its position in the original string Subtract 1 to get the number
of spaces before the first
non-space character in the original string.
     F F
    EF EF

Return To Top


< -------------------------------------------------------------- Legal Stuff ------------------------------------------------------------>

The University of Hartford (the "University") wishes to thank Prof. Lee Townsend for preparing this web page on behalf of the University, which provides any information included or referenced within this web page "AS IS" for the sole purpose of illustrating Microsoft® Excel® spreadsheet application techniques. Despite any contrary indication, the University provides no warranty of any kind concerning this web page, either express or implied including, without limitation: any implied warranty of merchantability, fitness for a particular purpose or non-infringement. The University further does not warrant that any aspect of this web page will meet any party's requirements or that the operation of any aspect of this web page will be error free. The University accepts no responsibility or liability for the accuracy, copyright compliance, legality or decency of any material contained in or referenced through this web page.

Microsoft® and Excel® are registered trademarks of Microsoft Corporation in the United States and/or other countries.

Copyright © 2015 The University of Hartford. All rights reserved, unless specifically granted on this web page otherwise. The University grants full permission to any person to link to part or all of this web page.

Return To Top


 Last Updated 08/06/16