|
| |
Fortgeschrittene Programmierung| Worksheet_Change() führt zu #WERT! in Zellen | |  | | Autor: RF | | Datum: 05.10.17 20:14 |
| Hallo,
ich habe ein interessantes Problem in VBA mit Excel 2003.
In einer Tabelle habe ich eine Übersicht, die ich automatisch sortiert haben möchte. Dazu habe ich - einem Snippet von stackoverflow - in dem Tabellenblatt folgenden Code eingefügt:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim SortRange As Range
Set SortRange = Range(("A1"), Cells(Rows.Count, 78))
SortRange.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End SubDas funktioniert soweit problemlos und genau nach meinen Wünschen.
In der Tabelle werden bestimmte Eigenschaften u.a. durch die Hintergrundfarbe einer Zelle gepflegt. Ich habe nun eine Spalte hinzugefügt, in der steht ob eine bestimmte Zelle der Zeile grün ist oder nicht.
Dazu habe ich in einem Modul folgenden Einzeiler definiert:
Function IsDigital(Cell As Range)
IsDigital = (Cell(1, 1).Interior.ColorIndex = 35)
End Functionund rufe ihn in der Tabelle über
=IsDigital(D3) auf.
In der Tabelle erscheint aber statt erwartet WAHR oder FALSCH nur #WERT! Eine kurze Fehleranalyse brachte folgendes zu Tage:
1) Im Formelfenster von Excel gibt die Funktion den korrekten Wert zurück.
2) Kommentiere ich die AutoSort-Funktion aus, funktioniert die Formel korrekt und in der Tabelle stehen die korrekten Werte
3) Kommentiere ich die AutoSort-Funktion wieder ein und ändere die Funktion zu
Function IsDigital(Cell As Range)
IsDigital = (Cell(1, 1).Value)
End Functiondann steht in der entsprechenden Zelle der korrekte Wert.
Es scheint mir also, dass ich der .Interior-Teil mit der Sortierfunktion nicht verträgt.
Nun frage ich mich zwei Dinge:
a) Kennt jemand das Problem?
b) Wie löse ich es, dass ich AutoSort _und_ die IsDigital-Funktion gleichzeit haben kann?
Viele Grüße,
Rico
____________________________________________________
"Mein Leipzig lob' ich mir, es ist ein Klein-Paris" (aus: "Faust")
| |
| Re: Worksheet_Change() führt zu #WERT! in Zellen | | | | Autor: effeff | | Datum: 17.10.17 15:30 |
| Deine Funktion hat keinen Rückgabewert. Zudem hat Deine Funktion einen festen Bezug auf A1: Cell(1, 1). Zudem musst Du prüfen, ob die Zelle eine bestimmte Hintergrundfarbe hat. Das machst Du auch nicht...
EALA FREYA FRESENA | |
| Re: Worksheet_Change() führt zu #WERT! in Zellen | | | | Autor: RF | | Datum: 27.10.17 22:47 |
| Hallo,
- meine Funktion hat einen Rückgabewert. Das lässt sich einfach überprüfen, z.B. im Formelfenster von Excel. Dort funktioniert alles.
- Cell(1,1) bezieht sich auf die erste Zelle des übergebenen Bereiches, nicht des Arbeitsblattes. Das lässt sich leicht überprüfen da die Funktion im Formelfenster den korrekten Wert anzeigt. Cell(,) != Cells(,) !!!!
- Die Überprüfung geschieht mit dem Teilbefehl "ColorIndex = 35". Das lässt sich leicht überprüfen, denn wie ich oben schrieb: 1) Im Formelfenster von Excel gibt die Funktion den korrekten Wert zurück.
Ich weiß nicht, ob dir die verkürzte Schreibweise von Funktionen und deren Rückgabe werten bekannt ist. Falls nicht solltest Du aber nicht im Forum "Fortgeschrittene Programmierung" kommentieren.
Viele Grüße,
Rico
P.S.: Zum bessern Verständnis. Nehmen wir die oben genannte Funktion und schreiben sie mal ganz ausführlich nach Erstsemestermanier auf:
Function IsDigital(Cell As Range)
Dim CellColorIndex As Integer
Dim Result As Boolean
CellColorIndex = Cell(1,1).Interior.ColorIndex
If CellColorIndex = 35 Then
Result = True
Else
Result = False
End If
IsDigital = Result
End FunctionDas ist unnötig viel Code. Als erster kann man die letzte Zuweisung streichen und den Wert direkt zuweisen.
Function IsDigital(Cell As Range)
Dim CellColorIndex As Integer
CellColorIndex = Cell(1,1).Interior.ColorIndex
If CellColorIndex = 35 Then
IsDigital = True
Else
IsDigital = False
End If
End FunctionAls nächstes kann man die Zuweisung CellColorIndex entfernen:
Function IsDigital(Cell As Range)
If Cell(1,1).Interior.ColorIndex = 35 Then
IsDigital = True
Else
IsDigital = False
End If
End FunctionSchlussendlich kann man auch noch das If-Then-Else wegstreichen, denn der Wert ist ja sowieso direkt davon abhängig, welche Farbe die Zelle nun hat. Also:
Function IsDigital(Cell As Range)
IsDigital = (Cell(1,1).Interior.ColorIndex = 35)
End FunctionUnd genau so steht es oben. Ich gestehe hier die Verwirrung zu, die VB schafft weil die Funktionszuweisung in anderen Sprachen eher so aussehen würde:
IsDigital == (Cell(1,1).Interior.ColorIndex = 35) ____________________________________________________
"Mein Leipzig lob' ich mir, es ist ein Klein-Paris" (aus: "Faust")
| |
| Re: Worksheet_Change() führt zu #WERT! in Zellen | | | | Autor: effeff | | Datum: 28.10.17 16:56 |
| Nochmal: Deine Function hat keinen Rückgabewert!
Im folgenden Beispiel habe ich der Zelle A1 des Worksheets "Tabelle1" die Farbe mit dem Index 35 verpasst und kann sie so abfragen:
Private Sub ErgebnisAuswerten()
Dim cell As Range
Set cell = Sheets("Tabelle1").Range("A1")
MsgBox (IsDigital(cell))
End Sub
Private Function IsDigital(ByVal cell As Range) As Boolean
If cell.Interior.ColorIndex = 35 Then
IsDigital = True
Else
IsDigital = False
End If
End FunctionIn der fett dargestellten Zeile kannst Du den Rückgabewert "Boolean" erkennen...
EALA FREYA FRESENA | |
| Re: Worksheet_Change() führt zu #WERT! in Zellen | | | | Autor: RF | | Datum: 03.11.17 22:34 |
| Hallo,
meine Funktion hat einen Rückgabewert, sie hat aber keinen expliziten Rückgabetyp. Es ist unter VB allerdings so, dass Funktionen (und Variablen!), deren Typ nicht explizit angegeben wird stets den Typ "Variant" haben. Bequem, aber vom Speicher eher ungünstig. Es ist also nicht verwunderlich dass es auch absolut keinen Unterschied macht, ob ich jetzt meinen Einzeiler oder deine ausgeschriebene Funktion verwende, denn: Wie ich bereits oben erklärt habe, funktioniert die Funktion selber absolut wunderbar.
Können wir dann also bitte zu dem eigentlichen Problem kommen? Dass nämlich der Rückgabewert der Funktion in Excel beim Sortieren zu #WERT! wird? Danke!
Viele Grüße,
Rico
____________________________________________________
"Mein Leipzig lob' ich mir, es ist ein Klein-Paris" (aus: "Faust")
| |
| Re: Worksheet_Change() führt zu #WERT! in Zellen | | | | Autor: MarkM | | Datum: 14.12.17 01:44 |
| Hey, wenn du eine Fehlerbehandlung in IsDigital einfügst, sieht du das Problem:
Function IsDigital(Cell As Range) As Boolean
On Error GoTo ErrorHandler
IsDigital = (Cell(1, 1).Interior.ColorIndex = 35)
Exit Function
ErrorHandler:
Debug.Print Cell.Address & ": " & Err.Description
End FunctionDu erhältst als Fehler bspw. "$D$4: Die Interior-Eigenschaft des Range-Objektes kann nicht zugeordnet werden."
Das liegt daran, dass deine Funktion - wie du schon richtig vermutet hast - während des Sortiervorgangs aufgerufen wird (siehst du auch schön im Calculate-Event des Worksheets).
Excel scheint hier nun Probleme zu haben. Das kann uns bei Sortiervorgängen, wo keine Wertänderungen eintreten (können), aber auch egal sein. Wir schalten die Berechnung vor dem Sortieren einfach aus und anschließend wieder ein:
Private Sub Worksheet_Change(ByVal Target As Range)
Set SortRange = Range(("A1"), Range("D5"))
Application.Calculation = xlCalculationManual
SortRange.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Application.Calculation = xlCalculationAutomatic
End SubBei mir war dein Problem reproduzierbar und mit diesem Ansatz behoben.
Schönen Abend noch!
Mark
Beitrag wurde zuletzt am 14.12.17 um 02:13:06 editiert. | |
| Re: Worksheet_Change() führt zu #WERT! in Zellen | | | | Autor: RF | | Datum: 14.12.17 23:23 |
| Hallo,
kurz und knapp: funktioniert auch bei mir. Ein ganz großes "Vielen Dank!"
Viele Grüße,
Rico
____________________________________________________
"Mein Leipzig lob' ich mir, es ist ein Klein-Paris" (aus: "Faust")
| |
 | Sie sind nicht angemeldet!
Um auf diesen Beitrag zu antworten oder neue Beiträge schreiben zu können, müssen Sie sich zunächst anmelden.
 Einloggen | Neu registrieren |
 |
|
sevISDN 1.0 
Überwachung aller eingehender Anrufe!
Die DLL erkennt alle über die CAPI-Schnittstelle eingehenden Anrufe und teilt Ihnen sogar mit, aus welchem Ortsbereich der Anruf stammt. Weitere Highlights: Online-Rufident, Erkennung der Anrufbehandlung u.v.m. Weitere InfosTipp des Monats sevWizard für VB5/6 
Professionelle Assistenten im Handumdrehen
Erstellen Sie eigene Assistenten (Wizards) im Look & Feel von Windows 2000/XP - mit allem Komfort und zwar in Windeseile :-) Weitere Infos
|
April 2024
Sie sind aktuell nicht angemeldet.