Cell Value bzw. Zellen Inhalt ändern über Function in VBA?
Hallo Zusammen, kann mir jemand sagen wie ich mithilfe einer Function in Excel VBA den Inhalt einer Zelle ändern kann? Ich habe hier ein kleines Beispiel, während der Code in einem Sub scheinbar ohne Probleme ausgeführt wird, scheint dieser in einer Function zum Abbruch zu führen.
Vielen Dank für jede Antwort im Voraus!
Gruß Leo
Beispiel:
Sub Test()
ActiveCell.Offset(1, 1) = "1"
End Sub
Function Test()
ActiveCell.Offset(1, 1) = "1"
End Function
4 Antworten
Soweit ich weiß, kann eine VBA Funktion nicht in eine andere Zelle schreiben, sondern nur lesen. Die Funktion kann nur den Wert der Zelle beeinflussen, aus der sie aufgerufen wird.
Was Du vorhast, geht nur über eine Sub().
Ich kenne auch keine einzige Original-Excel-Funktion, die in einer anderen Zelle etwas auslöst. Das ist eine ganz grundlegende Systematik von Excel.
Ich habe den Lösungsansatz aus folgendem Forum https://www.ozgrid.com/forum/index.php?thread/41643-can-a-subroutine-be-called-within-a-function/ genommen und festgestellt, dass wenn ich nun in den Sub die Zeile zum "ändern des Zelleninhaltes" einfüge, diese dort nun ebenfalls zum absturz führt.
Hier der Code mit der zum Absturz führenden Zeile auskommentiert:
Function MyFunc()
MsgBox "MyFunc"
Call MySub
Application.Run "MySub"
End Function
Sub MySub()
'ActiveCell.Offset(1, 1) = "1"
MsgBox "MySub"
End Sub
Mit VBA kann man in einem Excel Workbook nahezu alles machen.
Binde dies in einem Modul ein (oder Tabellenblatt in der VBA IDE) und Drücke F5
Der Code wird in genau dem Blatt Auswirkung haben welches aktuell zu sehen ist.
Private Function Test()
Range("A1").Value = 1
Cells(1,2).Value = 2 'Entspreicht der Zelle B1
Cells(1,3).Value = "Test" 'Entspreicht der Zelle C1
End Function
Vielen Dank für deine Antwort, es scheint in Excel nicht möglich zu sein, den Inhalt anderer Zellen mit einer Funktion zu beeinflussen.
Doch es ist möglich. ALLES ist in Bezug auf Zellen mit VBA möglich.
Schreibe in ein Modul:
Wenn du in A1 ne 2 und in B1 ne 3 hast und in C1: =myTest(A1;B1)
schreibst, dann wird in C1 ne 5 sein.
Public Function myTest(Zelle1 as Range, Zelle2 as Range) as Double
myTest = Zelle1 + Zelle2
End Function
sorry habe die Antwort scheinbar übersehen gehabt, danke dir nochmals. ich glaube, dass es nur möglich ist die jeweilige Zelle, in welcher die Funktion steht, durch ihren entsprechenden Rückgaberecht zu manipulieren. Versuche ich jedoch gleichzeitig andere Zellen, in welchen diese Funktion nicht steht, zu beeinflussen, führt dies zum crash.
Dies macht man dann NICHT auf diese Weise. Immer wenn sich ein Zellen halt ändert löst dies ein Chance event aus. Dort kann man alles machen. Beliebig viele Zellen verändern.
Eine Sub kann man z.B. einfach mit F5 ausführen - eine Function hingegen muß man aufrufen. Z.B. im Direktbereich mit "Call Test", oder auch einfach nur "Test" eingeben, da keine Argumente vorhanden sind.
Die Wahl wäre, es hier bei der Prozedur zu belassen, wenn einfach Zellen geändert werden sollen. Funktionen nutzt man, wenn man en Ergebnis zurückbekommen möchte.
Danke für deine schnelle Rückmeldung! Wirklich erstaunlich wie schnell das hier geht :) Ich habe zur Vereinfachung meines Problems hier nur einen kleinen Code als Beispiel aufgeführt. In meinem Aktuellen Projekt hingegen möchte ich auch einen Wert zurückgeben, sollte also eine Funktion verwenden. Kannst du mir erklären, wie man sowas typischer weise umsetzt oder wieso es bei meinem beispiel zum Abbruch kommt? Danke dir!
Ja, eine Function muß im Code immer irgendwo noch eine Zeile haben "Funktionsname = Ergebnis". Also, Beispiel:
Function RechteckUmfang(Breite As Double, Laenge As Double) As Double
Dim WrtTmp as Double
WrtTmp=2 * (Laenge + Breite)
RechteckUmfang=WrtTmp
End Function
Das heißt, es wurde eine Funktion erstellt zum Berechnen des Umfangs. Mit der Nennung des Funktionsnamen habe ich in den Klammern auch vereinbart, das der, der die Funktion aufruft, an erster Stelle in den Klammern einen Zahlenwert zu Breite und an zweiter Stelle einen Zahlenwert zu Länge angeben muß. Das Ergebnis der Funktion ist auch ein Double Datentyp, also Dezimalzahl mit max 15 Stellen.
Wenn Du diese Funktion in Deinem Projekt einfügst, kannst Du diese Funktion sogar schon über den Funktionsassistenten finden. Oder einfach in eine Zelle schreiben "=Rechteckumfang(A1;A2)" - wobei also in A1 die Breite und in A2 die Länge dann steht.
VG, Armin (edducato.de)
Viele Dank dass du dir so viel Zeit nimmst und mühe gibst mir weiter zu helfen!
Um mal bei deinem Beispiel zu bleiben, habe ich dieses genommen und mit meiner Zeile zum "Zellen Inhalt ändern" ergänzt.
Leider kommt es scheinbar immer noch zum Abbruch ..
Hier der Code:
Dim WrtTmp As Double
ActiveCell.Offset(1, 1) = "1"
WrtTmp = 2 * (Laenge + Breite)
RechteckUmfang = WrtTmp
End Function
Ich habe den Lösungsansatz aus folgendem Forum https://www.ozgrid.com/forum/index.php?thread/41643-can-a-subroutine-be-called-within-a-function/ genommen und festgestellt, dass wenn ich nun in den Sub die Zeile zum "ändern des Zelleninhaltes" einfüge, diese dort nun ebenfalls zum absturz führt.
Hier der Code mit der zum Absturz führenden Zeile auskommentiert:
Function MyFunc()
MsgBox "MyFunc"
Call MySub
Application.Run "MySub"
End Function
Sub MySub()
'ActiveCell.Offset(1, 1) = "1"
MsgBox "MySub"
End Sub
Wenn du den Text von deiner Funktion "Test()" einzeln so verwendest, läuft er korrekt.
Hast du sowohl die "sub" als auch die "function" im selben Modul? Das geht nicht, weil sie nicht denselben Namen haben dürfen.
Beim Abbruch wird ja auch immer Eine Fehlermeldung generiert. Da siehst du es dann ("ambiguous name" in dem Fall).
Vielen Dank für deine schnelle Antwort!
Ich habe es nun umbenannt in " Sub TestSub()" und "Function TestFunc()" und diese in 2 eigenständige Module getrennt. Leider scheint es bei der Function immer noch zum Abbruch zu kommen. Um ehrlich zu sein bin ich mir nicht sicher wo ich die Ursache für diesen Fehler auslesen kann, ein Fehler-Fenster öffnet sich jedenfalls nicht.
Starte es am besten mal in dem "Developer"-Fenster mit "Run". Dann geht es in den Debugger, wenn ein Fehler vorkommt.
Aber in diesem extrem einfachen Code ist echt kein Fehler…
Vielleicht bekomme ich keinen Fehler, weil es nicht so vorgesehen ist. Laut Hannes62a scheint es so zu sein: "Soweit ich weiß, kann eine VBA Funktion nicht in eine andere Zelle schreiben, sondern nur lesen. Die Funktion kann nur den Wert der Zelle beeinflussen, aus der sie aufgerufen wird.
Was Du vorhast, geht nur über eine Sub()."
Ich habe den Lösungsansatz aus folgendem Forum https://www.ozgrid.com/forum/index.php?thread/41643-can-a-subroutine-be-called-within-a-function/ genommen und festgestellt, dass wenn ich nun in den Sub die Zeile zum "ändern des Zelleninhaltes" einfüge, diese dort nun ebenfalls zum absturz führt.
Hier der Code mit der zum Absturz führenden Zeile auskommentiert:
Function MyFunc()
MsgBox "MyFunc"
Call MySub
Application.Run "MySub"
End Function
Sub MySub()
'ActiveCell.Offset(1, 1) = "1"
MsgBox "MySub"
End Sub
Die Schwierigkeit ist einfach die Referenz zu "ActiveCell", die offenbar nicht immer funktioniert. wenn ein anderes Workbook offen ist, oder wenn sich der Wert gerade ändert oder so.
Probier's vielleicht mit einer festen Range, etwas Range("A10"), um mal zu schauen, ob das passt.
Mit Function und Subroutine hat das alles nichts zu tun.
Auf meinem Rechner funktioniert schon dein urspruenglicher Code. Da muss irgendwie der Zugriff zu deinem aktiven Worksheet nicht passen.
Du kannst ja am Internet mal schauen: "When to use ActiveCell", da gibt es Warnungen.
Okay, gut zu wissen, dass mit einer Funktion scheinbar nicht der selbe Code ausführbar ist wie mit einem Sub. Ich hat bereits sowas in der Richtung befürchtet und versucht das Problem zu umgehen, indem ich in meiner Funktion einen Sub mit "Call" aufrufe. Beim debuggen ist macht es jedoch leider den Eindruck, dass er zwar durch "Call" in den Sub springt, aber am ende des Sub nicht mehr zurück in die Funktion geht. Nochmal vielen Dank für die Hilfe! Liebe Grüße Leo