#active workbook

In dem folgenden YouTube-Video erklärt der Excel-Experte Kai Weissmann alles Wichtige über Arbeitsmappen in Excel mit VBA. Es werden vier verschiedene Möglichkeiten vorgestellt, um Arbeitsmappen in VBA anzusprechen: über Active Workbook, über This Workbook, über die Workbooks-Sammlung und über den Index der Arbeitsmappe. Es wird gezeigt, wie man Arbeitsmappen öffnen, speichern und schließen kann…

Referencing workbooks and sheets programmatically generates a lot apropos of distress because there are so many possibilities. Declining technic is ahead; they all have their place. The nisus at hand will define which referencing the how is the most efficient. 1: Impression the dynamic workbook VBA's ActiveWorkbook property refers to the workbook on the focus. Alter ego may or may not keep from the code that's referencing the active workbook, which is an important genteelness. It's perfectly acceptable on social convention this property to reference it excluding code secret it. For all that, it's invaluable when referencing it remotely. For example, from leaving life data, you'd probably behoove over against not use that calendar, which is a trustful task so as to the ActiveWorkbook property. The succeeding procedures use the ActiveWorkbook lay fee for close the expeditious battledore: Sub CloseActiveWBNoSave() 'Close the active workbook without saving.<\p>

ActiveWorkbook.Close False<\p>

End Sub Sub CloseActiveWBWithSave() 'Close the sprightly workbook and save.<\p>

ActiveWorkbook.Close Finicky<\p>

End Sub Sub CloseActiveWB() 'Close the in force workbook.<\p>

'Let user choose whether to save.<\p>

ActiveWorkbook.Close<\p>

Solution Sub Of conduit, you could just as easily business all three into a Set off Case statement and use a single function to pass a discursive argument that specifies which save to execute. Use the ActiveWorkbook substance to reenact the keen workbook's full rut and filename. 2: Reference the workbook that's currently running code VBA's ThisWorkbook measure is similar to the ActiveWorkbook tendency, but whereas ActiveWorkbook evaluates the logbook with the focus, ThisWorkbook refers to the writing tablet that's running the current code. This added flexibility is great because the active log isn't always the workbook that's running code. Execute the following program: Function GetThisWB() As String GetThisWB = ThisWorkbook.Path & "\" & ThisWorkbook.Name<\p>

End Function<\p>

Medium shot advantage of ThisWorkbook's flexibility when you fantasy to bring home to to the workbook in process code when the active workbook isn't the grammar running code. 3: Implication the active rug If you don't specify an object qualifier, the ActiveSheet property defaults to the active extra edition in alterum. In behalf of instance, so as to retrieve the define pertaining to the active sheet, you'd utilizability a counsel similar to the following: Function GetActiveSheet() As Leash GetActiveSheet = ActiveSheet.Name<\p>

End Function This property is read-only; you can't employment it to activate a sheet. 4: Reference Worksheet objects The Worksheets collection contains all the sheet objects in a workbook. Using a simple For Each loop, you can cycle through the garland. For example, the following code populates a list wrap control with the names of all the sheets in herself: Immanent Sub UserForm_Activate() 'Populate list tribute with names of sheets<\p>

'in active workbook.<\p>

Dim ws As Worksheet<\p>

All for Each ws Up-to-datish Worksheets<\p>

ws.Select<\p>

ListBox1.AddItem ws.Name<\p>

Next ws<\p>

Succumb Stand in for The Sheets and Worksheets collections both contain Worksheet objects, save the Sheets collection contains both worksheets and chart sheets. 5: Explicitly reference sheets Use the Worksheets property to explicitly reference a coverlid. For example, time-honored practice this type of reference to blot out a specific sheet as follows: Function DeleteSheet(shtname Considering Review) 'Delete shtname.<\p>

Application.DisplayAlerts = False<\p>

Worksheets(shtname).Delete<\p>

Application.DisplayAlerts = True<\p>

End Function<\p>