Logical Functions

Select Case Statements

Case statement has the functionality of a multiple ELSEIF statements. Use Select Case when the decision you want to arrive at depends on one variable or expression (known as text case) that has more different values that you need to evaluate. The syntax is:

Select Case Test_Expression
   Case condition_1
     statement_1
   Case condition_2
    statement_2
   ...
   Case condition_n
    statement_n
   Case Else
    statement_else
End Select

‘ If you have many different conditions, Elseif become unhandy, use Select Case instead. In this example, when the specified conditions are met, it populates the string text one cell to the right
Dim finalRow As Long, n As Long
finalRow = Cells(65536, 1).End(xlUp).Row
For n = 2 To finalRow
    Select Case Cells(n, 1).Value
        Case "Down"
            Cells(n, 1).Offset(, 1).Value = "delivery time is 4 to 5 days"
        Case "Priority"
            Cells(n, 1).Offset(, 1).Value = "delivery time is 8 to 10 days"
        Case "Regular"
            Cells(n, 1).Offset(, 1).Value = "delivery time is 14 days"
        Case Else
    End Select
Next n

Return To Top

IF-THEN-ELSE  function
The syntax for the IF-THEN-ELSE statement is: 

If condition_1 Then
     statement_1
  ElseIf condition_2 Then
     statement_2
  ElseIf condition_n Then
     statement_n
  Else
     statement_else
End If

’ An alternative to the Select Case statement. When the specified conditions are met, it populates the string text one cell to the right
Dim finalRow As Long, n As Long, r As String
finalRow = Cells(65536, 1).End(xlUp).Row
For n = 2 To finalRow
r = Cells(n, 1).Value
  If r = "01" Then
     Cells(n, 1).Offset(, 1).Value = "delivery time is 4 to 5 days"
  ElseIf r = "02" Then
     Cells(n, 1).Offset(, 1).Value = "delivery time is 8 to 10 days"
  ElseIf r = "03" Then
     Cells(n, 1).Offset(, 1).Value = "delivery time is 14 days"
  Else
  End If
Next n

’ This example loops through the Used Range in column A, and change the font color of the Used Range in first 2 columns as soon as a specified condition is made.
Dim iRow As Long
Dim rng As Range, rCell As Range
Sheets("Sheet1").Select
Set rng = Sheets("Sheet1").Range(Cells(2, "A"), Cells(Rows.Count, "A").End(xlUp))
For Each rCell In Range(Cells(1, "A"), Cells(Rows.Count, "A").End(xlUp))
    If Not rng.Find(what:="01", LookIn:=xlValues, LookAt:=xlWhole) Is Nothing Then
       rCell.Resize(1, 2).Font.ColorIndex = 3
    ElseIf Not rng.Find(what:="02", LookIn:=xlValues, LookAt:=xlWhole) Is Nothing Then
       rCell.Resize(1, 2).Font.ColorIndex = 5
    ElseIf Not rng.Find(what:="03", LookIn:=xlValues, LookAt:=xlWhole) Is Nothing Then
       rCell.Resize(1, 2).Font.ColorIndex = 7
    Else
       rCell.Resize(1, 2).Font.ColorIndex = 10
    End If
Next
 

’ If it finds any errors in the 1st sheet, it fills the cell background of the cells that have errors with red color, or else it displays a message to say that it finds no error. You need to always enable error handling so that a run-time error will not occur if the SpecialCells method doesn’t find any special cells. When SpecialCells give error, the Range variable rngSpecial will not be initialized. Using Not…Is Nothing method, you can see the action result when there are errors found in cells. Finally, set the variable names to Nothing dissociated the specified variables from the objects to which they are referred. It means that the variable now refers to nothing, but the variable name still exists in memory.
Dim ws As Worksheet, rngSpecial As Range, rngCell As Range
On Error Resume Next
Set ws = ActiveWorkbook.Sheets(1)
Set rngSpecial = ws.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
  If Not rngSpecial Is Nothing Then
    rngSpecial.Interior.ColorIndex = 3
  Else
    MsgBox "found no formula error in " & ws.Name
  End If

‘ to dissociate variables from the objects
Set rngSpecial = Nothing
Set rngCell = Nothing
Set ws = Nothing


Note: In the expression.SpecialCells(Type, Value) statement, if Type is either xlCellTypeConstants or xlCellTypeFormulas, this argument is used to determine which types of cells to include in the result. These values can be added together to return more than one type as shown in example below. The default is to select all constants or formulas, no matter what the type.

ActiveSheet.Cells.SpecialCells(xlCellTypeConstants, xlErrors + xlTextValues)

XlCellType can be one of these XlCellType constants :

XlSpecialCellsValue can be one of these XlSpecialCellsValue constants :
 

xlErrors
xlLogical
xlNumbers
xlTextValues

Return To Top

Return To Top

' Volatile function must be recalculated whenever calculation occurs in any cells
Application.Volatile

Select Case True
   Case Application.IsLogical(Cell): CellType = "Logical"
   Case Application.IsText(Cell): CellType = "Text"
   Case Application.IsErr(Cell): CellType = "Error"
   Case IsDate(Cell): CellType = "Date"
   Case InStr(1, Cell.Text, ":") <> 0: CellType = "Time"
   Case IsNumeric(Cell): CellType = "Value"
   Case IsEmpty(Cell): CellType = "Blank"
End Select
End Function

IsText
’ Value refers to text. The code below shows the first cell being found in Column A that is not formatted as text
lastrow = Range("A65536").End(xlUp).Row
For i = 1 To lastrow
  If Application.WorksheetFunction.IsText(Cells(i, 1)) = False Then
  Else
     MsgBox "Cells A" & i & " in Column A is a non-text format"
  Exit Sub
  End If
Next i

IsNonText
’  Value refers to any item that is not text. (Note that this function returns TRUE if value refers to a blank cell.)
If Application.WorksheetFunction.IsNonText(Cells(2, 1)) = True Then
  MsgBox "A2 is not formatted as text"
Else
  MsgBox "A2 is formatted as text"
End If

IsNumber function
‘ Function returns True if the data type of the argument is numeric; otherwise Function returns False. Specifically, it considers the following data types numeric: Boolean, Byte, Currency, Date, Double, Integer, Long, and Single.
Public Function IsNumber(ByVal Value As Variant) As Boolean
If Value Like "[+-]*" Then Value = Mid$(Value, 2)
IsNumber = Not Value Like "*[!0-9.]*" And Len(Value) > 0 And Value <> vbNullString
End Function

IsNumber(12)           ‘ returns True if 12 was entered as a number
IsNumber(123.45)       ‘ returns True
IsNumber($123.45)      ‘ returns True
IsNumber(#1/1/2005#)   ‘ returns True
IsNumber("12")         ‘ returns False
IsNumber("MJ7")        ‘ returns False

Return To Top

IsNumeric function
’ IsNumeric function determines if a variable can be evaluated as a number
var = "123.456"
x = IsNumeric(var)     ' returns True

var = "2224 Helpdesk"
x = IsNumeric(var)     ' returns False


’ Run through column A and only if the Cells are numeric will it copy the Cell value to the 4 Cells on the right
Dim rTarget As Excel.Range
Dim LastRow As Long, r As Long
LastRow = Cells(65536, 1).End(xlUp).Row
For r = 1 To LastRow
Set rTarget = Cells(r, 1)
    If IsNumeric(rTarget) Then
            rTarget.Copy Range("B" & r & ":E" & r)
        Else
    End If
Next
 

Choose function
‘ Choose function returns a value from a list of values based on a given position. The syntax is:

Choose(index position, choice1, choice2, ... choice n)

‘ Choose function evaluates an numeric argument and a list. In the following example, there are four arguments in the list, the numeric argument should be between 1 to 3. If the numeric argument is 3, Choose returns the third item in the list. If the numeric argument is less than one or greater than the number of items in the list, Choose returns Null. This Select Case statement is cumbersome as compared to using the Choose function. The second example using Choose function to display a name in response to an index passed into the procedure in the Choice parameter.

Public Function ChooseMySelect(Choice As Integer) As String
Dim X As String
Select Case Choice
Case 1
X= "1"
Case 2
X= "2"
Case 3
X= "3"
Case Else
X= vbNullString
End Select
ChooseMySelect = X
End Function


Public Function ChooseMySelect(Choice As Integer) As String
Dim X
X = Choose(Choice, "1", "2", "3)
ChooseMySelect = "" & X
End Function


‘ This following example of initializing with long static arrays is equally long and cumbersome as compared
to using the more handy Choose function with arrays.
Private Sub UseLongStaticArray()
Dim iArray(1 To 5) As Currency
Dim i As Integer
For i = 1 To 5
iArray(1) = 100
iArray(2) = 500
iArray(3) = 2000
iArray(4) = 5000
iArray(5) = 10000
Cells(i, 1) = iArray(i)
Next
End Sub


Private Sub UseChooseNotStaticArray()
Dim iArray(1 To 5) As Currency
Dim i As Integer
For i = 1 To 5
    iArray(i) = Choose(i, 100, 500, 2000, 5000, 10000)
Cells(i, 1) = iArray(i)
Next
End Sub

Return To Top

Switch function
‘ Evaluates a list of expressions and returns a Variant value or an expression associated with the first expression  in the list that evaluates as True. If expression1 is True, then Switch returns the value indicated by value1; and so on. The Syntax is:
Switch(expression1, value1, expression2, value2, ... expression_n, value_n)

Function LanguageMatch(ByVal ThankYouIn As String) As String
   LanguageMatch = Switch(ThankYouIn = "Russian", "spaciba", ThankYouIn = _
                   "German", "Danke", ThankYouIn = "Spanish", " gracias")
End Function

Return To Top

File/Directory Functions

ChDir statement
’ ChDir statement allows you to change the current directory or folder on the current drive as the following example
would do.
ChDir "C:\william91128\GPP_DSM\"

Dir function
’ It  returns a String value containing the name of a file, directory, or folder that matches a pattern (specified in the pathname argument) and/or a file attribute (specified in attributes). You can use wildcard characters to specify multiple files (* allows you to match any string of any length, including zero length; ? allows you to match on a single character).         
Dir[(pathname[, attributes])]

File attributes is optional and can be one or a combination of the following values:

Dim Lpath As String
Lpath = Dir("C:\MyBook?.txt", 1)
If Len(Dir("C:\MyBook?.txt")) > 0 Then
    MsgBox "the file does exist"
Else
    MsgBox "file does not exist"
End If

Return To Top

FileLen function
’  In example below, the variable called Lpath would now contain the size of the file InvTargetFY08.xls in bytes.
Dim Lpath As Long
Lpath = FileLen("C:\william91128\GPP_DSM\InvTargetFY08.xls")

MkDir statement
’ It creates the new directory or folder specified by the path argument. The MkDir statement in the first line will only create the GPP_DSM directory under C:\william91128 directory. It will not create the C:\william91128 directory itself. However, if you are not sure whether GPP_DSM directory already exist or not, the second line IF-THEN-ELSE code will do the job.

MkDir "C:\william91128\GPP_DSM"

If Len(Dir("C:\william91128\GPP_DSM", vbDirectory)) = 0 Then
   MkDir "C:\william91128\GPP_DSM"
Else
End If

FileDateTime function
’ It returns the date and time of when a file was created or last modified, depending on your locale date setting
Dim LastModified As Date
LastModified = FileDateTime("C:\william91128\GPP_DSM\InvTargetFY08.xls")
Cells(1, 1).Value = "Report Last Modified date was on " & LastModified