Page 233 - Microsoft Office Excel 2003 Programming Inside Out

Manipulating Data with VBA

Note Although this version of the procedure uses a Select Case statement with included
code to perform each summary calculation, you could choose to create a function for each
summary operation and call the function from within the corresponding Case statement.
You might also choose to let the user type in the number of the summary operation they
want to perform instead of the name. (To avoid spelling and capitalization errors, the values
must match exactly.)

Public Sub Summarize()
Dim intColNumber As Integer
Dim strOperation, strCriteria As String
Dim sngResult As Single
MsgBox ("Select a cell within the table you want to summarize." _
& "Type the number, not the letter, representing the column of the " _
& "cells you want to summarize.")
intColNumber = InputBox("Which column do you want to summarize?")
strOperation = InputBox("Which summary operation do you want to perform?" _
& " The options are Sum, SumIF, Max, Min, Count, CountA, CountBlank, " _
& "CountIF, Average, Mode, StDev. (Type them exactly as they appear.)") Chapter 9
With ActiveCell.CurrentRegion.Columns(intColNumber)
Select Case strOperation
Case "Sum"
sngResult = Application.WorksheetFunction.Sum(.Cells)
MsgBox ("The sum of the column is " & sngResult & ".")
Case "SumIF"
strCriteria = InputBox("Type a criteria for the method by " _
& "typing a number alone or preceded by one of the operators " _
& ">, <, or =.")
sngResult = Application.WorksheetFunction.SumIf(.Cells, strCriteria)
MsgBox ("The sum of the values is " & sngResult & ".")
Case "Max"
sngResult = Application.WorksheetFunction.Max(Cells)
MsgBox ("The maximum value in the column is " & sngResult & ".")
Case "Min"
sngResult = Application.WorksheetFunction.Min(.Cells)
MsgBox ("The minimum value in the column is " & sngResult & ".")
Case "Count"
sngResult = Application.WorksheetFunction.Count(.Cells)
MsgBox ("The number of cells is " & sngResult & ".")
Case "CountA"
sngResult = Application.WorksheetFunction.CountA(Cells)
MsgBox ("The number of non-blank cells is " & sngResult & ".")
Case "CountBlank"
sngResult = Application.WorksheetFunction.CountBlank(.Cells)
MsgBox ("The number of blank cells is " & sngResult & ".")
Case "CountIF"
strCriteria = InputBox("Type a criteria for the method by " _
& "typing a number alone or preceded by one of the operators " _
& ">, <, or =.")

207
Part 4 Advanced VBA