Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema MsgBox
BildScreenshot zu MsgBox MsgBox-Seite mit Beispielarbeitsmappe aufrufen

Global Worksheet Variable


Betrifft: Global Worksheet Variable von: Severin
Geschrieben am: 26.07.2018 14:36:00

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

  

Betrifft: Public statt Global. owT von: Rudi Maintaire
Geschrieben am: 26.07.2018 14:48:47




  

Betrifft: und SET muss... von: Rudi Maintaire
Geschrieben am: 26.07.2018 14:57:30

... natürlich innerhalb einer Prozedur erfolgen


  

Betrifft: AW: Global Worksheet Variable von: ChrisL
Geschrieben am: 26.07.2018 14:52:57

Hi Severin

Erstmal Unterschied Global und Public:
http://www.office-loesung.de/ftopic207739_0_0_asc.php

Deine Idee habe ich zwar schon öfters gesehen, aber persönlich verwende ich es nicht. Du kannst ja den internen Codename verwenden, der bleibt gleich, auch wenn der Blattname geändert wurde.

Tabelle1.Range("A1")
oder
Set ws = Tabelle1

Und Global bzw. Public funktioniert schon. Folgende beide Makros nacheinander abgespielt:

Global ws As Worksheet

Sub t1()
Set ws = Worksheets("Tabelle1")
End Sub

Sub t2()
MsgBox ws.Name
End Sub
cu
Chris


  

Betrifft: AW: Global Worksheet Variable von: Severin
Geschrieben am: 26.07.2018 15:21:09

Das die Variable Global innerhalb einer Prozedur gesetzt werden muss ist mir bewusst.

Also ist Public als nur das neue Global, interessant.

Dein Beispiel funktioniert bei mir auch.
Warum funktioniert dann aber:

Global ws As Worksheet

Sub t1()
Set ws = Worksheets("test")
End Sub

Sub t2()
ws.UsedRange
MsgBox ws.Name
End Sub
nicht?

Ich habe mir gerade das mit den Codenamen angeschaut. So nah und doch ist es mir nie aufgefallen.
Manchmal ist es schwer etwas zu finden, wenn man nicht weiß wonach man sucht :D

Vielen Dank für die Hilfe :)

Gruß,
Severin


  

Betrifft: AW: Global Worksheet Variable von: ChrisL
Geschrieben am: 26.07.2018 15:28:17

Hi Severin

z.B.

Range("A1") (ohne nix)
führt genauso zum Fehler.

Sub t2()
MsgBox ws.UsedRange.Address
End Sub
cu
Chris


  

Betrifft: AW: Global Worksheet Variable von: Severin
Geschrieben am: 26.07.2018 15:42:38

Hi Chris,

ich verstehe was du meinst.

Nur habe ich bis jetzt immer folgenden Code verwendet um die letzte Zelle eines Blattes zu aktualisieren.

Sub t1()
Worksheets("test").UsedRange
End Sub

Excel macht das ja nur beim speichern wenn ich mich recht entsinne.
Das heißt, bevor ich
totalrow = Worksheets("test").Cells.SpecialCells(xlCellTypeLastCell).Row

verwende, möchte ich ja erst die letzte Zelle aktualisieren.

Das funktioniert auch einwandfrei.
Ich habe also noch nicht ganz verstanden, warum das dann mit der Variable nicht mehr geht...

Danke,
Severin


  

Betrifft: AW: Global Worksheet Variable von: ChrisL
Geschrieben am: 26.07.2018 17:13:44

Hi Severin

Jetzt bin ich verwirrt.

a) UsedRange beinhaltet den ganzen benutzten Bereich, nicht nur die letzte Zelle
b) UsedRange ist kein Aktualisierungsbefehl

Wenn die automatische Formelberechnung ausgeschaltet ist, dann wäre Aufruf...

für die letzte Zelle (rechts unten)
Worksheets("test").Cells.SpecialCells(xlCellTypeLastCell).Calculate

für das ganze Blatt
Worksheets("test").Calculate

cu
Chris


  

Betrifft: AW: Global Worksheet Variable von: ChrisL
Geschrieben am: 26.07.2018 18:13:38

Sub t1()
Worksheets("test").UsedRange
End Sub

übrigens bin ich erstaunt, dass obiger Code nicht zur Fehlermeldung führt.

Um welche Art von Aktualisierung geht es (Formel, Abfrage, Pivot usw.)? Je nach dem kann es auch ein RefreshAll oder o.ä. sein.

Und falls du die automatische Formelberechnung nur während der Makrolaufzeit ausschaltest, dann findet die Aktualisierung automatisch und ohne zusätzlichen Befehl statt, sobald du die automatische Formelberechnung wieder einschaltest.

cu
Chris


  

Betrifft: AW: Global Worksheet Variable von: Severin
Geschrieben am: 27.07.2018 10:32:20

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


  

Betrifft: AW: Global Worksheet Variable von: ChrisL
Geschrieben am: 27.07.2018 15:04:33

Hi Severin

OK, jetzt verstehe ich um welche Art von Aktualisierung dass es geht.

Mit dem Problem war ich bisher nie konfrontiert, weil ich immer diese Variante verwende:
Cells(Rows.Count, 1).End(xlUp).Row

Hier musst du zwar zwingend eine Spalte angeben, aber dies ist fast immer auch sinnvoll. Solche Eingabemasken und Datenbanken sollten in der Theorie immer einen Primärschlüssel (ID / Laufnummer) haben. Und wenn nicht, dann wenigstens ein Muss-Feld/Spalte, welche zur Ermittlung verwendet werden kann.

Obwohl deine Umgehungslösung in verschiedenen Beiträgen vorgeschlagen wird, scheint es mir, dass hier ein Bug (fehlende Aktualisierung) mit einem weiteren Bug (UsedRange) umgangen wird.

cu
Chris


  

Betrifft: AW: Global Worksheet Variable von: Severin
Geschrieben am: 31.07.2018 12:57:00

Hi Chris,

Ab sofort verwende ich

Cells(Rows.Count, 1).End(xlUp).Row
Einen Primärschlüssel habe ich nämlich tatsächlich auch immer, da ich nur mit Listen und eindeutigen Zuordnungen arbeite.

Mir war nicht bewusst, dass diese Funktion anscheinend keine "Aktualisierung" benötigt.

Das ist sehr erfreulich.

Ein weiterer schöner Nebeneffekt der mir auffällt ist, dass diese Variante auch bei geschützten Arbeitsmappen anwendbar ist, SpecialCells nicht.

Habe meinen Code entsprechend angepasst:
--> kürzer, übersichtlicher, besser anwendbar

Danke für dein Hilfe,

Gruß,
Severin


  

Betrifft: AW: Global Worksheet Variable von: ChrisL
Geschrieben am: 27.07.2018 18:30:40

Hi

Nachtrag:

Wenn du sowieso pauschal alle Tabellen "aktualisieren" willst und weil du mit einem Eingabeformular arbeitest, warum nicht gleich die Datei zwischen speichern. Save führt angeblich ebenfalls zum Update.
ThisWorkbook.Save

Ich denke SpecialCells greift auf die UsedRange zu und die "Pseudo-Benutzung" der UsedRange führt zur Aktualisierung. Dass UsedRange stand-alone funktioniert erstaunt mich weiterhin, weil es ein Property/Eigenschaft ist. Insofern war auch mein Vergleich mit Range nicht ganz passend.
https://msdn.microsoft.com/en-us/vba/excel-vba/articles/worksheet-usedrange-property-excel?f=255&MSPPError=-2147217396

Wenn du noch tiefer nach dem wieso und warum graben willst. Mir sind bei deiner Umgehungslösung und aufgrund der Tatsache, dass UsedRange ebenfalls Read-Only ist, Parallelen zu diesem Beitrag aufgefallen.
https://www.herber.de/forum/messages/1635395.html

cu
Chris


  

Betrifft: AW: Global Worksheet Variable von: Nepumuk
Geschrieben am: 26.07.2018 15:19:48

Hallo Severin,

warum arbeitest du nicht mit den Objektnamen der Tabellen? Das ist der Namen im Projektexplorer der vor dem Namen der Exceloberfläche der in Klammern dahinter steht. Damit hast du vollen Zugriff auf die Tabelle egal welcher Name die Tabelle auf der Exceloberfläche hat.

Gruß
Nepumuk


  

Betrifft: AW: Global Worksheet Variable von: Severin
Geschrieben am: 26.07.2018 15:26:00

Hallo Nepumuk,

danke, das ist natürlich eine super Lösung.

Ich habe mir VBA selbst beigebracht und bin bis jetzt wohl nie über Objektnamen gestolpert.

Das ist wohl der Nachteil an learning by doing :)

Danke auch für deine Hilfe!

Gruß,
Severin


Beiträge aus dem Excel-Forum zum Thema "Global Worksheet Variable"