Office: Variable übergeben

fruitfly · 10. März 2014

Hallo liebe Forumsgemeinde,

ich bin ein VBA-Anfänger und habe erst letzte Woche begonnen, mich damit zu beschäftigen. Leider komme ich bei der Übergabe von Variabeln nicht zurecht. Anscheinend sind mir selbst die einfachsten Übungsbeispiele die dieses Thema behandeln zu hoch.

Ziel ist es, eine Formel in einen dynamischen Zellbereich einer Spalte zu schreiben. Das funktioniert auch ganz gut mit folgender Prozedur.

Code:

Sub FormelInZellenEinfügenDynamisch() 'fügt Formel dynamisch in Spalte "D" bis zu der Zelle ein, in der in Spalte "A" Werte stehen
Dim rngZelle As Range
Dim lngNr As Long
Dim strBisDahin As String

strBisDahin = Range("A65536").End(xlUp).Row

For Each rngZelle In ActiveSheet.Range("D2:D" & strBisDahin)
lngNr = rngZelle.Row
rngZelle.FormulaLocal = "=SUMME(A" & lngNr & ":C" & lngNr & ")"
Next rngZelle

End Sub
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Das Problem ist nun, dass ich gerne die benötigte Zeilennummer "strBisDahin" gerne vom folgender Prozedur in die oben gezeigte Prozedur übergeben möchte, so dass ich mir in der oberen den Code dafür spare.

Code:

Sub ZelleSuchen() 'gib die Zeilennummer zurück, bis dahin in Spalte "A" Werte stehen
Dim strBisDahin As String

strBisDahin = Range("A65536").End(xlUp).Row
' Debug.Print strBisDahin
End Sub
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Ich habe z.B. versucht die Variable "strBisDahin" modulöffentlich zu deklarieren, hier kam es aber dann in der Prozedur "FormelInZellenEinfügenDynamisch() " teilweise zu Fehlermeldungen und ich musste zuvor erst die Prozedur "ZelleSuchen()" ausführen.

Freue mich über jede Hilfe oder Erklärung.

viele Grüße

Alois

:)

hary · 11. März 2014

Moin Alois
Versuchs mal so, die Variable ausserhalb des Sub.
Code:

Dim strBisDahin As long
Sub ZelleSuchen()
strBisDahin = Range("A65536").End(xlUp).Row
End Sub
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

oder
Code:

Public strBisDahin As long
Sub ZelleSuchen()
strBisDahin = Range("A65536").End(xlUp).Row
End Sub
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

edit:
Und so reicht es auch:
Code:

Sub FormelInZellenEinfügenDynamisch()
ActiveSheet.Range("D2:D" & strBisDahin).FormulaLocal = "=SUMME(A2:C2)"
end sub
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

gruss hary

fruitfly · 11. März 2014

Hallo Harry,

vielen Dank für Deinen Beitrag!
Die beiden Varianten (ausserhalb Sub + Public) funktionieren.

Aber, eine Übergabe des Wertes von einer Sub zur anderen ist das jetzt nicht, oder?

Sollte ich da nicht mit Parametern, bzw. dem Befehl Call arbeiten?
Ich dachte immer ich müsste so etwas in der Art schreiben:
http://www.ms-office-forum.net/forum...d.php?t=241823

Ich frage deshalb, weil ich es mit einer modulöffentlichen Variable schon probiert hab und da musste ich (Grund kenne ich nicht) oftmals das Sub "ZelleSuchen" vorher ausführen, dass auch ein Wert im anderen Sub ankommt. Obwohl ich das Sub "ZelleSuchen" vorher schon des öfteren ausgeführt habe (Die Arbeitsmappe, war jedoch die ganze Zeit geöffnet).

Übrigens dein Code für das Sub zum Formel einfügen ist perfekt.
Sobald ich jetzt zusätzliche Werte in den Spalten A bis C eingebe, errechnet er mir automatisch das Ergebnis in D, OHNE das ich das Makro erneut laufen lassen muss. Vielen Dank hierfür!

Gruß

Alois

GMG-CC · 12. März 2014

Hallo Alois,

ich "grätsche" da mal rein. Richtig, das ist keine Übergabe; und vom Prinzip her hast du Recht mit dem von dir aufgezeigten Link. Speziell dazu noch eine Anmerkung: IMHO ist es "Sauberer", wenn du bei der Übergabe so arbeitest:
Code:

Call SubRoutine(Argumente)
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

also mit Call und kein Leerzeichen vor der öffnenden runden Klammer. Aber das nur nebenbei. Prinzipiell ist das, was du dort angesprochen hast, ein klassischer Fall für eine Function (Funktion), welche dann einen Wert zurück gibt. Ein Beispiel mit genau dieser Sachlage findest du bei Exel-ist-sexy.de in meinem Blog (der aber noch in den Kinderschuhen steckt).

EarlFred · 12. März 2014

Hallo Alois,

schau mal:
beginnend in der Sub "FormelInZellenEinfügenDynamisch"
Code:

Option Explicit

Sub FormelInZellenEinfügenDynamisch() 'fügt Formel dynamisch in Spalte "D" bis zu der Zelle ein, in der in Spalte "A" Werte stehen

ActiveSheet.Range("D2:D" & letzteZeile_SpalteA_AktivesBlatt).Formula = "=SUM(A2:C2)"

End Sub

Function letzteZeile_SpalteA_AktivesBlatt() As Long 'gib die Zeilennummer zurück, bis dahin in Spalte "A" Werte stehen

letzteZeile_SpalteA_AktivesBlatt = Application.Max(2, Cells(Rows.Count, 1).End(xlUp).Row)

End Function
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Der Weg "anders" herum (beginnend in der Sub "start"):
Code:

Option Explicit

Sub FormelInZellenEinfügenDynamisch(ByRef lngÜbergebeneLetzteZeile As Long) 'fügt Formel dynamisch in Spalte "D" bis zu der Zelle ein, in der in Spalte "A" Werte stehen

ActiveSheet.Range("D2:D" & lngÜbergebeneLetzteZeile).Formula = "=SUM(A2:C2)"

End Sub

Sub start()
Dim lngLetzteZeile As Long
lngLetzteZeile = Application.Max(2, Cells(Rows.Count, 1).End(xlUp).Row)

FormelInZellenEinfügenDynamisch lngLetzteZeile

End Sub
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Ob Du Dir nun Call angewöhnst oder nicht ist relativ egal - Du solltest aber beide Wege kennen und auch die Unterschiede und Einschränkungen. (Ich bin in meinem VBA-Leben bisher sehr gut ohne Call ausgekommen)

@Günther
dann sei aber so konsequent und nutze Call bei allen Methoden:
Code:

Call ActiveCell.Copy(Destination:=ActiveCell.Offset(, 1))
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

*wink.gif*

Grüße
EarlFred

fruitfly · 12. März 2014

Hallo EarlFred,

vielen Dank für Deine ausführliche Antwort und die Lösung meines "Problems" mittels zwei unterschiedlichen Wegen.
Das hat nun dazu geführt, dass es auch bei mir klick gemacht hat *Smilie

Hallo Günther,

auch Dir vielen Dank für´s reingrätschen *cool.gif*

Office: Variable übergeben

Variable übergeben

Variable übergeben

Variable übergeben

Variable übergeben - Similar Threads - Variable übergeben

Inhalt einer Variable an einen Filter übergeben

Funktion soll Wert in Variable übergeben (VBA)

Variablen einer UserForm übergeben

alle Excel Workbook Namen als Variablen übergeben

Anzahl Stellen in variable übergeben

Pfad an Variable übergeben

Variable in anderes Workbook übergeben