RefEdit Entries

Recently I've been working on creating a ribbon tab for Excel 2007 that interfaces with and controls several of the add-ins that I use for work. The Goal Seek Tool is one of those add-ins. I created this tool to allow the entry of a single-cell range or value when performing the goal-seek function.

In November 2008 I wrote a short post about my Goal Seek tool. In my first version of that tool, I used a RefEdit control to enter a range and a textbox to enter a value. In a comment to that post, Jon Peltier noted that the textbox could be eliminated and the RefEdit control could be programmed to accept either a range or text entry.

After a lot of time searching the internet, I finally found an example of how this functionality could work on the TM Consulting site. It looks like this:

When wanting to enter a range with RefEdit and assuming the RefEdit control is named "RefEdit1", use

Range(RefEdit1) 

When wanting to enter a value with RefEdit, use

Range(RefEdit1).value 

When wanting to validate the entries made into RefEdit, you can use examples like:

To validate that a range contains a formula, use

HasFormula(Range(RefEdit1)) 

To validate that a range contains a number, use

IsNumeric(RefEdit1)

VBA Fireworks Model from AJP Excel Information

Recently I had the opportunity to build Andy Pope's fireworks display into a line chart that I produce each month. Fireworks were requested because 2009 was the best year on record for a particular business unit at work. When I presented the operating results the animation was a big hit.

Once you download the file you'll find that Andy's display utilizes an XY Scatter chart and VBA code with trigonometry functions to produce the fireworks. Because the code is unprotected you can easily load the modules into your own project and then customize the code as you wish. Even if you don't have an interest in using the display, the file is worth downloading just to study how the model was developed. Thanks Andy for making this available.

Turn off Automatic Hyperlinks

Does it annoy you when Excel automatically turns an email or web address into a hyperlink when you want it entered as text? In Excel 2007 you can prevent this from happening by clicking on the Office button and going to Excel Options -> Proofing -> Deselect the option titled "Ignore Internet and file addresses".

The The Spreadsheet Page has a tip titled Removing or Avoiding Automatic Hyperlinks that covers this very topic. The tip provides the following macro which will allow you to instantly turn all of your hyperlinks into text. The macro really helped me as I had a sheet with over 100 links to zap.

Sub ZapHyperlinks() 
    Cells.Hyperlinks.Delete
End Sub

As I'm just making the switch from Excel 2003 to 2007 it made me think . . . what features do Excel users automatically turn on or off when moving to a different version? Understanding that everyone has their own preferences, I'd like to hear about those features that are a "must have" or that you immediately shut off.

VBA Array to Change Line Chart Colors

Part of my responsibilities at work is building the end-of-month financial packet that is presented to management. That packet includes a variety of line charts that show trends in financial and statistical metrics. Many of the graphs start with a base year and require adding an additional line as the year turns over. For example, in 2009 and given a base year of 2006 each line chart would have five lines (2006, 2007, 2008, 2009 plan, and 2009 actual). Now that the year has turned over, each graph will have six lines (2006, 2007, 2008, 2009, 2010 plan, and 2010 actual).

This may or may not be the most efficient process, but I prefer to keep the line series order as follows:

• Series 1 = Current Year (2010) -> line color = red
• Series 2 = Current Year Plan (2010) -> line color = blue
• Series 3 = Prior Year (2009) -> line color = green
• Series 4 = Prior Year - 1 (2008) -> line color = silver
• Series 5 = Prior Year - 2 (2007) -> line color = silver
• Series 6 = Prior Year - 3 (2006) -> line color = silver

This logic can present a problem in that as an additional series is added, the series order needs to be renumbered and the line colors have to be redone. To make the process easier, we use the following macro to change the series colors. It uses an array to store the color index numbers for each series.

Sub Color_Series()

    Dim arrColors(1 To 6) As Integer
    
    Dim Cht As ChartObject
    
    Dim i As Integer
     
    arrColors(1) = 3    'Series 1 - Red = Current Year (2010)
    arrColors(2) = 5    'Series 2 - Blue = Current Year Plan (2010)
    arrColors(3) = 10  'Series 3 - Green = Prior Year (2009)
    arrColors(4) = 16  'Series 4 - Silver = Prior Year (2008)
    arrColors(5) = 15  'Series 5 - Silver = Prior Year (2007)
    arrColors(6) = 14  'Series 6 - Silver = Prior Year (2006)
    
    Set Cht = Worksheets("Sheet1").ChartObjects("Chart01")
    
    For i = LBound(arrColors) To UBound(arrColors)
    
        Cht.Chart.SeriesCollection(i).Border.ColorIndex = arrColors(i)
        
    Next i
    
End Sub

Rather than looping through an array, you could just as easily refer to each of the series individually. To do so, the code would look like this:

Sub Color_Series()

    Dim Cht As ChartObject
    
    'Series 1 - ColorIndex = 3 = Red = Current Year (2010)
    'Series 2 - ColorIndex = 5 = Blue = Current Year Plan (2010)
    'Series 3 - ColorIndex = 10 = Green = Prior Year (2009)
    'Series 4 - ColorIndex = 16 = Silver = Prior Year (2008)
    'Series 5 - ColorIndex = 15 = Prior Year (2007)
    'Series 6 - ColorIndex = 14 = Silver = Prior Year (2006)
    
    Set Cht = Worksheets("Sheet1").ChartObjects("Chart01")
    
    Cht.Chart.SeriesCollection(1).Border.ColorIndex = 3
    Cht.Chart.SeriesCollection(2).Border.ColorIndex = 5
    Cht.Chart.SeriesCollection(3).Border.ColorIndex = 10
    Cht.Chart.SeriesCollection(4).Border.ColorIndex = 16
    Cht.Chart.SeriesCollection(5).Border.ColorIndex = 15
    Cht.Chart.SeriesCollection(6).Border.ColorIndex = 16
        
End Sub

VBA Function to Screen for Certain Charts

Until late last year I've primarily worked with Excel 2003 because that's what we use at work. However, that changed over the holidays because my home computer crashed and the new one came with Excel 2007. I was trying to wait until Excel 2010 was available before buying a new computer but the old machine just couldn't make it that long.

It seems like most of what I've read regarding the Excel 2007 ribbon has been negative. However, after using Excel 2007 for a couple of weeks my initial impressions are that I like it. Although the ribbon can't be modified as easy as the toolbars were in previous versions, having the ability to create your own ribbon tabs makes up for that problem for me. And having the ability to customize the quick access toolbar makes up for inefficiencies in the placement of the ribbon commends.

Now that I've made the move to 2007, I need to rebuild several add-ins that I've created to interface with the ribbon. One of the add-ins that I've created is for working with embedded charts only. Built into that add-in is a function written by John Walkenbach that alerts the user if he or she failed to select a chart. After screening for a chart selection, I've added a second chart-screening function that utilizes a case statement to alert the user if an "improper" chart type is selected. In the example, an improper chart is by my definition only. That second chart-screening function follows:

First, declare an optional public variable containing the name of the application (add-in).

Option Explicit
Public Const APPNAME As String = "Position Chart Labels"

Next, provide the code that triggers the user form. "ValidContext" references the chart screening function below.

Sub MoveChartLabels()
    If ValidContext(True) Then frmMoveLabels.Show
End Sub

Finally, provide the chart screening function. If the chart type appears as a line in the code below the function considers the chart to be valid. If not, the function kicks out an error message. The function works with combination charts because it evaluates each individual chart series for the accepted chart type.

Private Function ValidContext(ChtType) As Boolean
   
    Dim Sr As Series
    Dim Srs As SeriesCollection
    
    Const MsgChtType As String = "Invalid chart type."
        
    Set Srs = ActiveChart.SeriesCollection
    
    For Each Sr In Srs
     
        Select Case Sr.ChartType
            
            'Accepted Line Charts
            Case xlLine: ValidContext = True
            Case xlLineStacked: ValidContext = True
            Case xlLineStacked100: ValidContext = True
            Case xlLineMarkers: ValidContext = True
            Case xlLineMarkersStacked: ValidContext = True
            Case xlLineMarkersStacked100: ValidContext = True
            
            'Accepted Column Charts
            Case xlColumnClustered: ValidContext = True
            Case xlColumnStacked: ValidContext = True
            Case xlColumnStacked100: ValidContext = True
        
            'Accepted Bar Charts
            Case xlBarClustered: ValidContext = True
            Case xlBarStacked: ValidContext = True
            Case xlBarStacked100: ValidContext = True
                    
            'Accepted Area Charts
            Case xlArea: ValidContext = True
            Case xlAreaStacked: ValidContext = True
            Case xlAreaStacked100: ValidContext = True
            
            'Accepted XY Scatter Charts
            Case xlXYScatter: ValidContext = True
            Case xlXYScatterSmooth: ValidContext = True
            Case xlXYScatterSmoothNoMarkers: ValidContext = True
            Case xlXYScatterLines: ValidContext = True
            Case xlXYScatterLinesNoMarkers: ValidContext = True
    
            'Accepted Pie Charts
            Case xlPie: ValidContext = True
            Case xlPieExploded: ValidContext = True
            Case xlPieOfPie: ValidContext = True
            Case xlBarOfPie: ValidContext = True
            
            Case Else
                ValidContext = False
                MsgBox MsgChtType, vbCritical, APPNAME
                Exit Function
        
        End Select

    Next Sr
    
End Function