Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender

Forumthread: Variable in VBA ermitteln und als Wert übergeben

Variable in VBA ermitteln und als Wert übergeben
24.02.2019 15:51:18
AnBad
Hallo,
ist es möglich eine in VBA ermittelten Wert an einen Namen zu übergeben, so dass dieser Wert in normalen Exelformeln genutzt werden kann?
Beispiel:
Ich ermittle die letzte Zeile mit:
LastRow = Cells(Rows.Count, 4).End(xlUp).Row
Und später möchte ich diesen Wert dann in einer Formel nutzen z.B.
=Summe(A1:A[LastRow]
Ist das möglich?
Anzeige

7
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Variable in VBA ermitteln und als Wert übergeben
24.02.2019 15:55:50
Hajo_Zi
vba ist englisch.
WorksheetFunction.Sum(Range("A1:A" & Lastrow))

Beiträge von Werner, Luc, robert, J.O.Maximo und folgende lese ich nicht.
Die Beiträge werden auch ignoriert, es erfolgt keine Antwort.
AW: Variable in VBA ermitteln und als Wert übergeben
26.02.2019 18:30:30
AnBad
Feedback:
Hier liegt vlt. ein Missverständnis vor. Ich versuche meine Frage nochmals, anders zu formulieren.
In VBA berechnet man einen Wert und weist ihn einer Variable z.B. "VariableA" zu.
Wie kann man diesen Wert, bzw. diesen Variablennamen "VariableA" in einer normalen Formel nutzen? Durch Zuweisung eines Namens? Gernerierung einer Function()?
z.B.
=B1 + "VariableA" oder

=C9 x "VariabelA" + 1
Grund der Frage (nicht unbedingt lesenswert, da nicht wichtig, denke ich):
In B2 habe ich eine Summenformel (=Summe(B10:B100)). Manchmal endet die Tabelle bereits bei B20, manchmal bei B80. Zwei Zeilen unter der Zelle letzten Zelle, soll mir VBA auch einen Wert eintragen, der aber nicht aufsummiert werden soll. D.h: die Summenformel soll nicht fest bis B100 gehen sondern dynamisch, bestimmt durch VBA enden. Ja, jetzt kann man die Summenformeln (=Summe(B10:B100)) sicherlich komplett durch VBA erstetzen oder umgekehrt irgenwie mit einer normalen Formel die Max.Zelle ermitteln und -2 nehmen. Dies wollte ich jedoch nicht, da in machen Zellen ab B10 auch Text steht, und mir VBA Fehler ausgibt und umgekehrt, wenn der Wert in VBA bereits vorliegt, bedarfs es keine kompizierte Formel in Excel. Außerdem wollte ich aus SIcherheitsgründen für den Anwendungsfall die Summierung als Formel belassen und in einer weiteren Zelle ein Überpfürung duch VBA etablieren.
Dies ist ein Problem, welches jetzt kurzfristig gelöst werden müsste.
Anzeige
AW: Variable in VBA ermitteln und als Wert übergeben
26.02.2019 21:58:17
Dieter
Hallo AnBad,
du kannst per VBA entweder einem bereits vorhandenen Namen einen Wert zuweisen oder einen neuen Namen definieren und diesem Namen auch einen Wert zuweisen.
Das sieht z.B. folgendermaßen aus:
Sub Wert_zuweisen()
' Wert an einen vorhandenen Namen zuweisen.
Dim NameExistiert As Boolean
Dim nm As Name
Dim wb As Workbook
Dim ws As Worksheet
Dim zeileA As Long
Set wb = ThisWorkbook
Set ws = wb.Worksheets(1)
zeileA = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
For Each nm In wb.Names
If nm.Name = "LetzteZeileA" Then
NameExistiert = True
Exit For
End If
Next nm
If NameExistiert Then
Set nm = wb.Names("LetzteZeileA")
nm.Value = zeileA
Else
MsgBox "Der Name """ & "LetzteZeileA" & """ existiert nicht."
End If
End Sub
Sub Namen_per_VBA_Definieren()
' Neuen Namen erzeugen und Wert zuweisen
' Das Programm kann auch ausgeführt werden, wenn
' der Name bereits existiert.
Dim nm As Name
Dim wb As Workbook
Dim ws As Worksheet
Dim zeileB As Long
Set wb = ThisWorkbook
Set ws = wb.Worksheets(1)
zeileB = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
Set nm = ws.Names.Add(Name:="LetzteZeileB", RefersTo:="=" & CStr(zeileB))
End Sub
Die Namen verwendest du dann als Variable in deinem Tabellenblatt, wie gewohnt.
https://www.herber.de/bbs/user/127954.xlsm
Mit freundlichen Grüßen
Dieter Klemke
Anzeige
AW: Variable in VBA ermitteln und als Wert übergeben
26.02.2019 23:49:16
Werner
Hallo,
so:
Public Sub aaa()
Dim loLetzte As Long
With Worksheets("DeinBlatt")
loLetzte = .Cells(.Rows.Count, 2).End(xlUp).Row
.Cells(2, 2) = WorksheetFunction.Sum(.Range(.Cells(10, 2), .Cells(loLetzte, 2)))
.Cells(loLetzte, 2).Offset(2) = 50
End With
End Sub
Gruß Werner
Anzeige
AW: Variable in VBA ermitteln und als Wert übergeben
01.03.2019 13:34:53
AnBad
Hallo Werner,
vielen Dank. Habe die Formel für meinen Bedarf jetzt angepasst:
(Das Anpassen kann man bestimmt noch professioneller machen, aber mir tut es das erstmal)
Option Explicit
Public Sub SummenSpalten()
Dim LastRow As Long
With Worksheets("Kalkulation")
LastRow = .Cells(.Rows.Count, 5).End(xlUp).Row
'.Cells(33, 7) = WorksheetFunction.Sum(.Range(.Cells(19, 7), .Cells(loLetzte, 7)))
.Cells(LastRow, 7).Offset(3).Clear
.Cells(LastRow, 6).Offset(3).Clear
.Cells(LastRow, 6).Offset(3) = "Gesamt, Netto:"
.Cells(LastRow, 7).Offset(3) = WorksheetFunction.Sum(.Range(.Cells(19, 7), .Cells(LastRow,  _
7)))
.Cells(LastRow, 7).Offset(3).NumberFormat = "#,##0.00 €"
End With
End Sub

Anzeige
AW: Variable in VBA ermitteln und als Wert übergeben
24.02.2019 16:01:53
Sepp
Hallo ?,
das hgeht auch ohne VBA.
Tabelle1

 ABCD
120 871
253  2
314  3
462   
591   
619   
727   
850   
958   
1089   
1142   
1287   
1372   
1471   
1589   
1691   
1743   
1832   
1982   
2023   
2119   
2257   
2312   
2441   

Formeln der Tabelle
ZelleFormel
C1=SUMME(A1:INDEX(A:A;lastRow))
Namen in Formeln
ZelleNameBezieht sich auf
C1lastRow=VERWEIS(2;1/(Tabelle1!$D:$D<>"");Tabelle1!$D:$D)
Namen verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
http://www.excelformeln.de/formeln.html?welcher=48
 ABCDEF
1Gruß Sepp
2
3

Anzeige
aha...
24.02.2019 16:16:08
Werner
Hallo,
...neues Problem, aber beim "alten Problem" reicht es nicht mehr eine Antwort zu geben.
Gruß Werner
;

Forumthreads zu verwandten Themen

Anzeige
Anzeige
Anzeige
Entdecke relevante Threads

Schau dir verwandte Threads basierend auf dem aktuellen Thema an

Alle relevanten Threads mit Inhaltsvorschau entdecken
Anzeige
Anzeige

Infobox / Tutorial

VBA-Variablen in Excel-Formeln nutzen


Schritt-für-Schritt-Anleitung

  1. VBA-Variable definieren: Zuerst musst du eine VBA-Variable definieren und ihr einen Wert zuweisen. Hier ist ein einfaches Beispiel:

    Dim LastRow As Long
    LastRow = Cells(Rows.Count, 4).End(xlUp).Row
  2. Wert an einen Namen zuweisen: Du kannst den Wert der Variablen einem Namen in Excel zuweisen, damit du ihn später in Formeln verwenden kannst.

    ThisWorkbook.Names.Add Name:="LetzteZeile", RefersTo:="=" & LastRow
  3. Formel in Excel verwenden: Nun kannst du den Namen in einer Excel-Formel nutzen:

    =SUMME(A1:A[LetzteZeile])

    Beachte, dass du die Formel anpassen musst, um den dynamischen Bereich korrekt zu nutzen.


Häufige Fehler und Lösungen

  • Fehler: Name nicht gefunden: Wenn du beim Zugriff auf einen Namen in Excel einen Fehler erhältst, stelle sicher, dass der Name korrekt definiert wurde. Verwende die folgende Methode, um zu prüfen, ob der Name existiert:

    Dim nm As Name
    On Error Resume Next
    Set nm = ThisWorkbook.Names("LetzteZeile")
    On Error GoTo 0
    If nm Is Nothing Then
        MsgBox "Der Name existiert nicht."
    End If
  • Fehler: Ungültige Formel: Wenn deine Formel einen Fehler ausgibt, überprüfe die Syntax und stelle sicher, dass die verwendeten Namen korrekt sind.


Alternative Methoden

  1. Namen in Excel definieren: Du kannst Namen in Excel direkt über die Namensbox definieren, ohne VBA zu verwenden. Gehe dazu auf das Feld links von der Formelleiste und gib den gewünschten Namen ein.

  2. Verwendung von INDEX und VERGLEICH: Anstatt VBA zu verwenden, kannst du auch Excel-Formeln nutzen, um dynamische Bereiche zu definieren:

    =SUMME(A1:INDEX(A:A, LastRow)

    Dabei steht LastRow für die Zeilennummer, die du zuvor ermitteln musst.


Praktische Beispiele

  • Dynamische Summenformel: Du kannst eine dynamische Summenformel erstellen, die den Bereich basierend auf dem letzten gefüllten Wert in einer Spalte anpasst:

    Public Sub SummenSpalten()
        Dim LastRow As Long
        With Worksheets("Kalkulation")
            LastRow = .Cells(.Rows.Count, 5).End(xlUp).Row
            .Cells(LastRow, 6).Offset(3) = WorksheetFunction.Sum(.Range(.Cells(10, 6), .Cells(LastRow, 6)))
        End With
    End Sub
  • Wert aus Zelle in Variable: Du kannst auch direkt einen Wert aus einer Zelle in eine VBA-Variable laden:

    Dim Wert As Double
    Wert = Cells(10, 2).Value ' B10 Wert

Tipps für Profis

  • Option Explicit verwenden: Schalte Option Explicit ein, um sicherzustellen, dass alle Variablen deklariert werden. Dies hilft, Fehler zu vermeiden.

  • Verwende Namenskonventionen: Halte dich an eine klare Namenskonvention für deine Variablen und Namen in Excel. Das macht deinen Code und deine Excel-Formeln verständlicher.

  • Testen von Formeln: Teste deine Formeln in einer leeren Zelle, bevor du sie in den endgültigen Bereich einfügst, um sicherzustellen, dass sie wie erwartet funktionieren.


FAQ: Häufige Fragen

1. Wie kann ich eine VBA-Variable in einer Excel-Formel verwenden?
Um eine VBA-Variable in einer Excel-Formel zu verwenden, musst du den Wert der Variable einem Namen zuweisen und diesen Namen in der Formel verwenden.

2. Was ist der Unterschied zwischen einer normalen Excel-Formel und einer VBA-Funktion?
Eine Excel-Formel wird direkt in die Zelle eingegeben, während eine VBA-Funktion in einem Makro definiert wird und komplexere Berechnungen durchführen kann.

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