Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender

Forumthread: Zelle per VBA berechnen

Zelle per VBA berechnen
18.05.2019 22:17:37
Onkel
Hallo,
ich möchte die Berechnung der Zellen über VBA durchführen. Folgende Formel habe ich, aber ich denke, daß die umständlich ist und einfacher geht.
Range("GZEG") = (Range("F") * Range("UG")) + ((Range("OG") / (Range("OG") - Range("UG"))) - (Range("UG") / (Range("OG") - Range("UG"))) * Range("F")) * (Range("STBR") - Range("UG"))
Wie geht es besser?
Viele Grüße
Uli
Anzeige

21
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Zelle per VBA berechnen
18.05.2019 22:45:34
onur
Gab es die überflüssigen Klammern im Angebot? Oder waren deren MHD abgelaufen?
Was soll das hier denn?
((Range("OG") / (Range("OG") - Range("UG"))) - (Range("UG") / (Range("OG") - Range("UG"))) 

Von den Klammern mal abgesehen - Dieser Ausdruck ergibt immer die Zahl 1.
Anzeige
AW: Zelle per VBA berechnen
18.05.2019 23:22:12
Onkel
Das ist die vorgegebene Gleichung.
F*UG+((OG/(OG-UG))-(UG/(OG-UG))*F)*(Steuerbrutto-UG)

Beschwerden nimmt der Fiskus entgegen. Das ist die Berechnung für das Gleitzonenentgeld.
AW: Zelle per VBA berechnen
18.05.2019 23:47:08
onur
Sorry - Bei all den KLammern bin ich selbst durcheinandergekommen.
Anzeige
AW: Zelle per VBA berechnen
18.05.2019 23:56:29
onur
Ich würde in die Formel nur Variablen-Namen nehmen statt Baereichsnamen - dann wird sie übersichtlicher.
AW: Zelle per VBA berechnen
19.05.2019 18:48:09
Onkel
Es sind doch keine Bereichsnamen in der Formel. Soll ich die Klammern auflösen oder was meinst Du?
AW: Zelle per VBA berechnen
19.05.2019 18:49:10
onur
Was ist denn dann Range("OG") ?
Anzeige
AW: Zelle per VBA berechnen
19.05.2019 18:52:49
Onkel
OG ist die obere Gleitzone, UG die untere. Also der Bereich, welcher die Gleitzone definiert.
AW: Zelle per VBA berechnen
19.05.2019 18:55:40
onur
RANGE heisst Bereich!
Also bin ich davon ausgegangen, dass der Wert für UG in einer Zelle mit dem Bereichsnamen "UG" steht, da es sonst KEINEN SINN machen würde, Range("UG") zu schreiben!
AW: Zelle per VBA berechnen
19.05.2019 19:03:30
Onkel
Mir kam die Formel schon falsch vor, aber ich bin in Excel nicht zuhause. Wie mache ich das besser?
Anzeige
AW: Zelle per VBA berechnen
19.05.2019 19:05:32
onur
Willst du denn die Werte der Variablen aus einer Tabelle variabel entnehmen oder sind sie fest
vorgegeben?
AW: Zelle per VBA berechnen
19.05.2019 19:30:50
Onkel
Ich möchte zwei Tabellenblätter nutzen.
1. Tabellenblatt enthält die Werte, die sich unregelmäßig ändern.
2. Tabellenblatt zeigt die berechneten Werte an.
Die Tabelle soll Gehaltszettel im Gleitzonenbereich berechnen.
Anzeige
AW: Zelle per VBA berechnen
19.05.2019 19:37:37
onur
Dann brauchst eigentlich nicht mal VBA.
Auf dem ersten Blatt musst du den Feldern mit den Werten passende Namen vergeben und auf Blatt 2 mit den Namen eine Formel erstellen.
Siehe hier:
https://www.herber.de/bbs/user/129875.xlsm
Anzeige
AW: Zelle per VBA berechnen
19.05.2019 19:59:51
Onkel
Das habe ich schon, aber wenn ich einen Fehler in einer Zelle habe oder den Namen einer Zelle ändern muß, geht die Sucherei los. Mit VBA hoffe ich das zu umgehen.
AW: Zelle per VBA berechnen
19.05.2019 20:06:16
onur
Dann mach es so:
Dimensioniere die Variablennamen:
Dim OG,UG,F,Steuerbrutto,Ergebnis
Dann ordne Diesen die passenden Zellen zu:
OG=Sheest("Tabelle1").Range("A2")
UG=Sheest("Tabelle1").Range("B2")
usw usw
Dann kannst du mit diesen Variablen arbeiten:
Ergebnis = F*UG+((OG/(OG-UG))-(UG/(OG-UG))*F)*(Steuerbrutto-UG)
Anzeige
AW: Zelle per VBA berechnen
19.05.2019 20:09:42
Onkel
Danke
Uli
AW: Zelle per VBA berechnen
19.05.2019 20:11:03
onur
Natürlich
OG=Sheets("Tabelle1").Range("A2")
statt
OG=Sheest("Tabelle1").Range("A2")
AW: Zelle per VBA berechnen
23.05.2019 10:23:48
Onkel
Ich brauch nochmal Deine fachmännische Antwort. Wie kann ich die Formel berechnen und in einer Zelle anzeigen?
Sub Berechnung()
ActiveWorkbook.Names.Add Name:="PN", RefersToR1C1:="=Daten!R7C4" 'PN = Personalnummer
ActiveWorkbook.Names.Add Name:="Vorname", RefersToR1C1:="=Daten!R79C4"
ActiveWorkbook.Names.Add Name:="Name", RefersToR1C1:="=Daten!R11C4"
ActiveWorkbook.Names.Add Name:="Geburtsdatum", RefersToR1C1:="=Daten!R13C4"
ActiveWorkbook.Names.Add Name:="Anrede", RefersToR1C1:="=Daten!R15C4"
ActiveWorkbook.Names.Add Name:="Straße", RefersToR1C1:="=Daten!R17C4"
ActiveWorkbook.Names.Add Name:="PLZ", RefersToR1C1:="=Daten!R19C4"
ActiveWorkbook.Names.Add Name:="Ort", RefersToR1C1:="=Daten!R21C4"
ActiveWorkbook.Names.Add Name:="Bundesland", RefersToR1C1:="=Daten!R23C4"
ActiveWorkbook.Names.Add Name:="Lohnjahr", RefersToR1C1:="=Daten!R25C4"
ActiveWorkbook.Names.Add Name:="Lohnmonat", RefersToR1C1:="=Daten!R27C4"
ActiveWorkbook.Names.Add Name:="Gleitzone", RefersToR1C1:="=Daten!R29C4"
ActiveWorkbook.Names.Add Name:="Kinder", RefersToR1C1:="=Daten!R31C4"
ActiveWorkbook.Names.Add Name:="Firmeneintritt", RefersToR1C1:="=Daten!R33C4"
ActiveWorkbook.Names.Add Name:="Kirche", RefersToR1C1:="=Daten!R35C4"
ActiveWorkbook.Names.Add Name:="Freibeträge", RefersToR1C1:="=Daten!R37C4"
ActiveWorkbook.Names.Add Name:="Steuerklasse", RefersToR1C1:="=Daten!R39C4"
ActiveWorkbook.Names.Add Name:="ÖffentlDienst", RefersToR1C1:="=Daten!R41C4"
ActiveWorkbook.Names.Add Name:="Grundgehalt", RefersToR1C1:="=Daten!R7C8"
ActiveWorkbook.Names.Add Name:="F", RefersToR1C1:="=Daten!R9C8" 'Gleizonenfaktor
ActiveWorkbook.Names.Add Name:="GZEG", RefersToR1C1:="=Daten!R11C8" 'Gleitzonenentgeld
ActiveWorkbook.Names.Add Name:="AG_SVBrutto", RefersToR1C1:="=Daten!R11C9" 'Arbeitgeber SV- _
Brutto
ActiveWorkbook.Names.Add Name:="UG", RefersToR1C1:="=Daten!R13C8" 'Untere Gleitzone
ActiveWorkbook.Names.Add Name:="OG", RefersToR1C1:="=Daten!R15C8" 'Obere Gleitzone
ActiveWorkbook.Names.Add Name:="BSKV", RefersToR1C1:="=Daten!R20C8" 'Beitragssatz  _
Krankenversicherung
ActiveWorkbook.Names.Add Name:="BSZusatz", RefersToR1C1:="=Daten!R22C8" 'Beitragssatz  _
Zusatzversicherung
ActiveWorkbook.Names.Add Name:="BSRV", RefersToR1C1:="=Daten!R24C8" 'Beitragssatz  _
Rentenversicherung
ActiveWorkbook.Names.Add Name:="BSPV", RefersToR1C1:="=Daten!R26C8" 'Beitragssatz  _
Pflegepflicht
ActiveWorkbook.Names.Add Name:="BSAL", RefersToR1C1:="=Daten!R28C8" 'Beitragssatz  _
Arbeitslosenversicherung
ActiveWorkbook.Names.Add Name:="BSInsolvenz", RefersToR1C1:="=Daten!R30C8" 'Beitragssatz  _
Insolvenz
ActiveWorkbook.Names.Add Name:="BSU1", RefersToR1C1:="=Daten!R32C8" 'Beitragssatz Umlage U1  _
Krankheit
ActiveWorkbook.Names.Add Name:="BSU2", RefersToR1C1:="=Daten!R34C8" 'Beitragssatz Umlage U2  _
Schwangerschaft
ActiveWorkbook.Names.Add Name:="SachSVfrei", RefersToR1C1:="=Daten!R37C8" 'Sachwert SV-frei
ActiveWorkbook.Names.Add Name:="Ergebnis", RefersToR1C1:="=Daten!R44C8"
Dim UG, OG, F, Ergebnis, AG_SVBrutto, GZEG As Double
Dim Vorname, Name, Anrede, Straße, Ort, Bundesland As String
Dim ÖffentlDienst, Kirche, Gleitzone, Kinder As Boolean
Dim Geburtsdatum, Firmeneintritt As Date
Range("Ergebnis").Formula = F * UG + ((OG / (OG - UG)) - (UG / (OG - UG)) * F) * ( _
AG_SVBrutto - UG) 'Fehler Überlauf
ActiveCell.FormulaR1C1 = "= F * UG + ((OG / (OG - UG)) - (UG / (OG - UG)) * F) * ( _
AG_SVBrutto - UG)" 'Schreibt Formel in Zelle und die Zelle muß aktiviert werden
Range("Ergebnis") = (Range("F") * Range("UG")) + ((Range("OG") / (Range("OG") - Range("UG")) _
) - (Range("UG") / (Range("OG") - Range("UG"))) * Range("F")) * (Range("AG_SVBrutto") - Range("UG")) 'Geht, aber Formel erscheint mir unmöglich
End Sub

Die erste Berechnung bringt einen Fehler (Überlauf), die zweite muss die Zelle aktiviert werden und die dritte finde ich unmöglich.
Was ist der bessere Befehl?
VG Uli
Anzeige
AW: Zelle per VBA berechnen
23.05.2019 12:48:52
onur
ENTWEDER Namen ODER Variablen - aber nicht Beides!
Du kannst z. B. UG nur als Namen oder als Variable benutzen, aber nicht gleichzeitig.
AW: Zelle per VBA berechnen
23.05.2019 13:45:41
Onkel
OK, nehme ich raus. Wie kann ich verhindern, daß die Formeln in die Zelle geschrieben werden?
"Im Kopf rechnen" und nur das Ergebnis in die Zelle eintragen.
Anzeige
AW: Zelle per VBA berechnen
23.05.2019 14:08:39
onur
ENTWEDER Namen ODER Variablen - aber nicht Beides!
Du kannst z. B. UG nur als Namen oder als Variable benutzen, aber nicht gleichzeitig.
AW: Zelle per VBA berechnen
23.05.2019 10:46:09
Onkel
Ich habe einen Fehler gefunden.
Falsch ist:
Range("Ergebnis").Formula = F * UG + ((OG / (OG - UG)) - (UG / (OG - UG)) * F) * (AG_SVBrutto - UG) 'Fehler Überlauf

Richtig:
Range("Ergebnis") = "=F * UG + ((OG / (OG - UG)) - (UG / (OG - UG)) * F) * (AG_SVBrutto - UG)"
Leider steht halt der Code dann in der Zelle.
Anzeige
;
Anzeige
Anzeige

Infobox / Tutorial

Zelle per VBA berechnen


Schritt-für-Schritt-Anleitung

  1. Öffne Excel und drücke ALT + F11, um den VBA-Editor zu öffnen.
  2. Erstelle ein neues Modul: Klicke mit der rechten Maustaste auf "VBAProject (DeineDateiName)" und wähle "Einfügen" > "Modul".
  3. Definiere die Variablen: Schreibe den folgenden Code, um die Variablen für die Berechnung zu deklarieren:
    Dim UG As Double, OG As Double, F As Double, Ergebnis As Double
  4. Weise die Werte zu: Weise die Zellen den Variablen zu, die die Werte enthalten:
    UG = Sheets("Tabelle1").Range("B2").Value
    OG = Sheets("Tabelle1").Range("A2").Value
    F = Sheets("Tabelle1").Range("C2").Value
  5. Berechne das Ergebnis: Verfasse die Berechnung:
    Ergebnis = F * UG + ((OG / (OG - UG)) - (UG / (OG - UG)) * F) * (Steuerbrutto - UG)
  6. Gib das Ergebnis in einer Zelle aus:
    Sheets("Tabelle2").Range("D2").Value = Ergebnis
  7. Führe das Makro aus: Schließe den VBA-Editor und gehe zurück zu Excel. Drücke ALT + F8, wähle dein Makro aus und klicke auf "Ausführen".

Häufige Fehler und Lösungen

  • Fehler: Überlauf
    Dieser Fehler kann auftreten, wenn die Variablen nicht korrekt definiert sind oder die Werte außerhalb des zulässigen Bereichs liegen. Stelle sicher, dass die Variablen als Double deklariert sind.

  • Fehler in der Formel
    Achte darauf, dass Du die Formel richtig eingegeben hast. Wenn Du Variablen verwendest, stelle sicher, dass sie nicht gleichzeitig als Zellnamen verwendet werden. Beispiel:

    Range("Ergebnis").Value = F * UG + ...
  • Formel erscheint in der Zelle
    Wenn die Formel in der Zelle erscheint, statt das Ergebnis anzuzeigen, überprüfe, ob Du die richtige Zuweisung verwendest:

    Range("Ergebnis").Value = Ergebnis

Alternative Methoden

Falls Du nicht mit VBA arbeiten möchtest, kannst Du auch die Berechnung direkt in Excel-Formeln durchführen. Benutze dafür benannte Bereiche, die Du über die "Formel"-Leiste definieren kannst.

  1. Wähle die Zelle aus, in der Du die Berechnung durchführen möchtest.
  2. Gib die Formel ein, die die Werte aus anderen Zellen verwendet, um das Gleitzonenentgelt zu berechnen:
    =F * UG + ((OG / (OG - UG)) - (UG / (OG - UG)) * F) * (Steuerbrutto - UG)

Praktische Beispiele

Hier ist ein einfaches Beispiel, wie Du die Zellen mit VBA berechnen kannst:

Sub Berechnung()
    Dim UG As Double, OG As Double, F As Double, Steuerbrutto As Double
    UG = Sheets("Tabelle1").Range("B2").Value
    OG = Sheets("Tabelle1").Range("A2").Value
    F = Sheets("Tabelle1").Range("C2").Value
    Steuerbrutto = Sheets("Tabelle1").Range("D2").Value

    Dim Ergebnis As Double
    Ergebnis = F * UG + ((OG / (OG - UG)) - (UG / (OG - UG)) * F) * (Steuerbrutto - UG)

    Sheets("Tabelle2").Range("D2").Value = Ergebnis
End Sub

Tipps für Profis

  • Verwende benannte Bereiche, um die Lesbarkeit Deiner Formeln zu erhöhen. Du kannst z.B. die Zellen für UG und OG benennen und diese Namen in Deinen Formeln verwenden.
  • Debugging: Nutze Debug.Print, um den Wert von Variablen während der Ausführung zu überprüfen.
  • Fehlerbehandlung: Implementiere eine Fehlerbehandlung in Deinen VBA-Skripten, um unerwartete Probleme abzufangen.

FAQ: Häufige Fragen

1. Wie kann ich sicherstellen, dass meine Variablen korrekt deklariert sind?
Stelle sicher, dass Du die Variablen vor der Verwendung deklarierst und den richtigen Datentyp wählst, z.B. Dim UG As Double.

2. Kann ich VBA verwenden, um Berechnungen in Excel zu automatisieren?
Ja, VBA ist eine hervorragende Möglichkeit, um Berechnungen in Excel zu automatisieren und komplexe Aufgaben effizient zu erledigen.

3. Was mache ich, wenn die Formel nicht das erwartete Ergebnis liefert?
Überprüfe Deine Formel auf syntaktische Fehler und stelle sicher, dass alle verwendeten Variablen korrekt zugewiesen sind.

4. Wie kann ich die Berechnung ohne VBA durchführen?
Du kannst die Berechnung auch direkt in einer Excel-Zelle durchführen, indem Du die entsprechenden Formeln eingibst und die benötigten Zellreferenzen verwendest.

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige