VBA Code ist zu statisch

[DieMarie]

Hallo zusammen,

ich bin was VBA angeht wirklich nur in den Basics unterwegs. 
Häufig baut man etwas auf in der Annahme dass es für immer so bleibt - und später stellt sich häufig heraus dass es noch zig Änderungen gibt. 
So auch wieder kürzlich passiert.

Ich verweise in meinem Code hart auf Spalten oder Zellen. 
Ich sage z.B. "Wenn Button X aktiviert wird, klappe Spalte X ein"
Oder "Hole den Wert aus Zelle X15 und schreibe ihn in Zelle Z20"

Das funktioniert soweit auch alles bestens. 
Aber sobald sich etwas ändert und man noch weitere Spalten oder Zeilen einfügen muss bekomme ich die Krise, weil ich alle harten Verlinkungen im Code anpassen muss. 

Meine Frage für zukünftige Projekte --> wie macht man das professioneller, so dass man Zeilen / Spalten einfügen kann ohne alle Bezüge manuell ändern zu müssen?

[RPP63]

Moin!
Man verzichtet auf VBA und schult (zur Not) die Anwender, die sich mit der Excel-Oberfläche nicht auskennen.
Nichts von Deinen oben angemerkten Aktionen lässt sich nicht innerhalb von Sekunden mittels GUI lösen.

Gruß Ralf

[Ralf A]

Hi Marie,

ganz pauschal beantwortet hilft Dir das auch nicht weiter, denn die pauschale Antwort würde lauten: Alles, was man gern variabel hätte, muss man auch als Variable definieren.

Da aber niemand weiß, was nun konkret variabel gebraucht wird (ein Range, eine Zeile, eine Spalte, ein Wert, ein Objekt... ) kann es auch keine allgemeingültige Antwort darauf geben.

Mal ein Beispiel für eine Ganzzahl:

Code:

Dim i as Integer
i = 14

MsgBox i

oder i als Zeilennr einer Zelle:

Code:

Dim i as Integer
i = Cells(14,2).Row
'oder

i = Range("B14").Row

MsgBox i

oder für einen Range (der als Objekt 'gesettet' werden muss): 

Code:

Dim rng as Range
Set rng = Tabelle1.Range("B14:C28")

MsgBox rng.Address

oder diesen Range mit der Integervariablen kombiniert:

Code:

Set rng = Tabelle1.Range("B" & i & ":C28")
'oder
Set rng = Tabelle1.Range("B" & i & ":C" & i * 2)

MsgBox rng.Address

Üblicherweise schafft man sich separate Prozeduren/Funktionen um diese Variablen zu ermitteln oder sie als Parameter zu übergeben. Aber... das führt jetzt zu weit.... Da hilft wohl wirklich nur lernen, lernen, lernen....

[d'r Bastler]

Moin Marie,

Ralf A hat Dir schon den Weg gewiesen. Dazu folgende Ergänzung: Im Prinzip kannst Du die Werte aller Variablen in einer (ich verstecke sie gerne) Tabelle speichern und daraus z.B. Arrays befüllen.

Und um die Vorteile von For...Next-Schleifen zu nutzen, habe ich mir fast vollständig abgewöhnt Zellen/Spalten alphanumerisch zu adressieren und bevorzuge die Methode cells(Zeile,Spalte). Dann kann man Verschiebungen z.B. leichter mit Offset realisieren. Problem dabei: Du müsstest die Variablen als Public deklarieren, was leider den Nachteil hat, dass besonders bei umfangreichem Code leicht mal ein falscher Wert erzeugt wird, der dann für Chaos sorgt. Da hilft dann die Alternative der Parameterübergabe.

Schöne Grüße

[Ralf A]

Im Prinzip kannst Du die Werte aller Variablen in einer (ich verstecke sie gerne) Tabelle speichern 

...und welchen Nutzen soll das haben? Wenn die Werte unveränderlich sind, kann ich sie gleich als Konstante deklarieren, wenn sie veränderlich (variabel) sind bringt es nichts, sie extra in einem Blatt zu speichern. Ich hab sie ja bereits als Variable deklariert, dann nutze ich den Variablennamen. Deshalb deklariere ich sie ja als Variable! 
Was Du meinst, ist die Sichtbarkeit, also wo ist die Variable gültig. Das ist aber ein ganz anderes Thema.
Ich empfehle daher, den Rat des Bastler's ganz schnell wieder zu vergessen. Sorry Bastler, das ist nicht persönlich gemeint, aber Dein Rat ist wirklich nur Bastelei....

[NobX]

Hallo Marie,

was Du schreibst, ist mir am Anfang auch passiert. Ich habe dann wann immer möglich diese Bereiche oder auch einzelne Zellen mit Namen versehen und mit diesen Namen im Code gearbeitet. Vorteil: wenn es zu solchen Verschiebungen kommt, wie Du es beschrieben hast, muss der Code nicht angepasst werden, da die Namen "mitwandern".

Z. B. Dein X15 gibst Du einen aussagekräftigen Namen wie etwa Summe_Neu oder ..... irgendetwas anderes (im Namensmanager von Excel)
Im Code dimensionierst Du eine Variable z. B. Dim i As Long oder ....... was Du brauchst (Range, Integer etc.)
i = Tabelle1.Range("Summe_Neu").Row
oder wie ähnlich im Beitrag von Ralf A beschrieben in anderen Konstellationen bei Bereichen als Range

Grüße

Norbert.

[d'r Bastler]

@ Ralf A

Na, dann packe ich jetzt mal meine Laubsäge aus und säge Dir einen, dass es staubt! 

Im Prinzip kannst Du ...

Da hast Du zwar richtig zitiert, aber den Inhalt wohl nicht verstanden... Nimm's nicht persönlich, aber wer lesen kann, ist klar im Vorteil   




Mein Ansatz zielt nicht darauf ab, tatsächlich alle Variablen in eine Tabelle zu schreiben, was ein Unfug?! Es geht darum, die Variablen, die im Code (der bekanntlich etwas vulnerabler ist, als eine Tabelle) versteckt sind und gelegentlich an neue Tabellenlayouts angepasst werden müssen, an einer zentralen Stelle zu verwalten.



Als Beispiel: Ich habe ein Projekt gebastelt (ja, da stehe ich zu!!), das in mehreren Sprachen genutzt wird. Entsprechend müssen ListView-Überschriften, CommandButton-Captions, Label-Caption, uswusf. in der entsprechenden Sprache angezeigt werden. Also habe ich diesen Properties im Code einen Namen verpasst, der sich seinen Wert je nach Auswahl der Sprache aus der passenden Spalte einer Tabelle holt. Deutsch in Spalte A, Englisch in Spalte B, Mandarin in Spalte C, etc. Das ganze Projekt nun noch um Kantonesisch zu ergänzen bedarf also lediglich einer neuen Spalte mit den passenden Übersetzungen. Im Code selbst ändere ich dafür nicht eine Zeile.



Ich habe nicht nur eine App auf dem Rechner, in der solche Lokalisierungen über externe .txt-Dateien gelöst werden. XL kann das in einer Tabelle. Dieses Beispiel lässt sich auf andere Fälle der Änderung anwenden. Das hat also genau nichts mit Konstanten oder Variablen zu tun, sondern hilft der Übersichtlichkeit und beim Schutz des Cod



Falls Du jetzt einen Handfeger brauchst, lass es mich wissen ...



Grüße



@Marie: Man muss nicht alles glauben, was Ralf A so schreibt. Aber sein erster Ansatz war ganz gut.

[Ralf A]

Als Beispiel: Ich habe ein Projekt gebastelt (ja, da stehe ich zu!!), das in mehreren Sprachen genutzt wird.

Tut mir leid, wenn ich Dir widersprechen muss. Ich brauch auch keinen Handfeger, sondern eine Kopfschmerztablette, bei dem, was Du so schreibst...
Du bist nicht der Erste, der mehrsprachige Projekte entwirft. Das dazu. Und das, was Du schilderst, hat nichts mit der Variablen an sich zu tun. 

Die Variable heißt meinetwegen "Übersetzung" und sucht sich die WERTE entsprechend der angegebenen Sprache und des zu übersetzenden Wortes aus der Tabelle. In der Tabelle steht nicht eine Einzige Variable. Da stehen die Werte, die Du der Variablen zuweist.  

Bsp., die Variable Übersetzung sucht in Spalte A nach "Maschine" und wird mit dem Wert aus Spalte C befüllt.
Heißt: In der Tabelle stehen die WERTE! die Du suchst und keine Variablen!

Code:

Dim Übersetzung As String, wert As String
wert = "Maschine"
Übersetzung = Tabelle1.Columns(1).Find(wert ).Offset(, 2).Value

Nun kannst Du noch eine Enumeration für die Sprachen anlegen, die definiert, dass englisch = in Spalte B steht (also der Spaltenoffset wird definiert), spanisch = 3, italienisch = 4 usw ist und übergibst dann diese Werte.

Code:

Enum SPRACHEN
    deutsch = 0
    englisch = 1
    spanisch = 2
'...
'...
End Enum

Sub Übersetzen()
    MsgBox Translate(SPRACHEN.englisch, "Maschine")
    MsgBox Translate(SPRACHEN.englisch, "Mensch")
    MsgBox Translate(SPRACHEN.deutsch, "Mensch")
End Sub

Function Translate(Sprache As Integer, wort As String) As String
    Translate = Tabelle1.Columns(1).Find(wort).Offset(, Sprache).Value
End Function

Das ist jetzt auch nur gebastelt, weil keine Fehlerbehandlung dabei ist und die Find-Methode nur halbherzig implementiert ist, aber zu Demonstrationszwecken reicht das allemal....

[Sulprobil]

Hallo,

Das richtige Stichwort lautet Enumerierung, denke ich.
Mit dem Befehl Enum kannst Du z. B. die Spalten Deiner Tabelle benamsen und im Code dann die Namen verwenden.
Schau einmal hier:
http://www.sulprobil.com/sbteamgolf_en/
Wenn Du dann eine Spalte einfügst oder entfernst, passt Du einfach die Enum an, und Dein Code passt sich automatisch an.

Für statische Zeilen verwendest Du am besten Namensbereiche, ansonsten sollten die Tabellen nach unten immer variabel viele Einträge ermöglichen.

Viele Grüße,
Bernd

[d'r Bastler]

Moin,

dann mal noch'n Versuch zu beschreiben, was ich meine. Es wird gegen die Ralf A'schen Kopfschmerzen nicht helfen, aber ihn vielleicht erhellen, mal um die Ecke zu denken. Die Beispielmappe kommt ohne Änderung im Code oder Enum aus, um weitere Werte (nicht Variablen - und da hat Ralf, was meine Wortwahl betrifft, tatsächlich Recht!) zu nutzen.
Zum Testen: Einfach eine neue Spalte füllen und die USF aufrufen (F12)


Viel Spaß