VBA: An Advanced Add-in to Open a CSV File and Create Chart with Data Specified by User

Introduction

This article builds upon a previous post, VBA: A Simple Tool to Open a CSV File and Create Chart with Data Specified by User, which showed how to build the barest possible VBA procedure for opening a CSV file and making a chart from its data. This article will show how to make the VBA procedure more robust, easier to use, and resistant to errors. It is assumed that you’ve already built the simple VBA tool from the earlier article.

Advanced Solution

Rather than the simple workbook we created in the previous solution, the advanced solution will use an add-in. This integrates the add-in’s functionality more smoothly into Excel, making the solution a more professional option.

Add-ins are like regular Excel workbooks, complete with worksheets and other Excel objects, but they have a different file extension, .xlam, and they are not visible in the Excel interface. They can be opened like regular workbooks when their functionality is needed, or they can be installed so that they are always available.

Code Workbook (Add-In)

Saved Information: Named Range Containing Chart Types and Styles

The worksheet contains a list of chart types that our program will create. These are the simplest and most useful charts built into Excel. The list has three columns, the first being the integer values of Excel’s chart type constants (e.g., xlArea = 1, xlXYScatter = -4169), the second the default chart styles in Excel 2013 (ignored in earlier versions), and the third the chart type names that will appear in the dialog. To modify the eerlier worksheet, simply insert a column between the Value and Type values, and insert the Style values.

The range A2:C12 has been named “ChartTypes”, so it will be easy for the code to put these values into the dialog. We can save information like this even in the invisible worksheets of an invisible add-in.

Create an Add-In

Now that we’ve updated the chart type data, we can save our workbook as an add-in.

With the workbook active, go to File tab > Save As, browse to a folder, change the Save As Type to Excel Add-In (*.xlam), and note that the dialog changes to the default user add-in library (C:\Users\[username]\AppData\Roaming\Microsoft\AddIns). This is a convenient place to store add-ins, because they are easier to find when you want to install them. However, if you have an orderly file system hierarchy, you may want to store the add-in with related files. If so, navigate to the desired folder. Then click Save.

The worksheets of an add-in are not visible in the Excel interface. However, you can temporarily change an add-in back to a regular workbook. In the VB Editor’s Project Explorer, select the ThisWorkbook object of the add-in’s VB project, choose the IsAddIn property in the Property Window, and change it to False. The add-in workbook is now visible like any other workbook. Make any adjustments, then change the IsAddIn property back to True.

Starting the Program: The Excel Ribbon

Since the sheet is not visible, there is no button to start the code. We’ll use a button on the ribbon instead.

Many web pages and blog posts have been written about writing XML code for custom ribbon controls. As far as I know, only two books have been dedicated to the ribbon.

One is RibbonX: Customizing the Office 2007 Ribbon (affiliate link), written by two Excel MVPs and one Access MVPs. This is a very comprehensive volume that covers more than you could possibly want to know about customizing your ribbon. On a dollars-per-page basis, it also has to be about the cheapest Excel reference I’ve ever purchased.

The other book about the Office ribbon is RibbonX For Dummies, and I must admit I know very little about it.

To make it easy to add custom ribbon elements to your workbook or add-in, you should download the Custom UI Editor from the Open XML Developer web site. Open the add-in or workbook in the Custom UI Editor, insert an Office 2007 Custom UI Part from the Insert menu, and paste the code below into the Custom UI Part.

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
  <ribbon>
    <tabs>
      <tab idMso="TabData">
        <group id="gpCSV" label="Process CSV">
          <button id="btnProcessCSV" label="Process CSV File" screentip="Import CSV 
              file, save as Excel workbook, insert chart using dialog-selected type 
              and data" size="large" onAction="RX_OpenCSVFileAndPlotData" 
              imageMso="GroupChartLocation" />
        </group>
      </tab>
    </tabs>
  </ribbon>
</customUI>

This code adds a custom group, “Process CSV”, to the Data tab of Excel’s ribbon, and adds a custom button, “Process CSV File”, to this group. The button calls a procedure named RX_OpenCSVFileAndPlotData.

The Custom UI Editor can be used to generate the callback for this procedure:

'Callback for btnProcessCSV onAction
Sub RX_OpenCSVFileAndPlotData(control As IRibbonControl)
End Sub

This callback is pasted into a regular code module, and the procedure that we’ve been working on, OpenCSVFileAndPlotData, is called from within the callback. You could simply have called OpenCSVFileAndPlotData from the XML code, but you would have had to add the control As IRibbonControl argument to the procedure definition. This makes it harder to simply call the code from the VB Editor’s Immediate Window.

Here is the finished callback, ready to go:

'Callback for btnProcessCSV onAction
Sub RX_OpenCSVFileAndPlotData(control As IRibbonControl)
  OpenCSVFileAndPlotData
End Sub

The button looks like this, at the right end of the Data tab of the ribbon:

Code Module

Here are the adjustments to the MChartFromCSVFile code module.

1. Get CSV File Name

There are several things we can do to make sure the program is nice to the users.

First of all, we need to exit gracefully if the user cancels the GetOpenFileName dialog. Canceling the dialog returns False, so we need to prevent the error that will occur when the program tries to open a file named “False”.

We should check whether the file is already open in Excel, and offer to work on the open file.

We should check whether the file is even a CSV file. If it is not a CSV file, it will not open in Excel with the expected column structure. It is possible to make the program smart enough to open any text, and split the data into columns using any delimiter (commas, tabs, pipe characters), but that is for another exercise.

If the user decides to abort at any point, the execution proceeds to a label named ExitProcedure at the end of the main procedure.

  Do
    sCSVFullName = Application.GetOpenFilename("CSV Files (*.csv), *.csv", , _
        "Select a CSV file", , False)
    
    '' check whether user cancels
    If sCSVFullName = "False" Then GoTo ExitProcedure
    
    '' check that file isn't already open
    bOpenFile = False
    For Each wbTest In Workbooks
      If wbTest.FullName = sCSVFullName Then
        bOpenFile = True
        Exit For
      End If
    Next
    If bOpenFile Then
      iMsgBoxResult = MsgBox("Data file is already open. Continue with open file?", _
          vbYesNo + vbQuestion, "File Already Open")
      If iMsgBoxResult = vbYes Then
        Set wb = wbTest
        Exit Do
      End If
    End If
    
    '' ensure that it's a CSV file
    If LCase$(Right$(sCSVFullName, 4)) <> ".csv" Then
      iMsgBoxResult = MsgBox("That doesn't appear to be a CSV file. Continue anyway?", _
          vbYesNo + vbQuestion, "Not a CSV File")
      If iMsgBoxResult = vbYes Then Exit Do
    Else
      Exit Do
    End If
  Loop

2. Open CSV file

The only thing to add here is a check whether our workbook wb has already been defined (that is, the CSV file was already open when the user started the program).

  If wb Is Nothing Then
    Workbooks.Open sCSVFullName
    Set wb = ActiveWorkbook
  End If

3. Save as workbook

There are many little changes we can do to make a user’s life easier. We want to save the workbook with the same name as the original CSV file, but we don’t want to bother the user with any duplicate file names. If we encounter a duplicate, we’ll append “_1” to the file name; if that’s also a duplicate, we’ll append “_2”, and so on.

We need to check for duplicate file names among files already open in Excel, because two open workbooks cannot share the same file name. Then we need to check for duplicate file names in the directory we will save the workbook in.

  sFileRoot = Left$(sCSVFullName, InStrRev(sCSVFullName, ".") - 1)
  sWbkFullName = sFileRoot & ".xlsx"
  iFile = 0
  Do
    '' check for duplicate file (already open)
    bOpenFile = False
    For Each wbTest In Workbooks
      If wbTest.FullName = sWbkFullName Then
        bOpenFile = True
        Exit For
      End If
    Next
    
    '' check for duplicate file (in folder)
    If Not bOpenFile Then
      If Len(Dir(sWbkFullName)) = 0 Then Exit Do
    End If
    
    '' increment file name
    iFile = iFile + 1
    sWbkFullName = sFileRoot & "_" & CStr(iFile) & ".xlsx"
  Loop
  
  wb.SaveAs sWbkFullName, xlWorkbookDefault

All of this checking may seem excessively compulsive, but being a good programmer means preventing user frustration. Remember, the user may know where you live.

4. Parse file

We can improve the dialog display slightly, by not trying to show the first four rows of data if there are less than three rows in the CSV file.

While we’re making life easier for the user, let’s check the first row for text values, so we can precheck the dialog box if it looks like the file starts with column headers.

  Set ws = wb.Worksheets(1)
  Set rng = ws.UsedRange
  vRng = rng.Value2
  iRows = rng.Rows.Count
  iCols = rng.Columns.Count
  
  '' info to display: column number, first few rows of column
  ReDim vChartData(1 To iCols, 1 To 2)
  For iCol = 1 To iCols
    vChartData(iCol, 1) = iCol
    sTemp = ""
    For iRow = 1 To 4
      If iRow > iRows Then Exit For
      sTemp = sTemp & vRng(iRow, iCol) & ", "
    Next
    sTemp = Left$(sTemp, Len(sTemp) - 2)
    vChartData(iCol, 2) = sTemp
  Next

  '' first row header labels if all cells in first row are text
  For iCol = 1 To iCols
    bFirstRowHeaders = Not IsNumeric(vRng(1, iCol))
    If Not bFirstRowHeaders Then Exit For
  Next

5. Show dialog

When we load the dialog (UserForm), we pass in the array that shows the first few rows of data using the ChartData property, and also pass in our best guess about whether the first row of the has header labels in the FirstRowHeaders property.

After the user dismisses the dialog, we check whether the user canceled using the Cancel property. If the user did not cancel, we get chart type, first row headers, and X and Y columns using the appropriate properties.

Now we’re done with the UserForm, so we can unload it. If the user has canceled, we jump to the ExitProcedure label at the end of our program.

  Set frmChartFromCSVFile = New FChartFromCSVFile
  With frmChartFromCSVFile
    '' pass in information we know
    .ChartData = vChartData
    .FirstRowHeaders = bFirstRowHeaders
    
    .Show
    
    '' check whether user canceled
    bCancel = .Cancel
    If Not bCancel Then
      '' get information selected by user
      myChartType = .ChartType
      bFirstRowHeaders = .FirstRowHeaders
      vX = .Xcolumns
      vY = .YColumns
    End If
  End With
  Unload frmChartFromCSVFile

  If bCancel Then GoTo ExitProcedure

6. Draw chart

We define the chart series data as in the earlier program. We select a blank cell so the chart we insert has no data plotted in it.

We insert the chart using the Excel 2007 syntax, so the code runs as expected in Excel 2007 and 2010. After we assign the selected chart type in Excel 2013, we apply the default Excel 2013 chart style for that chart type, to obtain the nicer Excel 2013 styles.

One by one we add each series, and if there are more than one series, we add a legend to our chart.

  '' define some series parameters
  If IsEmpty(vX) Then
    nX = 0
  Else
    nX = UBound(vX, 1) + 1 - LBound(vX, 1)
  End If
  nY = UBound(vY, 1) + 1 - LBound(vY, 1)
  nSrs = nY
  If nX > nY Then nSrs = nX
  If bFirstRowHeaders Then
    Set rCht = rng.Offset(1).Resize(iRows - 1)
  Else
    Set rCht = rng
  End If
  
  '' select blank cell before inserting chart
  rng.Offset(iRows + 1, iCols + 1).Resize(1, 1).Select
  
  Set cht = ws.Shapes.AddChart.Chart '' Excel 2007+
  
  '' chart type and style
  With cht
    If myChartType <> 0 Then
      .ChartType = myChartType
      If Val(Application.Version) >= 15 Then
        '' Excel 2013 ChartStyles
        If iChartStyle > 0 Then
          .ChartStyle = iChartStyle
        End If
      End If
    End If
    
    '' add series
    For iSrs = 1 To nSrs
      Set srs = .SeriesCollection.NewSeries
      With srs
        ' X values
        If nX = 0 Then
          ' no X values specified
        ElseIf nX = 1 Then
          ' all series share X values
          .XValues = rCht.Columns(CLng(vX(0, 0)))
        Else
          ' each series has unique X values
          .XValues = rCht.Columns(CLng(vX(iSrs - 1, 0)))
        End If
        ' Y values
        If nY = 1 Then
          ' all series share Y values
          .Values = rCht.Columns(CLng(vY(0, 0)))
        Else
          ' each series has unique Y values
          .Values = rCht.Columns(CLng(vY(iSrs - 1, 0)))
        End If
        ' series name
        If bFirstRowHeaders Then
          If nSrs = nY Then
            .Name = "=" & rng.Cells(1, CLng(vY(iSrs - 1, 0))). _
                Address(True, True, xlA1, True)
          ElseIf nSrs = nX Then
            .Name = "=" & rng.Cells(1, CLng(vX(iSrs - 1, 0))). _
                Address(True, True, xlA1, True)
          End If
        End If
      End With
    Next
    
    If iSrs > 1 Then
      .HasLegend = True
    End If
  End With

7. Save file

Let’s select cell A1 before saving, just to satisfy our obsession with an orderly worksheet.

  ws.Range("A1").Select
  wb.Save

UserForm

The UserForm looks much like the earlier version, with the addition of a Default “label button” (a label with a raised appearance that functions as a button). This uses the first column of the CSV file as the chart’s X values, and each subsequent column as Y values for another series in the chart.

There also is a Cancel button if the user decides to bail out.

The lstChartType listbox now has three columns, two hidden columns for the chart type codes and the newly added chart style codes, and a visible column for the chart type names. This simply requires changing the ColumnWidths property to 0 pt;0 pt;112 pt. There is a new ChartStyles property used to get the appropriate Excel 2013 chart style codes for the selected chart type.

There are many changes to the code that make things run more smoothly.

Clicking the OK button will invoke some validation of selected columns, to ensure that the selected data is appropriate for the selected chart type. Data entry controls will be shaded light red if the data is not correctly validated and a message to the user will explain the problem.

Clicking the Cancel button sets a variable so that our code knows when the user canceled the process. In addition, code is added so that clicking the red X in the top right corner of the dialog doesn’t lead to error messages.

The X label button will do a little thinking before adding selected columns to the X listbox: if the chart type is a scatter chart, all selected columns will be added as X value columns, but if not, the list of X value columns will be cleared and only the first selected column will be added.

New variables in the UserForm module:

Dim mbCancel As Boolean
Const miBAD_COLOR As Long = &HC0C0FF
Const miGOOD_COLOR As Long = &H80000005

Validation added to the OK button:

Private Sub btnOK_Click()
  Dim bValid As Boolean
  Dim sInvalid As String
  
  bValid = True
  mbCancel = False
  
  'validate
  Me.lstX.BackColor = miGOOD_COLOR
  Me.lstY.BackColor = miGOOD_COLOR
  If Me.lstY.ListCount < 1 Then
    bValid = False
    sInvalid = "There must be at least one Y range in any chart."
    Me.lstY.BackColor = miBAD_COLOR
  End If
  Select Case Me.ChartType
    Case -4169, 75, 74
      If Me.lstX.ListCount = Me.lstY.ListCount Then
      ElseIf Me.lstX.ListCount <= 1 And Me.lstY.ListCount > 0 Then
      ElseIf Me.lstY.ListCount = 1 And Me.lstX.ListCount > 0 Then
      Else
        bValid = False
        Me.lstX.BackColor = miBAD_COLOR
        Me.lstY.BackColor = miBAD_COLOR
        sInvalid = sInvalid & "For a " & Me.lstChartTypes.List _
            (Me.lstChartTypes.ListIndex, 1) & " chart, there must be" & vbNewLine
        sInvalid = sInvalid & "- equal numbers of X and Y ranges," & vbNewLine
        sInvalid = sInvalid & "- at most 1 X range and multiple Y ranges, or" _
            & vbNewLine
        sInvalid = sInvalid & "- multiple X ranges and one Y range." & vbNewLine
      End If
    Case Else
      If Me.lstX.ListCount > 1 Then
        bValid = False
        sInvalid = sInvalid & "There can be at most one X range for a " & _
            Me.lstChartTypes.List(Me.lstChartTypes.ListIndex, 1) & " chart." & vbNewLine
        Me.lstX.BackColor = miBAD_COLOR
      End If
  End Select
  
  If bValid Then
    Me.Hide
  Else
    'Me.Repaint
    MsgBox sInvalid, vbExclamation, "Invalid Data Selections"
  End If
End Sub

The Cancel button code:

Private Sub btnCancel_Click()
  mbCancel = True
  Me.Hide
End Sub

This code ensures that clicking the red X closes the form as if the user had clicked the Cancel button:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
  If CloseMode = 0 Then
    Cancel = True
    btnCancel_Click
  End If
End Sub

Here’s the code for the Default label button:

Private Sub lblbtnDefault_Click()
  Dim iLst As Long
  
  Me.lstX.Clear
  Me.lstY.Clear

  Me.lstX.AddItem 1
  For iLst = 2 To Me.lstChartData.ListCount
    Me.lstY.AddItem iLst
  Next
End Sub

Here is the updated X label button code:

Private Sub lblbtnX_Click()
  Dim iLst As Long
  Dim myChartType As XlChartType
  
  myChartType = Me.ChartType
  For iLst = 1 To Me.lstChartData.ListCount
    If Me.lstChartData.Selected(iLst - 1) Then
      Select Case myChartType
        Case -4169, 75, 74 ' scatter
        Case Else ' not scatter
          Me.lstX.Clear
      End Select
      Me.lstX.AddItem iLst
      Select Case myChartType
        Case -4169, 75, 74 ' scatter
        Case Else ' not scatter
          Exit For
      End Select
    End If
  Next
End Sub

ChartStyle property

Public Property Get ChartStyle() As Long
  With Me.lstChartTypes
    If .ListIndex > -1 Then
      ChartStyle = CLng(.List(.ListIndex, 1))
    End If
  End With
End Property

Code Module MChartFromCSVFile

Here is the complete listing of MChartFromCSVFile:

Option Explicit

'Callback for btnProcessCSV onAction
Sub RX_OpenCSVFileAndPlotData(control As IRibbonControl)
  OpenCSVFileAndPlotData
End Sub

Sub OpenCSVFileAndPlotData()
  Dim sCSVFullName As String, sWbkFullName As String, sWbkFileName As String, _
      sFileRoot As String
  Dim iFile As Long
  Dim bOpenFile As Boolean
  Dim wb As Workbook, wbTest As Workbook, ws As Worksheet, rng As Range, vRng As Variant
  Dim rCht As Range, cht As Chart, srs As Series
  Dim iRows As Long, iCols As Long, iRow As Long, iCol As Long
  Dim sTemp As String
  Dim vChartData As Variant
  Dim bFirstRowHeaders As Boolean
  Dim myChartType As XlChartType, iChartStyle As Long
  Dim vX As Variant, vY As Variant
  Dim iX As Long, iY As Long, iSrs As Long
  Dim nX As Long, nY As Long, nSrs As Long
  Dim bCancel As Boolean
  Dim iMsgBoxResult As VbMsgBoxResult
  Dim frmChartFromCSVFile As FChartFromCSVFile
  
  ' 1. Get CSV file name
  Do
    sCSVFullName = Application.GetOpenFilename("CSV Files (*.csv), *.csv", , _
        "Select a CSV file", , False)
    
    '' check whether user cancels
    If sCSVFullName = "False" Then GoTo ExitProcedure
    
    '' check that file isn't already open
    bOpenFile = False
    For Each wbTest In Workbooks
      If wbTest.FullName = sCSVFullName Then
        bOpenFile = True
        Exit For
      End If
    Next
    If bOpenFile Then
      iMsgBoxResult = MsgBox("Data file is already open. Continue with open file?", _
          vbYesNo + vbQuestion, "File Already Open")
      If iMsgBoxResult = vbYes Then
        Set wb = wbTest
        Exit Do
      End If
    End If
    
    '' ensure that it's a CSV file
    If LCase$(Right$(sCSVFullName, 4)) <> ".csv" Then
      iMsgBoxResult = MsgBox("That doesn't appear to be a CSV file. Continue anyway?", _
          vbYesNo + vbQuestion, "Not a CSV File")
      If iMsgBoxResult = vbYes Then Exit Do
    Else
      Exit Do
    End If
  Loop
  
  ' 2. Open CSV file
  If wb Is Nothing Then
    Workbooks.Open sCSVFullName
    Set wb = ActiveWorkbook
  End If
  
  ' 3. Save as workbook
  sFileRoot = Left$(sCSVFullName, InStrRev(sCSVFullName, ".") - 1)
  sWbkFullName = sFileRoot & ".xlsx"
  iFile = 0
  Do
    '' check for duplicate file (already open)
    bOpenFile = False
    For Each wbTest In Workbooks
      If wbTest.FullName = sWbkFullName Then
        bOpenFile = True
        Exit For
      End If
    Next
    
    '' check for duplicate file (in folder)
    If Not bOpenFile Then
      If Len(Dir(sWbkFullName)) = 0 Then Exit Do
    End If
    
    '' increment file name
    iFile = iFile + 1
    sWbkFullName = sFileRoot & "_" & CStr(iFile) & ".xlsx"
  Loop
  
  wb.SaveAs sWbkFullName, xlWorkbookDefault
  
  ' 4. Parse file
  Set ws = wb.Worksheets(1)
  Set rng = ws.UsedRange
  vRng = rng.Value2
  iRows = rng.Rows.Count
  iCols = rng.Columns.Count
  
  '' info to display: column number, first few rows of column
  ReDim vChartData(1 To iCols, 1 To 2)
  For iCol = 1 To iCols
    vChartData(iCol, 1) = iCol
    sTemp = ""
    For iRow = 1 To 4
      If iRow > iRows Then Exit For
      sTemp = sTemp & vRng(iRow, iCol) & ", "
    Next
    sTemp = Left$(sTemp, Len(sTemp) - 2)
    vChartData(iCol, 2) = sTemp
  Next
  
  '' first row header labels if all cells in first row are text
  For iCol = 1 To iCols
    bFirstRowHeaders = Not IsNumeric(vRng(1, iCol))
    If Not bFirstRowHeaders Then Exit For
  Next
  
  ' 5. Show dialog (get chart type, X values, Y values)
  Set frmChartFromCSVFile = New FChartFromCSVFile
  With frmChartFromCSVFile
    '' pass in information we know
    .ChartData = vChartData
    .FirstRowHeaders = bFirstRowHeaders
    
    .Show
    
    '' check whether user canceled
    bCancel = .Cancel
    If Not bCancel Then
      '' get information selected by user
      myChartType = .ChartType
      iChartStyle = .ChartStyle
      bFirstRowHeaders = .FirstRowHeaders
      vX = .Xcolumns
      vY = .YColumns
    End If
  End With
  Unload frmChartFromCSVFile
  
  If bCancel Then GoTo ExitProcedure
  
  ' 6. Draw chart
  '' define some series parameters
  If IsEmpty(vX) Then
    nX = 0
  Else
    nX = UBound(vX, 1) + 1 - LBound(vX, 1)
  End If
  nY = UBound(vY, 1) + 1 - LBound(vY, 1)
  nSrs = nY
  If nX > nY Then nSrs = nX
  If bFirstRowHeaders Then
    Set rCht = rng.Offset(1).Resize(iRows - 1)
  Else
    Set rCht = rng
  End If
  
  '' select blank cell before inserting chart
  rng.Offset(iRows + 1, iCols + 1).Resize(1, 1).Select
  
  Set cht = ws.Shapes.AddChart.Chart '' Excel 2007+
  ''Set cht = ws.Shapes.AddChart2.Chart '' Excel 2013 only
  
  '' chart type and style
  With cht
    If myChartType <> 0 Then
      .ChartType = myChartType
      If Val(Application.Version) >= 15 Then
        '' Excel 2013 ChartStyles
        If iChartStyle > 0 Then
          .ChartStyle = iChartStyle
        End If
      End If
    End If
    
    '' add series
    For iSrs = 1 To nSrs
      Set srs = .SeriesCollection.NewSeries
      With srs
        ' X values
        If nX = 0 Then
          ' no X values specified
        ElseIf nX = 1 Then
          ' all series share X values
          .XValues = rCht.Columns(CLng(vX(0, 0)))
        Else
          ' each series has unique X values
          .XValues = rCht.Columns(CLng(vX(iSrs - 1, 0)))
        End If
        ' Y values
        If nY = 1 Then
          ' all series share Y values
          .Values = rCht.Columns(CLng(vY(0, 0)))
        Else
          ' each series has unique Y values
          .Values = rCht.Columns(CLng(vY(iSrs - 1, 0)))
        End If
        ' series name
        If bFirstRowHeaders Then
          If nSrs = nY Then
            .Name = "=" & rng.Cells(1, CLng(vY(iSrs - 1, 0))). _
                Address(True, True, xlA1, True)
          ElseIf nSrs = nX Then
            .Name = "=" & rng.Cells(1, CLng(vX(iSrs - 1, 0))). _
                Address(True, True, xlA1, True)
          End If
        End If
      End With
    Next
    
    If iSrs > 1 Then
      .HasLegend = True
    End If
  End With
  
  ' 7. Save file
  ws.Range("A1").Select
  wb.Save
  
ExitProcedure:
  
End Sub

blah

UserForm Module FChartFromCSVFile

Here is the complete listing of FChartFromCSVFile:

Option Explicit

Dim mbCancel As Boolean
Const miBAD_COLOR As Long = &HC0C0FF
Const miGOOD_COLOR As Long = &H80000005

Private Sub btnCancel_Click()
  mbCancel = True
  Me.Hide
End Sub

Private Sub btnOK_Click()
  Dim bValid As Boolean
  Dim sInvalid As String
  
  bValid = True
  mbCancel = False
  
  'validate
  Me.lstX.BackColor = miGOOD_COLOR
  Me.lstY.BackColor = miGOOD_COLOR
  If Me.lstY.ListCount < 1 Then
    bValid = False
    sInvalid = "There must be at least one Y range in any chart."
    Me.lstY.BackColor = miBAD_COLOR
  End If
  Select Case Me.ChartType
    Case -4169, 75, 74
      If Me.lstX.ListCount = Me.lstY.ListCount Then
      ElseIf Me.lstX.ListCount <= 1 And Me.lstY.ListCount > 0 Then
      ElseIf Me.lstY.ListCount = 1 And Me.lstX.ListCount > 0 Then
      Else
        bValid = False
        Me.lstX.BackColor = miBAD_COLOR
        Me.lstY.BackColor = miBAD_COLOR
        sInvalid = sInvalid & "For a " & Me.lstChartTypes.List _
            (Me.lstChartTypes.ListIndex, 1) & " chart, there must be" & vbNewLine
        sInvalid = sInvalid & "- equal numbers of X and Y ranges," & vbNewLine
        sInvalid = sInvalid & "- at most 1 X range and multiple Y ranges, or" _
            & vbNewLine
        sInvalid = sInvalid & "- multiple X ranges and one Y range." & vbNewLine
      End If
    Case Else
      If Me.lstX.ListCount > 1 Then
        bValid = False
        sInvalid = sInvalid & "There can be at most one X range for a " & _
            Me.lstChartTypes.List(Me.lstChartTypes.ListIndex, 1) & " chart." & vbNewLine
        Me.lstX.BackColor = miBAD_COLOR
      End If
  End Select
  
  If bValid Then
    Me.Hide
  Else
    'Me.Repaint
    MsgBox sInvalid, vbExclamation, "Invalid Data Selections"
  End If
End Sub

Private Sub lblbtnDefault_Click()
  Dim iLst As Long
  
  Me.lstX.Clear
  Me.lstY.Clear

  Me.lstX.AddItem 1
  For iLst = 2 To Me.lstChartData.ListCount
    Me.lstY.AddItem iLst
  Next
End Sub

Private Sub lblbtnReset_Click()
  Me.lstX.Clear
  Me.lstY.Clear
End Sub

Private Sub lblbtnX_Click()
  Dim iLst As Long
  Dim myChartType As XlChartType
  
  myChartType = Me.ChartType
  For iLst = 1 To Me.lstChartData.ListCount
    If Me.lstChartData.Selected(iLst - 1) Then
      Select Case myChartType
        Case -4169, 75, 74
        Case Else
          Me.lstX.Clear
      End Select
      Me.lstX.AddItem iLst
      Select Case myChartType
        Case -4169, 75, 74
        Case Else
          Exit For
      End Select
    End If
  Next
End Sub

Private Sub lblbtnY_Click()
  Dim iLst As Long

  For iLst = 1 To Me.lstChartData.ListCount
    If Me.lstChartData.Selected(iLst - 1) Then
      Me.lstY.AddItem iLst
    End If
  Next
End Sub

Public Property Let ChartData(vData As Variant)
  Me.lstChartData.List = vData
End Property

Property Let FirstRowHeaders(bFirstRowHeaders As Boolean)
  Me.chkFirstRowHeaders.Value = bFirstRowHeaders
End Property

Property Get FirstRowHeaders() As Boolean
  FirstRowHeaders = Me.chkFirstRowHeaders.Value
End Property

Public Property Get ChartType() As XlChartType
  With Me.lstChartTypes
    If .ListIndex > -1 Then
      ChartType = CLng(.List(.ListIndex, 0))
    End If
  End With
End Property

Public Property Get ChartStyle() As Long
  With Me.lstChartTypes
    If .ListIndex > -1 Then
      ChartStyle = CLng(.List(.ListIndex, 1))
    End If
  End With
End Property

Public Property Get Xcolumns() As Variant
  Xcolumns = Me.lstX.List
End Property

Public Property Get YColumns() As Variant
  YColumns = Me.lstY.List
End Property

Public Property Get Cancel() As Boolean
  Cancel = mbCancel
End Property

Private Sub UserForm_Initialize()
  Dim vChartTypes As Variant
  
  vChartTypes = ThisWorkbook.Names("ChartTypes").RefersToRange.Value2
  Me.lstChartTypes.List = vChartTypes
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
  If CloseMode = 0 Then
    Cancel = True
    btnCancel_Click
  End If
End Sub

Add-In with Working Code

You can download the advanced add-in My_CSV_Data_Processor.xlam to see all of this code in one place, and to see how it works.

Using the Add-In

You could open the add-in like a regular workbook, by double-clicking on it in Windows Explorer, dragging it onto the Excel window from Windows, or by using Excel’s File Open dialog. Alternatively you could install it as an add-in, so it is always available:

  • In Excel, click on the File tab or in Excel 2007 the big round Office button in the top left corner
  • Select Options (or Excel Options) from the list
  • Click Add-Ins in the dialog
  • Click the Go button
  • Click Browse and navigate to the folder containing .xlam add-in file
  • Select the .xlam file and click OK a couple times to return to Excel

However the add-in is used, a new button is added to the end of the Data tab on the ribbon. Click the button to launch the code.

 

Peltier Tech Charts for Excel

Comments

  1. Thank you for your posts – very useful!
    Please, have you got any example of dynamic charts on a VBA userform (Excel 2010)? I am trying to develop an interactive tool so users can change the charts on userform (set time period, set type of main error (+ all its suberrors will be displayed), then comment it through a textbox on the form. Then print the report as a word document.

  2. Thank you for your posts – very very useful!
    But I need your help/advice.. if a few row of csv file is not a number, how to reflect this situation into the Code Module MChartFromCSVFile?

  3. Nico –

    What does the data look like (could you post a representative few rows)? How would you like the code to treat the nonnumeric data?

Trackbacks

  1. […] The next blog post has a number of enhancements that make things easier for the user and prevent various warning messages: VBA: An Advanced Add-in to Open a CSV File and Create Chart with Data Specified by User. […]

Speak Your Mind

*

Peltier Tech Charts for Excel 3.0