Vergleich VBA

[HKindler]

@Juvee
Die Fallunterscheidung mit Target.Count ist hier doch überflüssig….

[juvee]

@Helmut

sicher ist sicher  

nun kann er machen, was will er denn!

VG Juvee

[herbert0803]

Hi,

wenn du die Eingabe in alle Tabellen ausser der Tabelle "Liste" überwachen willst, solltest du das Ereignis Workbook_SheetChange() verwenden

das würde ich dann etwa so machen

der Code gehört in den Codebereich von DieseArbeitsmappe
...
VG Juvee

Hallo Juvee,

es gibt in der Datei noch weitere Tabellen, die nicht überwacht werden müssen.
Überwacht werden müssen nur die Tabellen "Gruppe 1", "Gruppe 2" und "Gruppe 3".

Wenn ich in einem der 3 Tabellenblätter die Eingaben mache, funktioniert es, nachdem ich in der folgenden Zeile die Range auf A2:K205 angepasst habe.
retval = WorksheetFunction.VLookup(rg.Value, Worksheets("Liste").Range("A6:K205"), 11, False) <> mtch.Value

Ich verstehe zwar nicht mehr alles was der Code macht, aber es funktioniert.

Eines noch. 
Wenn ich in der überwachten Range einen Zellinhalt lösche, kommt errMsg.
Kann ich das unterbinden?

[herbert0803]

Hallo Juvee,

ich habe es selbst mal versucht und das wäre mein Ergebnis.

Code:

Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim rg As Range
If Sh.Name Like "Gruppe*" And Not Intersect(Target, Sh.Range("A6:A205")) Is Nothing Then
  If Target.Count > 1 Then
    For Each rg In Target
     Call matchSheet(rg, Sh.Cells(1, 23))
    Next
  Else
    Call matchSheet(Target, Sh.Cells(1, 23))
  End If
End If
End Sub

Sub matchSheet(ByVal rg As Range, mtch As Range)
  Dim retval As Long
  On Error GoTo errorhandler
  retval = WorksheetFunction.VLookup(rg.Value, Worksheets("Liste").Range("A2:K602"), 11, False) <> mtch.Value
  If retval = -1 Then Call createMsg(rg)
  Exit Sub

errorhandler:
  Call errMsg(rg)
End Sub

Sub createMsg(rg As Range)
  MsgBox "Nr. " & rg.Value & " als Startnummer nicht in " & rg.Parent.Name & " zugelassen." & vbCrLf _
  & "Die Startnummer " & rg.Value & " gehört lt. Liste in Gruppe " & WorksheetFunction.VLookup(rg.Value, Worksheets("Liste").Range("A2:K602"), 11, False) & "!", _
  vbInformation, "Falsche Gruppe"
  Call clearCells(rg)
End Sub

Sub errMsg(rg As Range)
  If rg.Value <> "" Then
  MsgBox "Startnummer " & rg.Value & " nicht in Liste vorhanden/gefunden", vbInformation, "Startnummer prüfen"
   Call clearCells(rg)
  End If
End Sub

Sub clearCells(rg As Range)
  Application.EnableEvents = False
    rg.Value = ""
    rg.Select
  Application.EnableEvents = True
End Sub

Was meinst du?

[juvee]

Hi,

mit der festlegung auf Tabellen mit "Gruppe*" als Namen gut gelöst.

Wandle errMsg ab, incdem du die übergebene Zelle auf Inhalt prüfst

z.B. so

Code:

Sub errMsg(rg As Range)
If Len(rg) Then
  MsgBox "Startnummer nicht in Liste vorhanden/gefunden"
  Call clearCells(rg)
End If
End Sub

Nichtvorhandene Startnummern haben zumindest eine Längen <> 0 ( aka true). Löscht du eine Zelle, dann hat rg die Länge 0 (aka false )

Was soll ich dir zu deinem besseren Verständnis erläutern/erklären?

VG Juvee

[herbert0803]

Hallo Juvee,

nach längerem Studium des Codes ist einiges schon verständlicher geworden.
Irritiert hat mich u. a. am Anfang die Aufteilung auf mehrere Sub.

Und vor allem kenne ich mich mit der Verwendung von "ByVal" gar nicht aus.
zB wurde rg As Range festgelegt, aber der Wert rg auf den ersten Blick nicht definiert.
Und woher der Wert für mtch.Value im Sub matchSheet kommt, ist mir gar nicht klar.

Danke!

[juvee]

Hi,

man könnte die Lösung der Aufgabenstellung komplett in einem einzigen Code herstellen.

Durch die Aufteilung der Gesamtaufgabe in einzelne Arbeitsschritte ( Auslagerung sich wiederholender Programmzeilen ) wird (soll) der Code kürzer und übersichtlicher (werden). ==> Vermeidung von sog. Redundanzen

Darüber hinaus braucht man dann bei notwendigen Änderungen lediglich an 1 Stelle den Code ändern.

Aufrufe von "Unterroutinen" können ohne oder mit Übergabe von Argumenten erfolgen. Sollen "Unterroutinen" etwas an die aufrufende Prozedur zurückliefern, verwendet man im Regelfall FUNCTION(). Braucht nichts zurückgeliefert werden, kann man auch eine weitere SUB() aufrufen.

Bei beiden können Argumente erforderlich sein. Das ergibt sich aus der "Kopfzeile", z.B.   Sub errMsg(rg as Range)
Das ist dann auch gleichzeitig deren Deklaration, es entfällt also   ---dim rg as Range---  innerhalb der Function oder Sub
Hier ist also eine Übergabe einer Range ( das kann eine Zelle oder ein Zellbereich sein ) erforderlich. Fehlt diese bei Aufruf gibt es einen Fehler.
Mann kann mehrere Argumente übergeben. Es gibt auch die Möglichkeit, Argumente optional zu übergeben. 

Die Parameterübergabe ist als Default eine Übergabe  byRef. Insofern kann man das weglassen. Die andere Übergabeform wird durch das prefix byVal erreicht  --- Sub errMsg( byval rg as range) --
Was bedeutet das jeweils???

Bei der Übergabe eines Parameters byRef wird quasi ein Zeiger auf den Parameter übergeben, d.h. dass wenn in der aufgerufenen Function oder Sub der Parameter verändert wird, dieser dann ebenfalls in der aufrufenden Prozedur verändert ist, da die Veränderung am Originalwert stattfindet.
Die Übergabe byVal ist eine KOPIE des Parameters, d.h. ich kann diese in der aufgerufenen Function oder Sub zwar verändern, in der aufrufenden Prozedur ist und bleibt er aber unverändert.
Lies mal hier:
https://www.vba-tutorial.de/prozeduren/

mtch.value ist der Wert der Zelle W1 aus dem jeweiligen Worksheet Gruppe*.

Die Sub matchSheet  erwartet 2 parameter ( rg und mtch ), die Range-Objekte sind. Dass rg als byVal und mtch als byRef übergeben werden liegt an meiner Oberflächlichkeit. Ist mir nicht aufgefallen und spielt auch keine Rolle, da lediglich die Werte in beiden Zellen miteinander verglichen werden sollen. Sry for that  

Um Code nachzuvollziehen und zu verstehen mache ich, falls notwendig weil nicht sofort ersichtlich ist, was wie passiert, folgendes:
ich setze in der Codezeile, wo mein Unverständnis beginnt, mit F9 oder Mausklick einen Breakpoint und starte den Code. Der hält dann am Breakpoint und ich kann dann jeweils mit F8 den Code zeilenweise abarbeiten.
Der Mauszeiger über den jeweiligen Variablen zeigt mir deren Wert. Ersatzweise kann auch das Überwachungs- oder Lokalfenster Aufschluss geben. Und wenn mann sich dann so durch den Code durcharbeitet, versteht man ( ich ) leichter, was wie passiert.


Hoffe, es ist ein wenig mehr Licht!

VG Juvee

[herbert0803]

Guten Morgen Juvee,

Etwas mehr verstehe ich schon.
Aber mtch.Value im Sub matchSheet verstehe noch immer nicht ganz.
Dass es der Wert der Zelle W1 aus dem jeweiligen Worksheet Gruppe* ist, ist mir klar. Aber wie kommt es zu dieser Zuordnung?

In der Zeile Call matchSheet(rg, Sh.Cells(1,23)) wird rg übergeben und eine Range.
Wird bei Sub matchSheet(ByVal rg As Range, mtch As Range) dann automatisch die übergebende Range ohne Variablenbezeichnung zur mtch?

Könnten auch mehrere Ranges für mehrere Variablen übergeben werden und wie wäre dann die Zuordnung zu Variablen?
zB zusätzlich Worksheets("Liste").Range("A2:K602" oder müsste ich da vorher deklarieren.

Meine Ausdrücke sind vermutlich nicht immer richtig?

[juvee]

Hi,

bei  parametrisierten Aufrufen von Unterroutinen kommt es nicht auf die NAMEN der Variablen an, lediglich die Position ist entscheidend. Ausserdem müssen die Variablentypen übereinstimmen.

Das legst du ja in der Kopfzeile der Unterroutinen selber fest, welchen Typ die Variable haben soll.

Du kannst soviele Variablen übergeben, wie du möchtest ==> je mehr du übergibst, desto unübersichtlicher wird es ggfs.

Übergibst du  ein Range-Object, brauchst du z.B. den Pfad nicht extra als Argument übergeben, denn mit Range.parent.parent.....kannst du bis  zum Application-Objekt zugreifen.

Kopiere mal folgenden Code in eine bereits gespeicherte Datei und durchlaufe ihn im Einzelschrittmodus ( Taste F8  )
Es sind 3 unterschiedliche Aufrufe, die dir verdeutlichen sollen, dass es nicht auf den Namen der Variablen ankommt, sondern auf deren Position im Aufruf und in der Kopfzeile

Wenn du den Aufruf abänderst, so dass die Subroutine2 aufgerufen wird, wird deutlich, dass der sog. Scope von Variablen ( also wo sie gelten ) jeweils nur dort bekannt ist, selbst dann, wenn sie gleichlautende Namen tragen.

Code:

Option Explicit
Sub Aufruf()
Dim firstcell As Range, secondcell As Range
Cells(1, 1) = "Hallo"
Cells(1, 2) = "Welt"

Set firstcell = Cells(1, 1)
Set secondcell = Cells(1, 2)

  Call subroutine1(Cells(1, 1), Cells(1, 2))
  Call subroutine1(Cells(1, 2), Cells(1, 1))
  Call subroutine1(secondcell, firstcell)
End Sub

Sub subroutine1(ByVal rg As Range, ByVal zelle As Range)
  MsgBox rg.Value
  MsgBox zelle.Address
  MsgBox rg.Parent.Name
  MsgBox rg.Parent.Parent.FullName
End Sub

Sub subroutine2(ByVal firstcell As Range, ByVal secondcell As Range)
  MsgBox firstcell.Value
  MsgBox secondcell.Address
  MsgBox firstcell.Parent.Name
 
End Sub


 

VG Juvee

[herbert0803]

Hallo Juvee,

Danke für die Erklärung.
Jetzt ist mir das ganze schon um einiges verständlicher.

Werde mal versuchen das bei meinen nächsten Projekten umzusetzen.

DANKE!