Swimmer Plots in Excel

A reader of the Peltier Tech Blog asked me about Swimmer Plots. The first chart below is taken from “Swimmer Plot: Tell a Graphical Story of Your Time to Response Data Using PROC SGPLOT (pdf)“, by Stacey Phillips, via Swimmer Plot by Sanjay Matange on the Graphically Speaking SAS blog.

The swimmer chart below is an attempt to show the responses of several patients to drug treatments. Each horizontal stripe represent a patient’s history, color coded by the stage of the patient’s disease at the onset of treatment at month zero. Different events are plotted within each patient’s stripe. The term “Swimmer Plot” comes from the resemblance to lanes in a swimming pool, where swimmers (events) must stay in their own lanes.

Swimmer Plot

The paper cited above gives a detailed protocol and related code for constructing this chart in a statistical graphics package that I’ve never used. The Peltier Tech blog reader wondered if there was a way to build this chart in Excel without having to draw shapes on the chart and locating them inexactly with the mouse.

I’m never one to pass up a reasonable challenge in Excel charting, so I decided to give it a try. This is the kind of chart that can probably be adapted to a variety of uses, and it’s a great way to help people learn how to think outside their usual approaches, to push Excel beyond its supposed limits.

The Approach

One’s first thought might be to build a horizontal bar chart, then use XY scatter data series for the various symbols on the bars. The arrows would have to be shapes drawn on the chart. This mixture of bar and XY chart types causes problems when trying to synchronize axes and aligning markers with bars.

On second thought, though, this chart can be built using XY chart series exclusively. The thick horizontal bands can be made from thick-lined error bars. The arrows at the end of certain bands can also be made using error bars. The only shape needed is for the arrow in the legend.

This entire protocol is one of my longest ever, but it will be worth the ride.

Swimmer Plot Data

All of the data in the chart conforms to two axes. The horizontal (X) axis shows months, either the total duration of the patient’s history or the treatment events. The vertical (Y) axis is simply the index of the subjects (patients): subject 1 is the bottom band in the chart and its associated symbols, subject 2 is the second band and symbols, on up to subject 10 at the top of the chart. In this example, the X axis data has been color coded blue and the Y axis data orange.

This first set of data shows the endpoints of the patient histories. Patients 10 and 3 have month data (18.2 and 9.5 months) in the Stage 1 column, patients 9, 5, and 1 in the Stage 2 column, etc. I optically extracted this data from the chart above before I realized it was included in the pdf article. I’ll take responsibility for any transcription errors.

Swimmer Plot Main Data

Each set of markers in the chart need X (month) and Y (subject) data. The ranges below show data for the four sets of markers and the set of arrows that will be added to the chart.

Swimmer Plot Supplemental Data

Building the Chart: The Swimmer Lanes

The first block of data is used to create the bands in the swimmer chart. Excel’s usual arrangement is to have X values in the first column of the data range and one or more columns of Y values to the right. Our data has Y values in the last column, and several columns of X values to the left. So putting this data into the chart will take a few steps.

Select the Disease Stage 1 column of data, and hold down the Ctrl key to select the unlabeled column of subject indices, then insert an XY Scatter chart. Most of the Disease Stage 1 column is blank, so only two points appear.

Swimmer Plot Construction

I’ve already stretched the chart above to its final size, and I’ve scaled the axes using their ultimate scale parameters. The X axis extends to -1 to allow room for the Durable Responder indicators.

The chart below has had the tick marks and labels removed from the vertical (Y) axis. Note that I’m leaving out chart and axis titles and other annotations to avoid distractions.

Swimmer Plot Construction

Now the next three Disease Stage series must be added. You can do this in at least two ways. My favorite is to select and copy the data, using the Ctrl key if needed to select discontiguous regions, then select the chart and use Paste Special from the Home tab. Use the settings on the screen shot below: add data as new series, values in columns, series names in first row, categories in first column.

Paste Special Dialog

Alternatively, right click on the chart and choose Edit Data from the popup menu or click on Edit Data on the Chart Tools > Design tab to open the Select Data Source dialog. Click the Add button, then populate the Edit Series dialog with the ranges containing the data for the new series.

Select Data Dialog

Repeat until all of the Disease Stages are potted on the chart.

Swimmer Plot Construction

I’ve formatted the markers with the desired formatting for the swimmer plot lanes. Basically I picked four colors that were a bit darker than in the original chart from the cited paper, and lightened them by applying 30% transparency.

Shameless Plug

Previously I noted that the Disease Stage data for this chart was listed with several columns of X data and one column of (shared) Y data. This differs from Excel’s assumption that the data consists of one column of (shared) X data and several columns of Y data. Because of this data arrangement, the chart has to be created tediously, one series at a time.

Such tedium isn’t necessary, however. Using my Peltier Tech Chart Utility, I was able to use the Quick XY Charts feature to create the chart with all of these series in one shot.

Peltier Tech Chart Utility - Quick XY Charts

I selected the data, clicked on the Quick Charts button on the Peltier Tech ribbon, selected Series in Columns and the X-X-Y data layout (highlighted above). The result was this XY Scatter chart, which requires much less manipulation to generate our Swimmer Plot.

Quick XY Swimmer Chart

The series need to be formatted: square markers with the colors and transparency described above and no lines. Also the legend should be moved to the right of the chart.

Back to the Lanes

We’ll use thick error bars as the swimmer lanes. Select one series and add error bars using the “+” icon in Excel 2013…

Add Error Bars in Excel 2013

… or using the Chart Tools > Layout tab in Excel 2007 and 2010.

Add Error Bars in Excel 2007-2010

Excel adds horizontal and vertical error bars to an XY series.

Swimmer Plot Construction

Select the vertical error bars, and click the Delete key. Then select the horizontal error bars and press Ctrl+1 (numeral one) to open the format dialog or task pane. This is the Excel 2013 task pane, but the Excel 2007-2010 format dialog is essentially the same.

Format Error Bars

Select the options shown above: Minus direction only, No End Caps, and Percentage using 100%. The chart below shows Minus 100% error bars with End Caps not removed.

Swimmer Plot Construction

Format the error bar lines with the colors used for the markers, and select a thickness for the bars which will be suitable for the chart.

Format Error Bar Lines

Hey look, bars without a bar chart.

Swimmer Plot Construction

Add and format error bars for the rest of the Disease Stage series.

Swimmer Plot Construction

We need to remove the markers from the chart without removing them from the legend. We have to trick Excel into thinking each series in the legend is formatted with markers while each point is formatted without.Click once to select a series, then again to select a marker, then click Ctrl+1 to open the formatting dialog or task pane for the single point, and choose the No Marker option. The marker from the top Stage 1 point has been removed in this chart:

Swimmer Plot Construction

Repeat; all Stage 1 markers are hidden below:

Swimmer Plot Construction

And repeat for all of the markers:

Swimmer Plot Construction

Add the Treatment Events

Several sets of XY markers are now added. You can copy the data,select the chart, and use Paste Special as described above, or you can use the Select Data Source dialog.

Here is the chart with Complete Response Start markers added to the lanes, with the markers formatted as red triangles like those in the example chart.

Swimmer Plot Construction

Now Partial Response Start points have been added.

Swimmer Plot Construction

Finally, Response Episode End has been added.

Swimmer Plot Construction

Add the Continuation Arrows

The continued Response data is displayed not as markers but as arrows, indicating that the activity extends further to the right than the lanes. This is easy to do with an XY series that shows no markers but instead has error bars with arrowheads on the ends of the bars.

First, add the data as a new series with no markers and a heavy black line. The heavy dark line will somewhat resemble the arrows in the legend.

Swimmer Plot Construction

Add error bars (use the same method as for the error bar lanes above).

Swimmer Plot Construction

Select and delete the vertical error bars as before, then format the horizontal error bars as follows: Plus direction only, No End Caps, and a Fixed Value of 1 (e.g., one month along the X axis).

Format Error Bars

Then format the error bar lines as medium thickness black lines with the appropriate arrowhead type and size. The “begin” arrow points toward the markers, the “end” arrow points away.

Format Error Bar Arrows

The result is a set of arrows extending beyond some of the swimming lanes.

Swimmer Plot Construction

Now we need to remove the black lines from the chart without removing them from the legend. In the same way we hid the unwanted markers from the chart but kept them in the legend to denote the lanes, we can select the entire series with one click, then select one individual line segment, then press Ctrl+1 and format this segment as No Line.

Here the first segment has been hidden.

Swimmer Plot Construction

Now the second segment is hidden.

Swimmer Plot Construction

A few clicks later, all traces of the line are gone.

Swimmer Plot Construction

Add Durable Responder Indicators

Finally, the Durable Responder data is added to the chart. The -0.25 X values place the markers just left of the vertical axis and the start of the lanes.

Swimmer Plot Construction

Final Adjustments

A couple small adjustments improve the look of the chart. First, the vertical axis scale of 0 to 11 leaves rather wide margins above and below the data. If the axis scale min and max are changed to 0.25 and 10.75, this margin is slightly reduced.

The -1 horizontal axis minimum is strange, but changing the horizontal axis number format to 0;0; hides the negative value.

Swimmer Plot Construction

Finally, if the thick black line is not acceptable as a legend marker for the Continued Response arrows, you can use an arrow for the legend entry.

First, use a white line instead of the black line for the series lines, then hide each segment of this line as you hid the black line segments; this places a white line in the legend. If you had formatted the series as No Line, the line in the legend would not be there, and the markers would be squeezed right up against the legend labels, which does not leave room for a centered arrow.

Then insert an arrow from the Insert Shapes gallery on the ribbon. Format it as a heavy line with the matching arrow appearance. Drag it into the desired position in front of the Continued Response label.

Swimmer Plot Construction

Wrap Up

Charts like this swimmer plot, the related Gantt chart, and other charts were difficult to make in Excel 2003 and earlier versions of Excel. You needed to combine stacked bar chart and XY scatter chart data in the same chart. This required special effort to ;ine up the XY markers with the bars, and keep them aligned.

Excel 2007 introduced much more flexibility in formatting of lines, so you can make thick bars with any line segments, such as series connecting lines and error bars. Swimmer plots and Gantt charts are much easier to make and maintain, because markers and bars are easy to keep in place using a single set of axes.

Peltier Tech Chart Utility

Peltier Tech Advanced Chart Utilities for Mac

The Peltier Tech Advanced Chart Utilities for Mac is here.

Over the past month a month, I’ve converted the Advanced Edition of the Peltier Tech Chart Utility so that it works on a Mac as well as in Windows. I’m pleased that every feature from the Windows version has made it into the Mac version, though some options are not available. This is due mostly to the inconsistencies between Office on the Mac and Office in Windows, such as the unavailability of modeless forms and the reduced selection of Paste Special options in MacOffice. A few differences relate to incompatibilities between the two platforms themselves, such as the strange special keys and special key behavior on the Mac (somehow the Mac’s Cmd-Ctrl-Opt-Shift keys provide less functionality than Windows’ Ctrl-Alt-Shift). The lack of a customizable ribbon in Mac Office means the user experience is less fulfilling than in Windows.

 The Charts

The Advanced Edition for the Mac has all the custom charts as the Standard Version for the Mac and for Windows: Waterfall, Box Plot, Marimekko, Cluster-Stack, etc., and all the new charts introduced last month in the Advanced Edition for Windows: More Waterfalls, Diverging Stacked Bar, Paretos and Histograms, and more.

In addition to the charts shown here, any new custom charts developed by Peltier Tech will become part of the Advanced Edition. If you hear from the Peltier Tech twitter feed, the Peltier Tech Facebook page, or the Peltier Tech Blog that a new chart has been added (or a bug has been fixed), email for a new download link so you can keep your license up-to-date.

Custom Chart Comparison of All Versions and Editions of the Peltier Tech Chart Utility

The Features

In addition to the helpful features of the Standard Edition, such as Edit Series Formulas, Copy and Paste Series Formats, Label Last Point, and Export Chart as Image File, the Advanced Edition adds such items as Export Range as Image File, Export Charts to PowerPoint and Word, Switch X and Y, and a simpler Error Bar Manager.

In addition to the features shown below, any new functionality developed by Peltier Tech will be added to the Advanced Edition. If you hear via the Peltier Tech twitter feed or the Peltier Tech Blog that a new feature has been added (or a bug has been fixed), email for a new download link so you can keep your utility current.

Feature Comparison of All Versions and Editions of the Peltier Tech Chart Utility

The Details

The Advanced Edition for the Mac runs on the latest updates of Excel 2011 for the Mac, and the relevant versions of the Mac OS that supports Excel 2011.

Detail Comparison of All Versions and Editions of the Peltier Tech Chart Utility

The Deals

The Advanced Edition retails for $99, just slightly more than the $79 for the Standard Edition. But there are some great introductory deals:

  • If you purchase a license for the Advanced Edition between July 8 and July 15, you get it for the same $79 price as the Standard Edition.
  • If you already have a license for the Standard Edition, send me an email and I will send you a coupon code for a free upgrade to the Advanced Edition.
    • In the future, you can upgrade from the Standard to Advanced Edition for only the $20 difference in price.
  • If you have licensed one of the earlier Waterfall or Cluster-Stack Utilities for the Mac, email me and I’ll send a coupon for half off the $79 introductory price.
    • In the future, if you have licensed any of the older utilities, email me for a half-price coupon.
  • When Microsoft releases the long-awaited Excel 2014 for Mac, the Peltier Tech Utility for Mac will be updated, and current licensees will be able to upgrade for no charge.
  • When Microsoft releases Excel 16 for Windows (probably a/k/a Excel 2016 or 2017), the Peltier Tech Chart Utility will be upgraded, and current licensees will as always be able to license the upgrade for half price.

Visit the Peltier Tech Chart Utilities page to take advantage of these special offers.

Peltier Tech Chart Utility

Peltier Tech Chart Utilities for Mac Excel – Advanced Edition

It’s nearly finished!

In only a month, I’ve converted the Advanced Edition of the Peltier Tech Chart Utility so that it works on a Mac as well as in Windows. I’m pleased that every feature from the Windows version has made it into the Mac version, though some options are not available. This is due mostly to the inconsistencies between Office on the Mac and Office in Windows, such as the unavailability of modeless forms and the reduced selection of Paste Special options in MacOffice. A small number of differences relate to incompatibilities between the two platforms themselves, such as the strange special keys and special key behavior on the Mac (somehow the Mac’s Cmd-Ctrl-Opt-Shift keys provide less functionality than Windows’ Ctrl-Alt-Shift). And the lack of a customizable ribbon in Mac Office means the user experience is less fulfilling than in Windows.

 The Charts

The Advanced Edition for the Mac has all the custom charts as the Standard Version for the Mac and for Windows: Waterfall, Box Plot, Marimekko, Cluster-Stack, etc., and all the new charts introduced last month in the Advanced Edition for Windows: More Waterfalls, Diverging Stacked Bar, Paretos and Histograms, and more.

In addition to the charts shown here, any new custom charts developed by Peltier Tech will become part of the Advanced Edition. If you hear from the Peltier Tech twitter feed or the Peltier Tech Blog that a new chart has been added (or a bug has been fixed), email for a new download link so you can keep your license up-to-date.

Custom Chart Comparison of All Versions and Editions of the Peltier Tech Chart Utility

The Features

In addition to the helpful features of the Standard Edition, such as Edit Series Formulas, Copy and Paste Series Formats, Label Last Point, and Export Chart as Image File, the Advanced Edition adds such items as Export Range as Image File, Export Charts to PowerPoint and Word, Switch X and Y, and a simpler Error Bar Manager.

In addition to the features shown below, any new functionality developed by Peltier Tech will be added tothe Advanced Edition. If you hear via the Peltier Tech twitter feed or the Peltier Tech Blog that a new feature has been added (or a bug has been fixed), email for a new download link so you can keep your utility current.

Feature Comparison of All Versions and Editions of the Peltier Tech Chart Utility

The Details

The Advanced Edition for the Mac runs on the latest updates of Excel 2011 for the Mac, and whatever relevant versions of the Mac OS that supports Excel 2011. Mavericks, Mountain Lion, Cougar, even Water Buffalo, for all I know, which I find more confusing than Microsoft’s imaginative names, like Windows 7, 8, 3.14, etc. Joking aside, if you keep your Mac software up-to-date, you should have no problems.

Detail Comparison of All Versions and Editions of the Peltier Tech Chart Utility

The Deals

The Advanced Edition of the Peltier Tech Chart Utilities for the Macintosh will be available on Tuesday, July 8, 2104.

The Advanced Edition retails for $99, just slightly more than the $79 for the Standard Edition. But there are some great introductory deals:

  • If you purchase a license for the Advanced Edition between July 8 and July 15, you get it for the same $79 price as the Standard Edition.
  • If you already have a license for the Standard Edition, send me an email and I will send you a coupon code for a free upgrade to the Advanced Edition.
    • In the future, you can upgrade from the Standard to Advanced Edition for only the $20 difference in price.
  • If you have licensed one of the earlier Waterfall or Cluster-Stack Utilities for the Mac, email me and I’ll send a coupon for half off the $79 introductory price.
    • In the future, if you have licensed any of the older utilities, email me for a half-price coupon.
  • When Microsoft releases the long-awaited Excel 2014 for Mac, the Peltier Tech Utility for Mac will be updated, and current licensees will be able to upgrade for no charge.
  • When Microsoft releases Excel 16 for Windows (probably a/k/a Excel 2016 or 2017), the Peltier Tech Chart Utility will be upgraded, and current licensees will as always be able to license the upgrade for half price.

Mark your calendars so you don’t miss the special offers.

Peltier Tech Chart Utility

Pivot Table Resources

Pivot Table Tutorials

There are numerous articles and tutorials about Pivot Tables on the Peltier Tech blog. This is a nearly complete list of Pivot Table posts.

Explore Your Data With Pivot Tables

Pivot tables are one of Excel’s most outstanding features. They allow fast dynamic and interactive analysis of data from the workbook or from any number of external sources. This tutorial shows the power of pivot tables for fast and detailed analysis.

Preliminary Data Exploration with Excel Pivot Tables

In this tutorial we first normalize the data, then generate easy Marginal Means Plots and Interaction Plots to quickly explore the data.

Making Regular Charts from Pivot Tables

Pivot charts are powerful: they are dynamic, interactive, and can be pivoted. However, sometimes you need something that a regular old chart can do, that pivot charts cannot. This tutorial shows how to create a regular chart from a pivot table.

Create and Update a Chart Using Only Part of a Pivot Table’s Data

This tutorial shows how to use a little VBA to create and maintain a chart that uses only part of a pivot table’s data.

Update Regular Chart when Pivot Table Updates

When a  pivot table is pivoted or updated, it may have different numbers of rows and columns, and a regular chart must be updated manually to keep up. Or you could use a little VBA to keep the chart in line with the pivot table.

Using Pivot Table Data for a Chart with a Dual Category Axis

You can easily lay out a pivot table so that two nested fields can be used to populate the labels in a dual category axis.

Grouping by Date in a Pivot Table

If you collect daily data, the day-to-day variation may overwhelm longer-term trends. You can group dates in your pivot table by larger units (weeks, months, years) and perform broader analysis.

Clean Up Date Items in An Excel Pivot Table

Grouping a pivot table by dates can make the table and related chart easier to read. This article shows how to remove extraneous date entries.

Referencing Pivot Table Ranges in VBA

You can create great analyses by merging the power of pivot tables with the flexibility of VBA. This tutorial removes much of the confusion about how to refer to parts of the pivot table in code.

Copy a Pivot Table and Pivot Chart and Link to New Data

If you copy a chart to a new worksheet, it still reflects data from the old worksheet. You can try to fix all the links, but even this tedium will not help with a pivot table. But the approach described here will do the trick.

Pivot Table Conditional Formatting with VBA

Use VBA to format cells within a pivot table according to various conditions.

Dynamic Chart using Pivot Table and VBA

This article shows how VBA can update a regular chart when its pivot table data changes.

Dynamic Chart using Pivot Table and Range Names

This article shows how dynamic range names without VBA can update a regular chart when its pivot table data changes, as long as the number of charted series is unchanged.

Pivot Chart Formatting Changes When Filtered

When you apply a filter or slicer to a PivotChart the custom formatting can change with each change in the filter. Here’s what you can do about it.

Pivot Table Books

Here are a few books about pivot tables, available from Amazon and elsewhere. I’ve selected these books because I know the authors and I’ve used the books, so I can vouch for their quality. Disclosure: if you purchase one of these books using my link, I’ll get a teeny commission.

Beginning PivotTables in Excel 2007: From Novice to Professional

In Beginning PivotTables in Excel 2007 Debra Dalgleish explains what PivotTables are, how you can benefit from using them, how to create them and modify them, and how to use their enhanced features. Using a Pivot Table in Microsoft Office Excel 2007 is a quick and exciting way to slice and dice a large amount of data. Debra carefully explains the benefits of using Pivot Tables for fast data analysis, provides a step-by-step approach to those new to Pivot Tables, and offers tips and tricks that cannot be found elsewhere.

Excel 2007 PivotTables Recipes: A Problem-Solution Approach

Debra Dalgleish, Microsoft Office Excel “Most Valuable Professional” since 2001, and an expert and trainer in Excel, brings together a one-stop resource for anyone curious about representing, analyzing, and using their data with PivotTables and PivotCharts. Debra presents tips and techniques in this collection of recipes that can’t be found in Excel’s Help section, while carefully explaining the most confusing features of PivotTables to help you realize their powerful potential.

Excel 2013 Pivot Table Data Crunching (MrExcel Library)

This book will help you leverage all the amazing flexibility and analytical power of Pivot Tables. You will learn how to generate complex pivot reports complete with drill-down capabilities and accompanying charts. Then you’ll discover how to build a comprehensive, dynamic pivot table reporting system for any business task or function. Bill Jelen and Mike Alexander include step-by-step instructions, real-world case studies, plus complete and easy recipes for solving your most common business analysis problems.

Peltier Tech Chart Utility

Save and Retrieve Information With Text Files

Introduction

There are many techniques one can use to write information from a running Microsoft Office program. You can save data in a workbook, but this is unwieldy for saving small items, such as program settings. You can use ini files or the registry, but both can get complicated. Using the registry is more invasive than you may want to be, and it’s very hard to transfer registry settings among computers. One of the easiest methods to save and retrieve data is by using simple IO commands with text files. Such files are small, they are processed rapidly, they can be read with a simple text editor, and they can be easily copied from computer to computer.

Visual Basic I/O

Visual Basic contains some commands which are useful for fast and simple text file input and output. Since the Visual Basic for Applications (VBA) used to program Microsoft Office is based on VB, Excel and the other Office applications can use these commands to read and write text files. I frequently use this technique to save application settings or to store debugging information.

The VBA help files contain detailed information about the various IO commands. Rather than beat the topic to death, I’ll present a couple simple examples that write and read text from a text file, then I’ll show some practical examples. The reader is directed to the help files for further details.

The subroutine TextIODemoWrite, shown below, opens a specified text file, puts a simple text string into the file, then closes the file. Files are opened using integers starting with 1; the FreeFile function assigns the next available integer to the file, to prevent accidentally assigning the same integer to two files. When you Open the file, a buffer is allocated to it, and the access mode (e.g., Output) is assigned. If the file does not already exist, it is automatically created. Subsequent commands (Write and Close) refer to the file using its integer. Text can be written using Write or Print; refer to the help files for specifics. Closing a file deallocates the buffer and disassociates the file from the file number.

Sub TextIODemoWrite()
  Dim sFile As String
  Dim sText As String
  Dim iFileNum As Integer

  sFile = "C:\test\textio.txt"
  sText = "Testing 1 2 3."

  iFileNum = FreeFile
  Open sFile For Output As iFileNum
  Write #iFileNum, sText
  Close #iFileNum
End Sub

The subroutine TextIODemoRead below shows the reverse operation, opening a text file, retrieving some text, closing the text file, then displaying the text in a message box.

Sub TextIODemoRead()
  Dim sFile As String
  Dim sText As String
  Dim iFileNum As Integer

  sFile = "C:\test\textio.txt"

  iFileNum = FreeFile
  Open sFile For Input As iFileNum
  Input #iFileNum, sText
  Close #iFileNum
  MsgBox sText
End Sub

Save and Retrieve Settings

The VBA procedures above can be expanded to allow us to save settings for later retrieval. The simplistic approach used in function SaveSetting reads variable names and values from a file, and writes them to a temporary file. If the named variable already exists, its new value is written to the temporary file in place of the existing value. When all variables have been read and written, the original file is replaced by the new file.

SaveSetting uses some additional functions, IsFullName, FullNameToPath, FullNameToFileName, and FileExists, which are shown at the end of this article.

Function SaveSetting(sFileName As String, sName As String, _
      Optional sValue As String) As Boolean
  
  Dim iFileNumA As Long
  Dim iFileNumB As Long
  Dim sFile As String
  Dim sXFile As String
  Dim sVarName As String
  Dim sVarValue As String
  Dim lErrLast As Long
  
  ' assume false unless variable is successfully saved
  SaveSetting = False
  
  ' add this workbook's path if not specified
  If Not IsFullName(sFileName) Then
    sFile = ThisWorkbook.Path & "\" & sFileName
    sXFile = ThisWorkbook.Path & "\X" & sFileName
  Else
    sXFile = FullNameToPath(sFileName) & "\X" & FullNameToFileName(sFileName)
  End If
  
  ' open text file to read settings
  If FileExists(sFile) Then
    'replace existing settings file
    iFileNumA = FreeFile
    Open sFile For Input As iFileNumA
    iFileNumB = FreeFile
    Open sXFile For Output As iFileNumB
      Do While Not EOF(iFileNumA)
        Input #iFileNumA, sVarName, sVarValue
        If sVarName <> sName Then
          Write #iFileNumB, sVarName, sVarValue
        End If
      Loop
      Write #iFileNumB, sName, sValue
      SaveSetting = True
    Close #iFileNumA
    Close #iFileNumB
    FileCopy sXFile, sFile
    Kill sXFile
  Else
    ' make new file
    iFileNumB = FreeFile
    Open sFile For Output As iFileNumB
      Write #iFileNumB, sName, sValue
      SaveSetting = True
    Close #iFileNumB
  End If
  
End Function

The function is called using this syntax (bTest is declared as a Boolean). The filename (with or without path), the variable name, and the value are all passed to the function as strings. If a path is not included as part of the file name, then the workbook’s own path is used. The value of the function is true unless an error is encountered.

bTest = SaveSetting("C:\test\settings.txt", "test variable", "test value")

Subroutine GetSetting enumerates the variables in the file until the named variable is found, then it extracts the value of this variable.

Function GetSetting(sFile As String, sName As String, _
      Optional sValue As String) As Boolean
  
  Dim iFileNum As Long
  Dim sVarName As String
  Dim sVarValue As String
  Dim lErrLast As Long
  
  ' assume false unless variable is found
  GetSetting = False
  
  ' add this workbook's path if not specified
  If Not IsFullName(sFile) Then
    sFile = ThisWorkbook.Path & "\" & sFile
  End If
  
  ' open text file to read settings
  If FileExists(sFile) Then
    iFileNum = FreeFile
    Open sFile For Input As iFileNum
      Do While Not EOF(iFileNum)
        Input #iFileNum, sVarName, sVarValue
        If sVarName = sName Then
          sValue = sVarValue
          GetSetting = True
          Exit Do
        End If
      Loop
    Close #iFileNum
  End If
  
End Function

The function is called using this syntax. The filename (with or without path) and the variable name are passed to the function as strings. The value of the function is true unless an error is encountered (i.e., the file or the variable is not found), and the value of the variable is passed back by the function.

If GetSetting("C:\test\settings.txt", "test variable", sValue) Then
  MsgBox sValue
End If

Save Debugging Information

During development or debugging of a program, it’s useful to save information during its execution. Subroutine DebugLog saves information in a text file with a name like debuglog051225.txt in the parent workbook’s directory. It saves the date and time along with the debug message, so the timing of the messages can be followed. You can log any string value, including markers indicating how far program execution has progressed, what the value of a key variable is, what an error may have occurred, etc.

Public Sub DebugLog(sLogEntry As String)
  ' write debug information to a log file

  Dim iFile As Integer
  Dim sDirectory As String
  
  sDirectory = ThisWorkbook.Path & "\debuglog" & Format$(Now, "YYMMDD") & ".txt"

  iFile = FreeFile

  Open sFileName For Append As iFile
  Print #iFile, Now; " "; sLogEntry
  Close iFile

End Sub

The following are a few examples of how to use DebugLog.

DebugLog "Starting Execution"

DebugLog "Variable MyVar = " & MyVar

DebugLog "Error " & Err.Number & ": " & Err.Description

The following is an excerpt from an actual debuglog file, debuglog051223.txt created by one of my old projects.

12/23/2005 8:00:33 AM  Excel Version 9.0, Build 8924
12/23/2005 8:00:33 AM  Program Starting
12/23/2005 8:00:33 AM  - File Name: ABC Engineering 2005-12-23-0.doc
12/23/2005 8:00:33 AM  - Order Number: ABC Engineering 2005-12-23-0
12/23/2005 8:00:38 AM  - saved as C:\Orders\ABC Engineering 2005-12-23-0.doc
12/23/2005 8:00:38 AM  - file exists: True
12/23/2005 8:00:38 AM  - no backup directory specified
12/23/2005 8:01:25 AM  - Checking Row 17: 3chars
12/23/2005 8:01:26 AM  - Checking Row 16: 3chars
12/23/2005 8:01:26 AM  - Checking Row 15: 3chars
12/23/2005 8:01:26 AM  - Checking Row 14: 3chars
12/23/2005 8:01:26 AM  - Checking Row 13: 3chars
12/23/2005 8:01:27 AM  - Checking Row 12: 3chars
12/23/2005 8:01:27 AM  - Checking Row 11: 3chars
12/23/2005 8:01:27 AM  - Checking Row 10: 3chars
12/23/2005 8:01:27 AM  - Checking Row 9: 15chars
12/23/2005 8:01:30 AM  - removing extra empty paragraphs
12/23/2005 8:01:56 AM  Program Finished

Supplementary Functions

Function IsFullName(sFile As String) As Boolean
  ' if sFile includes path, it contains path separator "\"
  IsFullName = InStr(sFile, "\") > 0
End Function

Function FullNameToPath(sFullName As String) As String
  ''' does not include trailing backslash
  Dim k As Integer
  For k = Len(sFullName) To 1 Step -1
    If Mid(sFullName, k, 1) = "\" Then Exit For
  Next k
  If k < 1 Then
    FullNameToPath = ""
  Else
    FullNameToPath = Mid(sFullName, 1, k - 1)
  End If
End Function

Function FullNameToFileName(sFullName As String) As String
  Dim k As Integer
  Dim sTest As String
  If InStr(1, sFullName, "[") > 0 Then
    k = InStr(1, sFullName, "[")
    sTest = Mid(sFullName, k + 1, InStr(1, sFullName, "]") - k - 1)
  Else
    For k = Len(sFullName) To 1 Step -1
      If Mid(sFullName, k, 1) = "\" Then Exit For
    Next k
    sTest = Mid(sFullName, k + 1, Len(sFullName) - k)
  End If
  FullNameToFileName = sTest
End Function

Function FileExists(ByVal FileSpec As String) As Boolean
   ' by Karl Peterson MS MVP VB
   Dim Attr As Long
   ' Guard against bad FileSpec by ignoring errors
   ' retrieving its attributes.
   On Error Resume Next
   Attr = GetAttr(FileSpec)
   If Err.Number = 0 Then
      ' No error, so something was found.
      ' If Directory attribute set, then not a file.
      FileExists = Not ((Attr And vbDirectory) = vbDirectory)
   End If
End Function

Final Words

This article was originally posted on an old site which no longer exists. I was able to retrieve my work from this defunct site, so I am reposting it here as is. There are newer techniques for working with text files in Windows, but sometimes the old ways are as simple and effective as the new.

Peltier Tech Chart Utility

Peltier Tech Chart Utility

 

Create Excel dashboards quickly with Plug-N-Play reports.

 

Site by: Dawud Miracle, Business Coach & WordPress Websites