vb@rchiv
VB Classic
VB.NET
ADO.NET
VBA
C#
vb@rchiv Offline-Reader - exklusiv auf der vb@rchiv CD Vol.4  
 vb@rchiv Quick-Search: Suche startenErweiterte Suche starten   RSS-Feeds  | Newsletter  | Impressum  | Datenschutz  | vb@rchiv CD Vol.6  | Shop Copyright ©2000-2017
 
zurück

 Sie sind aktuell nicht angemeldet.Funktionen: Einloggen  |  Neu registrieren  |  Suchen

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 Sub
Das 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 Function
und 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 Function
dann 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")

Themenbaum einblendenGesamtübersicht  |  Zum Thema  |  Suchen

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

Themenbaum einblendenGesamtübersicht  |  Zum Thema  |  Suchen

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 Function
Das 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 Function
Als 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 Function
Schlussendlich 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 Function
Und 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")

Themenbaum einblendenGesamtübersicht  |  Zum Thema  |  Suchen

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 Function
In der fett dargestellten Zeile kannst Du den Rückgabewert "Boolean" erkennen...

EALA FREYA FRESENA

Themenbaum einblendenGesamtübersicht  |  Zum Thema  |  Suchen

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")

Themenbaum einblendenGesamtübersicht  |  Zum Thema  |  Suchen

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 Function
Du 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 Sub
Bei 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.
Themenbaum einblendenGesamtübersicht  |  Zum Thema  |  Suchen

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")

Themenbaum einblendenGesamtübersicht  |  Zum Thema  |  Suchen

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

Funktionen:  Zum Thema  |  GesamtübersichtSuchen 

nach obenzurück
 
   

Copyright ©2000-2017 vb@rchiv Dieter Otter
Alle Rechte vorbehalten.
Microsoft, Windows und Visual Basic sind entweder eingetragene Marken oder Marken der Microsoft Corporation in den USA und/oder anderen Ländern. Weitere auf dieser Homepage aufgeführten Produkt- und Firmennamen können geschützte Marken ihrer jeweiligen Inhaber sein.

Diese Seiten wurden optimiert für eine Bildschirmauflösung von mind. 1280x1024 Pixel