Examples of Visual Basic Code for Controlling Excel

A major reason to program Excel Macros is to get the spreadsheet to do something.  This post is a goldmine of useful Excel Visual Basic code for talking to Excel.  You can get use the routines stand-alone.  Alternatively, you can save retyping or copy-pasting: Get the file Macros.xlsm free of charge from us.

1.        Table of Range Names

This code (in Macros.xlsm Module RangeNameTable) demonstrates:  Getting Range Names, placing values in cells, moving around, looping, and some string manipulation:

Sub RangeNameTable()   ' Simulate Lotus 1-2-3 /Range Name Table
  ' Place your cell selector where you want to start.
  Dim nam As Name, Tmp$
  ActiveCell.FormulaR1C1 = "Range Name Table"
  Selection.Font.Bold = True
  ActiveCell.Offset(1, 0).Range("A1").Select    ' Down One Cell
  For Each nam In Names
'   On Error Resume Next
    ActiveCell.FormulaR1C1 = "'" & nam.Name     ' Name
    ActiveCell.Offset(0, 1).Range("A1").Select  ' Right one cell
    Tmp$ = Mid$(nam.RefersTo, 2)      ' Cell ref (cut off "=")
    Tmp$ = Replace$(Tmp$, "$", "")    ' Remove $ signs
    ActiveCell.FormulaR1C1 = Tmp$
    ActiveCell.Offset(1, -1).Range("A1").Select ' Down, Left one
  Next nam
  ActiveCell.Clear
End Sub

2.        Unhide All Sheets

This code (in Macros.xlsm Module Utilities) demonstrates: Making all (hidden) Sheets visible, using a For…Next loop:

Sub UnhideAll()   ' Unhide All sheets
' Macro 15/01/2001 by Rick Raubenheimer

  Dim I%
  For I% = 1 To Sheets().Count
    Sheets(I%).Visible = True
  Next I%
End Sub

3.        Protect or Unprotect Sheets

This code (in Macros.xlsm Module Utilities) demonstrates: Looping through Sheets and password protecting, or unprotecting them.  The password is “x“.

Sub ProtAllON()     ' Turn ON Protection for all sheets
  Dim I%
  For I% = Sheets.Count To 1 Step -1
    Sheets(I%).Select
    ActiveSheet.Protect "x", True, True, True, True
    Range("D5").Select
  Next I%
End Sub

Sub ProtAllOFF()    ' Turn OFF Protection for all sheets
  Dim I%
  For I% = 1 To Sheets.Count
    Sheets(I%).Select
    ActiveSheet.Unprotect "x"
  Next I%
End Sub

4.        Copy List of Sheet Names

This more complex code (in Macros.xlsm Module Utilities) demonstrates:  Getting Sheet Names, looping, and some string manipulation.  Unlike the previous example, it activates every sheet in turn.  It puts the resulting string in an InputBox where you can copy it using Ctrl+C:

Sub GetSheetNames()   ' Run thru file and List Sheet Names.
' Macro recorded 14/12/2000 by Rick Raubenheimer
  Dim Names$, I%
 
  On Error GoTo GotNames
 
  For I% = 1 To Sheets.Count
    Sheets(I%).Select
    Range("A1").Select
    Names$ = Names$ & ActiveSheet.Name & ", "
'   ActiveSheet.Next.Select       (alternative, commented out)  Next I%
 
  InputBox "Sheet names are: (Ctrl+C to copy)", , Names$
  Exit Sub

GotNames: MsgBox Error$
End Sub

5.        Refer to a Chart on a Sheet

This code is in the Macros.xlsm Module Form_Controls.  It demonstrates how to manipulate a Chart that is on a Worksheet (the ChartObjects reference).  The code runs from the horizontal scroll bar on Sheet2 (Form Controls) and gets its value from the Named Range, “Rotation”:

Sub ScrollBar7_Change()   ' Horiz Scroll Bar - Chart Rotation:
  Worksheets("Form Controls").ChartObjects(1).Chart.Rotation _
    = Range("Rotation")
End Sub

We linked the Form Control scrollbar to the Named Range “Rotation” as follows:

1. Right-click the Form Control
2. From the pop-up menu choose “Format Control”
3. On the dialog box’s “Control” Tab, set Cell link = Rotation (or cell M20).

6.        Call a Worksheet Function in Visual Basic

This code is in Macros.xlsm, in the Sheet3 code for text box txtName.  The routine converts input text to upper case, lower case, and proper case (initial capitals).  But Visual Basic has no function for the latter.  So, it also shows to use an Excel Worksheet Function when Visual Basic does not have the required function.

Data comes from the text box txtName (note that the “value” property is optional as it is the default).  The results go into the Named Ranges “Upcase”, “Downcase”, and “Proper”.

Private Sub txtName_Change()    ' Text Box Demo
  Range("Upcase").Value = UCase$(txtName.Value)
  Range("Downcase") = LCase$(txtName)
  ' Visual Basic has no "Proper" Function,
  ' so call the Worksheet Function:
  Range("Proper") = Application.WorksheetFunction.Proper(txtName)
End Sub

7.        Move/Select Relative and Absolute, Use the END key

This code fragment demonstrates various ways to navigate around the worksheet from code.  We show how to go to (or select to) a specific sheet or cell, use the End, Down key sequence, and go to a cell relative to the current cell.  Test it well – you will find it valuable to know how the End key works in all cases.

' Go to specific sheet:
Sheets("Totals").Select
' Go to specific cell:
Range("B7").Select

' Use the END key to move:
Selection.End(xlDown).Select          ' End Down

' Move Cell Selector (Rows, Cols) Relative:
ActiveCell.Offset(0, -1).Select       ' Move 1 Left
ActiveCell.Offset(2, 0).Select        ' Move 2 down

' Use the END key to select Down:
Range(Selection, Selection.End(xlDown)).Select
' Select 0 more rows Down, 2 more columns Right:
Range(Selection, ActiveCell.Offset(0, 2)).Select

You need a filled range (a contiguous group of cells all containing data) to demonstrate using the End key.