Schlagwort-Archive: VBA

Programmierung, VBA

Microsoft Excel VBA: How to delete a worksheet in a workbook / Ein Arbeitsblatt in einer Arbeitsmappe löschen

Schreibe einen Kommentar

Problem

A worksheet within a workbook shall be deleted via VBA in Excel.
Ein Arbeitsblatt soll in einer Arbeitsmappe gelöscht werden.

Approach / Ansatz

  • Create a new SUB method with the worksheet name as parameter / Definition einer neuen Sub-Methode mit Übergabe des Namen des zu löschenden Arbeitsblattes
  • Iterate all worksheets / über alle Arbeitsblätter iterieren
    • Check the name of the current worksheet / Namen der Worksheets prüfen
      • When the name matches the SUB paramter, the worksheet will get deleted via .Delete method / Wenn der Name des übergebenen Parameters entspricht, wird das worksheet gelöscht mit der .Delete Methode

Solution / Lösung

Sub DeleteWorksheet(worksheetName As String)
Dim ws As Worksheet, wb As Workbook

Set wb = ActiveWorkbook

Application.DisplayAlerts = False

For Each ws In wb.Worksheets
    If ws.Name = worksheetName Then
        ws.Delete
        Exit For
    End If
Next

Application.DisplayAlerts = True

End Sub

Programmierung, VBA

VBA: Check if string StartsWith / StartWith or EndsWith / EndWith

Schreibe einen Kommentar

Problem

Visual Basic for application does not have function to test, whether a string starts with or ends with another string like it is included in the .NET Framework

Approach

Those function can easily created by using the existing string functions

Solution

The following code can be pasted to a VBA project:

Public Function EndsWith(str As String, ending As String) As Boolean
     Dim endingLen As Integer
     endingLen = Len(ending)
     EndsWith = (Right(Trim(UCase(str)), endingLen) = UCase(ending))
End Function

Public Function StartsWith(str As String, start As String) As Boolean
     Dim startLen As Integer
     startLen = Len(start)
     StartsWith = (Left(Trim(UCase(str)), startLen) = UCase(start))
End Function

Example usage:

If StartsWith(„My string has something in it“, „My string“) Then Msg Box „It is in it!“
If EndsWith(„My string has something in it“, „in it“) Then Msg Box „It is in it!“