Global Worksheet Variable

Hallo zusammen,
da ich mich nun mittlerweile besser in VBA auskenne, jedoch meine saubere Programmierung zu wünschen übrig lässt, wollte ich anfangen möglichst viele Variablen zu definieren, die alles übersichtlicher und allgemeiner gestalten sollen.
Anfangen wollte ich damit, meine Tabellenblätter in Worksheet Variablen zu speichern, um bei Änderung des Namens des Blattes nur an einer Stelle etwas ändern zu müssen. Außerdem soll der Schreibaufwand von Worksheets("xxx") auf ws reduziert werden.
Anfangs hat alles gepasst. Nun wollte ich aber ws.UsedRange verwenden und ich erhalte die Meldung: "Unzulässige Verwendung einer Eigenschaft"
Wenn ich Worksheets("xxx").UsedRange verwende, funktioniert es einwandfrei.
Verstehe ich die Variable Global ws As Worksheet falsch?
Müsste nach festlegen der Variable...
Global ws As Worksheet
------------------------------------
Set ws = Worksheets("xxx")
...ws nicht einfach genau für Worksheets("xxx") stehen und alle Eigenschaften funktionieren?
Vielen Dank für eure Hilfe!
Gruß,
Severin

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Hi Chris,
ich versuch mal zu erläutern wie ich darauf gekommen bin und für was ich das eigentlich benötige:
Ich erstelle öfters Listen, in denen der Benutzer Daten in ein Formular einträgt, auf einen _ Button klickt und dann diese Informationen in einer großen Liste in der nächsten freien Zeile gespeichert werden. Dafür möchte ich, dass Excel den neuen Datensatz immer in der nächsten freien Zeile speichert. Dafür habe ich grundsätzlich


.Cells.SpecialCells(xlCellTypeLastCell).Row

verwendet. Wenn der Benutzer dann aber eine Zeile gelöscht hat und ohne speichern wieder eine neue über das Makro hinzufügen wollte, ist mir aufgefallen dass sich der benutzte Bereich anscheinend nicht dauernd "aktualisiert". Daraufhin habe ich gegoogelt und habe folgende Lösung gefunden:
https://stackoverflow.com/questions/2107047/force-refresh-of-last-cell-of-the-worksheet
Seitdem habe ich diesen Code verwendet.
Hier mal eine Beispieldatei:
https://www.herber.de/bbs/user/122945.xlsm
Wenn du den Button "Ohne .UsedRange" drückst, werden die Daten in die nächste freie Zeile auf dem entsprechenden Tabellenblatt eingefügt. Wenn du nun aber eine Zeile manuell löscht und dann nochmals auf den Button klickst, wirst du sehen, dass eine freie Zeile übersprungen wird...
Dieses Problem habe ich "Mit .UsedRange" nicht.
Mit Sheets(2).Calculate erhalte ich nicht das gewünscht Ergebnis.
Ich kann mein Problem mittlerweile aber folgendermaßen Lösen:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Call UsedRange
End Sub
Sub UsedRange()
Dim WS As Worksheet
For Each WS In ThisWorkbook.Worksheets
Worksheets(WS.Name).UsedRange
Next WS
End Sub

Mit folgendem Code erreiche ich, dass sich die Zellbereiche jedes mal aktualisieren, wenn sich etwas im Workbook ändert.
Was ich immer noch komisch finde ist, dass folgender Code wieder zu einer Fehlermeldung führt:

Sub UsedRange()
Dim WS As Worksheet
For Each WS In ThisWorkbook.Worksheets
WS.UsedRange
Next WS
End Sub

Wenn ich über das Worksheet Objekt aber den Namen ziehe und den dann in Worksheet("") schreibe, funktioniert es.

Sub UsedRange()
Dim WS As Worksheet
For Each WS In ThisWorkbook.Worksheets
Worksheets(WS.Name).UsedRange
Next WS
End Sub

Danke und Gruß,
Severin

Beliebteste Forumthreads (12 Monate)

14 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

14
Beiträge zum Forumthread

Beiträge zu diesem Forumthread