CODEWITHSUNDEEP
vbaexcel
teepee
teepee

Reputation: 2714

What is the most concise ElseIf logic for analyzing two Boolean criteria?

I have a function called AddSheetAtEnd which creates a new worksheet in the current book. I am using it to prepare a worksheet for inputting certain data.

Originally I had it as such:

Function AddSheetAtEnd(ShtName As String)
    If Not IsWorksheetName(ShtName) Then
        'if worksheet doesn't exist, create sheet
        With ThisWorkbook
            .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = ShtName
            AddSheetAtEnd = True
        End With
    Else
        'if worksheet exists, quit function
        AddSheetAtEnd = False
        Exit Function
    End If
End Function

If the worksheet did exist, this function quits, and I have another function that wipes everything off the sheet before data is written in later. I decided to instead add an optional argument readOnlyFlag to give permission to delete the sheet and add a new one if necessary, instead of having to have another function to just wipe the data off the sheet. Now I need a way of executing this:

If readOnlyFlag = True AND sheetExists = True Then
    'do nothing, exit
Elseif readOnlyFlag = False AND sheetExists = True Then
    'delete sheet
    'create sheet
Elseif readOnlyFlag = True AND sheetExists = False Then
    'create sheet
Elseif readOnlyFlag = False AND sheetExists = False Then
    'create sheet
End If

I am trying to determine the most concise (and efficient way of having this execute. I have come up with this so far:

If readOnlyFlag = True AND sheetExists = True Then 
    AddSheetAtEnd = False
    Exit Function
Elseif readOnlyFlag = False Then
    If sheetExists = True Then DeleteSheet(ShtName)
    With ThisWorkbook
        .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = ShtName
        AddSheetAtEnd = True
    End With    
End If

Is there a more robust or concise way to organize this? Is any of this superfluous, or missing possible cases?

Upvotes: 2

Views: 50

Answers (2)

Dan Donoghue
Dan Donoghue

Reputation: 6216

You can use the errors and make assumptions like so:

On Error Resume Next
If Not readOnlyFlag Then
    'delete sheet
End If
shtCount = ThisWorkbook.Worksheets.Count
'create sheet
If ThisWorkbook.Worksheets.Count > shtCount Then 'Set your flag or pass back the result from the function
On Error GoTo 0

Set the error command to continue.

If it's not read only then delete the sheet, if it doesn't exist it just continues.

Then create the sheet, if it already exists it just continues

Set the error command back to normal

Upvotes: 3

tigeravatar
tigeravatar

Reputation: 26660

You can convert the Boolean values to 1's and 0's and then sum to find out what's what. For every additional Boolean value, multiply it by an additional power of 2 (first test is at 2^0, so times 1 - don't multiply - second test is at 2^1, so times 2, third test is at 2^2, so times 4, etc)

For your specific situation, you would do Abs(readOnlyFlag) + Abs(sheetExists) * 2

  • If readOnlyFlag is False, you get 0 and True you get 1
  • If sheetExists is False you 0 and True you get 2

That results in 0 = Both False, 1 = readOnlyFlag True, 2 = sheetExists True, 3 = Both True

Generic code would look like this:

Select Case Abs(readOnlyFlag) + Abs(sheetExists) * 2
    Case 0: 'Both False
    Case 1: 'readOnlyFlag true
    Case 2: 'sheetExists true
    Case 3: 'Both True
End Select

For your specific situation, it becomes this:

With ThisWorkbook
    Select Case Abs(readOnlyFlag) + Abs(sheetExists) * 2
        Case 0, 1:  .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = ShtName
        Case 2:     DeleteSheet (ShtName)
        Case 3:     AddSheetAtEnd = False
    End Select
End With

Upvotes: 3

Related Questions