Ich spiele derzeit mit Enums. Ich bin davon überzeugt, dass Enumerations den Code lesbarer machen.
Hier ein Beispiel - mit ein paar weiteren "Helpers" (als Zugabe...)
In diesem Beispiel wird für .Cells (row, Column) bei Columns die Enumeration verwendet anstatt die Spaltennummer, oder Spaltenbuchstabe
Bitte beachtet auch die Namenskonventionen: für Module immer Plural (also mit angehängtem "s" am Ende), Subs, Functions und Enums immer Singular. Weiters bei Variablen, camelCase, und vor allem - wenn geht - "sprechende" Variablenamen.
Modul: modEnums
Code: Alles auswählen
'-------------------------------------------------------------
'
' purpose: Enumerations of Columns in the Sheet
' "wksSession"
' @params: -
' @return: -
' author: thowe, Thomas Weber
' @version: 1.0.6
' @depreciated: None
' @source: inspired by Excel Macro Mastery
'
'-------------------------------------------------------------
Public Enum eColumnSession
ecNone = 0
[_First] = 1
ecNumber = [_First]
ecDatum
ecUhrzeit
ecVermittler
[_Last]
End Enum
Code: Alles auswählen
'-------------------------------------------------------------
'
' purpose: populates Session data into the specified
' Columns. The columns are enumerated in the
' Modul "modEnum"
' @params: -
' @return: -
' author: thowe, Thomas Weber
' @version: 1.0.6
' @depreciated: None
' @source: inspired by Excel Macro Mastery
'
'-------------------------------------------------------------
Sub WriteDataToSheetSession(intRow As Integer)
Dim varDate As Variant 'Variable: The actual Date
Dim VarTime As Variant 'Variable: The actual Hour
'initialize
varDate = Date
VarTime = Time
With wksSession
.Cells(intRow, ecNumber) = intRow - 1
.Cells(intRow, ecDatum) = varDate
.Cells(intRow, ecUhrzeit) = VarTime
End With
End Sub
Modul: modSheetHelpers
Code: Alles auswählen
'-------------------------------------------------------------
'
' purpose: Check if the targeted Worksheet exists
' @params: strSheetName, objWorkbook
' @return: Boolean
' author: thowe, Thomas Weber
' @version: 0.0.9
' @depreciated: None
' @source:
'
'-------------------------------------------------------------
Function WorksheetExists(strSheetName As String, Optional objWorkbook As Excel.Workbook) As Boolean
Dim objWorkSheet As Excel.Worksheet
If objWorkbook Is Nothing Then Set objWorkbook = ThisWorkbook
On Error Resume Next
Set objWorkSheet = objWorkbook.Sheets(strSheetName)
On Error GoTo 0
WorksheetExists = Not objWorkSheet Is Nothing
'reset, unset, delete
Set objWorkSheet = Nothing
Set objWorkbook = Nothing
End Function
'-------------------------------------------------------------
'
' purpose: get the SheetName of the targeted Worksheet by
' CodeName of the Sheet
' @params: strSheetCodeName, objWorkbook
' @return: Boolean
' author: thowe, Thomas Weber
' @version: 0.0.9
' @depreciated: None
' @source:
'
'-------------------------------------------------------------
Function GetSheetNameByCodeName(strSheetCodeName As String, Optional objWorkbook As Excel.Workbook) As String
Dim wksSheet As Excel.Worksheet
Dim strSheetName As String
'initialize
strSheetName = ""
If objWorkbook Is Nothing Then Set objWorkbook = ThisWorkbook
For Each wksSheet In objWorkbook.Worksheets
If wksSheet.CodeName = strSheetCodeName Then
strSheetName = wksSheet.Name
Exit For
End If
Next
If Not strSheetName = "" Then GetSheetNameByCodeName = strSheetName
'reset, unset, delete
Set objWorkbook = Nothing
End Function
Der Trigger (Auslöser des Events)
Prinzipell löse ich den Dateneintrag mit dem Event "Workbook_BeforeClose(Cancel As Boolean)" aus
Klasse: DieseArbeitsmappe
Code: Alles auswählen
'-------------------------------------------------------------
'
' purpose: Before cloing ThisWorkbook we populate some
' data into the Worksheet "wksSession"
' @params:
' @return:
' author: thowe, Thomas Weber
' @version: 0.0.9
' @depreciated: None
' @source:
'
'-------------------------------------------------------------
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim intLastRow As Integer 'Variable: the first empty row in Sheet "wksSession"
If WorksheetExists("wksSession") Then 'Call the Helper WorksheetExist in modWorksheets
intLastRow = GetLastRow("wksSession") + 1 '+1: add 1 to get the next empty row
Call WriteDataToSheetSession(intLastRow - 1) '-1: this representates the counter (=total number of entries in wksSession)
End If
End Sub
LG
