Basic - Zellen und Bereiche

Adressierung, Eigenschaften, Methoden

Der Umgang mit Zellen und Bereichen von Kalkulations-Blättern ist die Grundlage für den Austausch von Daten zwischen dem Kalkulations-Programm und Basic. Auf dieser Seite finden sie einige ausgewählte Beispiele zu diesem Thema: Adressierung von Zellen und Bereichen, sowie Zugriff auf deren Inhalt.
VBABasic LibreOffice-Basic und MS-Visual Basic for Applications
Basic-Funktionen
Range Ein ReadOnly-Bereich von Zellen
Objekte in Basic-Subs
Allgemein Umgang mit Basic-(VBA)-Objekten
Workbook Das Datei-Objekt
Worksheet Ein Arbeitsblatt als Objekt
Range Ein Bereich von Zellen auf einem Arbeitsblatt
Cells Eine bestimmte Zelle in einem Bereich
Eigenschaften & Methoden:
Aktivierung EIn bestimmtes Objekt anzeigen
Name Namen statt Adressen für Bereiche
Value Werte von Zellen
Formula Formeln in berechneten Zellen
Clear Bereiche löschen
Sort Sortieren von Bereichen

Bereiche als Argumente von Basic-Funktionen
Basic-Funktionen werden meistens zum Rechnen verwendet. Daher braucht man oft Zahlen (d.h. die Typen Integer, Double, ....) oder Text (String), jedoch nur selten Objekte.
► Wichtige Ausnahmen sind die Objekte Range und Cell: Es ist möglich, als Argument einen Bereich von Zellen anzugeben, z.B. =range_demo(A1:C10) und die Werte des angegebenen Bereichs in der Funktion zu lesen. 		Typische Anwendungen sind z.B. Funktionen wie Anzahl, Minimum, Mittelwert, Maximum, Summe, ...
• Normalerweise ist es für eine Funktion jedoch nicht erlaubt, den Inhalt des angegebenen Bereichs zu ändern.
Die rechts gezeigte Funktion hat keinen praktischen Wert, sondern demonstriert lediglich die Verwendung einer Variablen vom Typ Range

Die Funktion berechnet alternativ die anzahl der Zellen im angegebenen bereich oder die summe der Werte aller Zellen im bereich oder deren Mittelwert (miwe).

Jedes Range-Objekt verfügt über einige nützliche Einschaften und Methoden, z.B. die Eigenschaft Count, welche an die Variable anzahl zugewiesen wird.

Alle Zellen des Bereichs werden typisch in einer For-Each-Schleife durchlaufen. Dabei wird je eine Zelle des Bereichs als Objekt an die Variable c zugewiesen, die ebenfalls vom Typ Range sein muss.

Innerhalb der Schleife kann man alle Eigenschaften der jeweiligen Zelle lesen, z.B. mit der Eigenschaft Value den jeweiligen Wert.

Nach dem Ende der Schleife wird zur Demonstration der Mittelwert miwe berechnet und eines der 3 möglichen Ergebnisse zurückgegeben. 		Basic-Funktion zur (alternativen) Berechnung von Anzahl, Summe oder Mittelwert der Zellen eines Bereichs:
Function range_demo(bereich As Range) As Double
Dim anzahl As Integer
Dim wert, summe, miwe As Double
Dim c As Range
anzahl = bereich.Count
summe = 0
For Each c In bereich
wert = c.Value
summe = summe + wert
Next
miwe = summe / anzahl
range_demo = summe
'range_demo = CDbl(anzahl)
'range_demo = miwe
End Function

In dieser Version wird die Summe aller Werte im bereich berechnet. Wenn man (durch Entfernen des führenden Kommentar-Zeichens) eine der beiden anderen Zeilen einschaltet, kann man alternativ Anzahl oder Mittelwert zurückgeben.

Option VBASupport

Die meisten einfachen Funktionen sind zwischen LibreOffice-Calc und MS-Excel ohne Änderung portabel. Einige wenige Elemente sind nicht portabel, darunter der Variablen-Typ Range

• Die von LibreOffice-Basic ohne diese Option für einen Bereich von Zellen verwendete Syntax ist kompliziert und nicht nach MS-VBA portabel.
● Man kann die einfache MS-VBA Syntax auch in LibreOffice-Basic verwenden, wenn man am Anfang des Basic-Moduls (vor der ersten Basic-Function) diese Zeile einfügt:
Option VBASupport 1

• Diese Option ist in MS-VBA nicht verwendbar: Man schaltet sie allenfalls mit einem vorangestellten Kommentar-Zeichen ab:
' Option VBASupport 1

Objekte in Basic-Subs
Alle ab hier folgenden Kapitel dieser Seite beziehen sich auf Basic-Subs. Subs sind im Gegensatz zu ↑ Functions wenig portabel und stark an das jeweilige Programm gebunden. Die Angaben aller folgenden Kapitel beziehen sich vorzugsweise auf MS-VBA und sind ohne Änderungen nicht auf LibreOffice übertragbar.
Das oberste Objekt der Hierarchie ist in LibreOffice-Basic das Objekt ThisComponent, in VBA das Objekt Application. Alle anderen Objekte sind ihm untergeordnet. Die Hinweise in diesem Kapitel ersetzen kein Manual. Sie sollen lediglich als Anregung dienen, wonach sie (z.B. in der Basic-Hilfe) suchen sollen.

Listen (Arrays):

► Das übergeordnete Objekt jeder Objekt-Art ist die jeweilige Objekt-Liste (→ Array).

► Der Name der Liste besteht normalerweise aus der Objekt-Bezeichnung und "s".

► In der Liste sind alle betreffenden Objekte enthalten und werden als Elemente (wien in jedem anderen Array) adressiert.

► Beispiel: Die Listen Sheets, Worksheets enthalten alle Sheet-bzw. Worksheet-Objekte einer geöffneten Kalkulations-Datei).

♦ Details zu Arrays in Basic und VBA 		Liste aller Sheet-Objekte in LibreOffice-Basic:
Sub sheets_list()
Dim sh, Sheets As Object
Sheets = ThisComponent.Sheets
For Each sh in Sheets
MsgBox "Sheet " & sh.Name
Next
End Sub
Liste aller Worksheet-Objekte in VBA
Sub worksheets_list()
Dim ws As Worksheet
For Each ws In Worksheets
MsgBox "Worksheet " & ws.Name
Next
End Sub

Länge einer Liste:

Die Eigenschaft Count bezeichnet die Länge einer Liste = Anzahl der darin enthaltenen Elemente. 		Anzahl der Sheets in LibreOffice-Basic:
Sub sheets_count()
Dim Sheets As Object
Sheets = ThisComponent.Sheets
MsgBox (Sheets.Count & " Sheets")
End Sub
Anzahl der WorkSheets in VBA:
Sub worksheets_count()
MsgBox (Worksheets.Count & " Worksheets")
End Sub

Adressierung nach Index

LibreOffice verwendet wie die gesamte moderne Informatik Indices (0...Count-1), VBA verwendet Indices (1...Count)
In beiden Beispielen wird das erste Kalkulations-Blatt adressiert.

Die Zuweisung zu einer Objekt-Variablen erfolgt in LibreOffice genauso wie für jede andere Variable, in VBA jedoch mit der Anweisung Set 		Adressierung eines Sheets mit Index in LibreOffice-Basic
Sub sheet_nr()
Dim mysh, Sheets As Object
Sheets = ThisComponent.Sheets
mysh = Sheets(0)
MsgBox "Sheet.Name=" & mysh.Name
End Sub
Adressierung eines Worksheets mit Index in VBA
Sub worksheet_nr()
Dim myws as Worksheet
Set myws = Worksheets(2)
MsgBox "Worksheet.Name=" & myws.Name
End Sub

Adressierung nach Name

Alternativ kann man ein Objekt mit seinem Namen (String) adressieren.

Zu einem (Work)Sheet navigieren

Zur Demonstration wird das mit seinem Namen bezeichnete Blatt angezeigt. In LibreOffice verwendet man dazu die Methode setActiveSheet() des Controllers, in VBA die Methode Select des Worksheet-Objekts.
In beiden Fällen wird das Worksheet "Tabelle2" angezeigt. 		Adressierung eines Sheets mit Name in LibreOffice-Basic
Sub sheets_name()
Dim mysh, Sheets As Object
Sheets = ThisComponent.Sheets
mysh = Sheets.getByName("Tabelle2")
ThisComponent.getcurrentController.setActiveSheet(mysh)
End Sub
Adressierung eines Worksheets mit Name in VBA
Sub worksheets_name()
Dim ws, n
Set ws = Worksheets("Tabelle2")
ws.Select
End Sub

Schleife über alle Objekte

Die Anweisung For Each ... Next durchläuft alle Objekte einer Liste. 		Ein Beispiel dazu finden sie ganz oben in diesem Kapitel.

Das aktive Objekt

In einer Liste kann ein Objekt gerade ausgewählt (im Vordergrund, markiert, usw.) sein. Dafür gibt es meist eine eigene Bezeichnung, die mit "Active" beginnt.

In LibreOffice kann man einige Eigenschaften der aktiven Datei ThisComponent lesen, allerdings erst dann, wenn diese erstmals gespeichert wurde.

VBA führt die aktive Datei als Objekt ActiveWorkbook, das aktive Arbeitsblatt ebenso wie LivbreOffice als ActiveSheet 		Aktive Datei und Sheet in LibreOffice-Basic
Sub sheet_active()
Dim sh As Object
MsgBox "Location=" & ThisComponent.Location
sh = ThisComponent.CurrentController.ActiveSheet
MsgBox ("ActiveSheet = " & sh.Name)
End Sub
Aktives Arbeitsblatt und Worksheet in VBA
Sub worksheet_active()
Dim wb, ws
Set wb = ActiveWorkbook
Set ws = ActiveSheet
MsgBox (wb.Name & "!" & ws.Name)
End Sub
Das integrierte VBA-Manual bietet ausgezeichnete Hilfe und viele Beispiele:
Suchen sie in der
MS-Excel Visual Basic Referenz
nach Objekten, Eigenschaften und Methoden.

Der VBA-Interpreter bietet ein angenehmes Service:
Deklarieren sie ein Objekt. Verwenden sie das Objekt in einer Programmzeile und setzen sie danach einen Punkt: VBA zeigt oft (warum nicht immer ??) eine Liste aller verwendbaren Elemente an, aus der man bequem durch Mausklick wählen kann. 		Automatische Anzeige der Unter-Objekte, Eigenschaften und Methoden durch den VBA-Interpreter:

Das Datei-Objekt ThisComponent bzw. Workbook
Ein Workbook-Objekt entspricht einer Datei (z.B. *.xls).

Liste und Anzahl der verfügbaren (geladenen) Workbooks
Sub workbooks_list()
Dim w as Workbook
Dim m as String
m = Workbooks.Count & " Workbooks:"
For Each w In Workbooks
m = m & chr(13) & "Name = " & w.Name
Next w
MsgBox (m)
End Sub
Ausgewählte Unter-Objekte, Eigenschaften und Methoden:
ActiveWorkbook
wb.Charts, wb.Name, wb.Path, wb.Sheets, wb.Worksheets,
wb.Close, wb.PrintOut, wb.Save, wb.SaveAs, wb.SaveCopyAs
Der VBA-Interpreter bietet Hilfe an:
► Zeigen sie das VBA-Eigenschafts-Fenster an.
► Markieren sie im Projekt-Explorer "Diese Arbeitsmappe"
► Die Eigenschaften des Workbooks werden angezeigt.
► In der Aufklappliste des Eigenschaften-Fensters können sie alle Unter-Objekte des Workbooks (normalerweise die Worksheets) auswählen
Sub workbook_demo()
Dim wb As Workbook
Dim ws As Worksheet
Set wb = Workbooks("range_cell.xls")
For Each ws In wb.Worksheets
MsgBox (ws.Name)
Next
End Sub
Eine Liste der Worksheets (und ihrer Eigenschaften) wird auch im Basic-(VBA)-Eigenschaften-Fenster angezeigt (siehe links)

Events:

► Events sind bestimmte Ereignisse, auf die ein Programm (z.B. LibreOffice oder MS-Excel) reagieren kann. Man kann ein (Basic)-Programm (Event-Handler) an das jeweilige Ereignis binden. Wenn das Ereignis auftritt, dann wird dieses Programm automatisch ausgeführt.
► Leider sind sowohl die Events als auch die Handler je nach Programm unterschiedlich und daher nicht portabel. Hier wird vorläufig nur die Version von MS-VBA vorgestellt.

► Wichtige Events treten auf, wenn ein Workbook geöffnet oder geschlossen wird.
► Die Programme (Sub's) für diese Events befinden sich nicht in einem VBA-Modul, sondern sind direkt dem Workbook-Objekt zugeordnet.
Doppelklicken sie im VBA-Projekt-Explorer "Diese Arbeitsmappe" und wählen sie aus der Aufklappliste oben links "Workbook" statt "Allgemein". Wählen sie aus der Aufklappliste rechts den gewünschten Event-Handler.
► Beim Öffnen richten sie die Arbeits-Umgebung (Menüs...) ein, führen notwendige Initialisierungen durch und Navigieren zu einem definierten Startpunkt, ähnlich einem Splash-Screen.
► Beim Schließen "räumen sie auf", z.B. durch Entfernen aller Installierten Menüs, usw.
Ausgewählte Workbook-Events in MS-VBA Syntax:
Die Bezeichnung "Private" verhindert, dass diese Sub's in der Macro-Liste von Excel angezeigt werden.
Private Sub Workbook_Open()
MsgBox ("Event Workbook_Open")
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
MsgBox ("Event Workbook_BeforeClose")
End Sub
Events in LibreOffice
LibreOffice und OpenOffice verwenden zur Programmierung von Ereignissen (Events) eine Syntax, die sich an der aktuellen Entwicklung moderner Programmiersprachen orientiert.
Das bringt Vorteile für gut ausgebildete EntwicklerInnen, ist jedoch nicht mit MS-VBA kompartibel. In diesem Fall überwiegen vermutlich die Nachteile, da Basic vorwiegend von ambitionierten Amateuren verwendet wird, die mit LibreOffice unerwartete Probleme lösen müssen.
♦ Details der Programmierung von LibreOffice-Events werden an dieser Stelle folgen, sind jedoch noch in Arbeit.
Sie finden im internet Hinweise mit diesen Stichworten:
LibreOffice oder OpenOffice, verknüpft mit Event, Listener, oListener, ...

Worksheet - ein Kalkulations-Arbeitsblatt
Liste, Anzahl und Adressierung der Worksheets entnehmen sie dem Kapitel Objekte weiter oben auf dieser Seite.
Ausgewählte Unter-Objekte, Eigenschaften und Methoden:
ActiveSheet
ws.Cells, ws.ChartObjects, ws.Name
ws.Activate, ws.Calculate, ws.Delete, ws.PrintOut, ws.SaveAs, ws.Select
Sub worksheet_demo()
Dim ws As Worksheet
Set ws = Worksheets("Tabelle2")
ws.Activate
End Sub

Events:

Sind genauso zu behandeln wie im Kapitel Workbook beschrieben.

► Das Ereignis (Event) Worksheet_Activate tritt bei jedem Aktivieren eines Worksheets auf.

► Ein wichtiges Ereignis ist Worksheet_Change: es tritt bei jeder Änderung im Worksheet auf.
► Die Variable Target enthält die Information, wo und was geändert wurde.
► Verzweigen sie nach Target.Address, um auf Änderungen bestimmter Bereiche zu reagieren und ignorieren sie alle anderen Änderungen (Exit Sub).
► Danach besteht die Möglichkeit, Verzweigungen (wie hier mit Select Case) je nach dem eingetragenen Wert auszuführen.
Private Sub Worksheet_Activate()
  MsgBox("event Worksheet_Activate")
End Sub

Private Sub Worksheet_Change(ByVal Target As range)
Dim m as String
'MsgBox ("Changed = " & Target.Address)
'Verzweigung nach Adresse
If Target.Address <> "$A$1" Then
Exit Sub
End If
MsgBox ("Changed = A1")
' Verzweigung nach eingetragenem Wert
Select Case Target
Case 1: m = "eins"
&Case 2: m = "zwei"
Case Else: m = "?"
End Select
MsgBox (m)
End Sub

Range - Ein Bereich von beliebig vielen Zellen
► Wenn sie auf einem Kalkulations-Blatt eine Zelle anklicken, dann haben sie einen Bereich von 1 Zelle ausgewählt.
► Wenn sie die Maus mit gedrückter Maustaste über das Kalkulations-Blatt ziehen, dann wählen sie einen rechteckigen Bereich mehrerer Zellen aus.
► Bereiche werden in Kalkulations-Formeln durch die Syntax
Anfangs-Zelle:End-Zelle
ausgedrückt, z.B: "A1:C5" (relativ adressiert) oder "$A$1:$C$5" (absolut adressiert) oder "Tabelle1!A1C5" (mit Angabe des Arbeitsblatts). 		Die hier vorgestellte Basic-Syntax ('Grammatik') zur Arbeit mit Bereichen ist die einfache, von MS-VBA verwendete Version. Sie ist auch in LibreOffice verwendbar, wenn man am Beginn des Moduls diese Option angibt:
Option VBASupport 1
In MS-VBA ist diese Option nicht verwendbar: Die Zeile wird mit einem vorangestellten Kommentar-Zeiochen abgeschaltet:
' Option VBASupport 1
Das Beispiel rechts zeigt, wie man Bereiche in Basic adressiert und verwendet.

• Wie für alle anderen Objekte erfolgt die Zuweisung an eine Variable mit der Set-Anweisung.

• Die Eigenschaft Value eines Range-Objekts bezeichnet seinen aktuellen Wert. Das Beispiel zeigt, wie man den Wert einer einzelnen Zelle lesen und schreiben kann.
Dim x,y as Range
Dim z
// Adressierung
Set x = Range("A1")
Set y = Range("A1:C5")
// Lesen
z = Range("B1").Value
// Schreiben
Range("B1").Value = 123
Range("B1").Value = "xyz"
Anzahl und Adressierung der Zellen eines Range

Die Zellen eines beliebig großen Bereichs werden durch einen ganzzahligen Index (1 .. Count) fortlaufend adressiert. Wenn sie bestimmte Zellen adressieren wollen, verwenden sie dazu ↓ Cells
Das Beispiel funktioniert in LibreOffice ebenso wie in MS-Excel.

Die Ausführung eines Basic-Sub erfolgt je nach Programm und Version unterschiedlich. Die Zuweisung des Sub an ein Objekt ist eine bedienungs-freundliche Variante:
• Erzeugen sie im Kalkulations-Blatt ein beliebiges Objekt (Zeichnen/Rechteck, Grafik, ...), markieren sie es mit Rechtsklick und binden sie das Sub mit 'Makro zuweisen' an das Objekt. Das Sub wird beim Anklicken des Objekts ausgeführt. 		Demonstration: Größe und Werte des Bereichs A1:A3
Option VBASupport 1 ' Nur in LibreOffice

Sub range_demo()
Dim i As Integer
Dim sr As Range
Dim v
Set sr = Range("A1:A3")
MsgBox("Count = " & sr.Count)
For i = 1 To sr.Count
v = sr(i).Value
MsgBox("Value(" & i & ")=" & v)
Next i
End Sub

Namen:

Verwenden sie nach Möglichkeit Namen für Zell-Bereiche, die mit Basic adressiert werden sollen.
Namen sind unempfindlich gegen Änderungen im Arbeitsblatt. Adressen werden durch Einfügen oder Löschen von Zellen geändert !
Die Definition der Namen erfolgt je nach Programm-Version unterschiedlich, z.B.:
• Markieren sie den zu benennenden Bereich
• Menü Einfügen | Name | Definieren
• Alternative: Im Adress-Fenster (links oberhalb des Zellen-Bereichs) wird die Adresse des markierten Bereichs oder seiner ersten Zelle angezeigt. Tragen sie hier einen Namen ein und schließen sie die Eingabe mit der <return>-Taste ab.
• Sichere Programmierung, funktioniert auch dann, wenn der Bereich verschoben, vergrößert oder verkleinert wurde:
Set sr = Range("bereichsname")

• Weniger empfehlenswert: funktioniert nicht mehr, wenn die Adresse geändert wurde:
Set sr = Range("A1:A3")

Cells - Die einzelnen Zellen eines Bereichs
Die einzelnen Zellen eines Bereichs werden z.B. mit Cells(row,column) adressiert.

■ Für dieses Beispiel markieren sie einen beliebigen (leeren) Bereich in einem Arbeitsblatt und definieren dafür den Namen "zeile_x_spalte".

■ Das Sub (Basic-Programm) cell_demo1 füllt den Bereich mit Zahlen. Jede Zahl entspricht dem Produkt aus Zeile * Spalte.
In Zelle "A1" steht z.B. 1 * 1 = 1,
in Zelle "A2" steht 2 * 1 = 2,
in Zelle "C2" steht 2 * 3 = 6, usw.

■ Markieren sie den Bereich "zeile_x_spalte" und ziehen sie ihn mit der Maus an eine andere Stelle des Arbeitsblatts. Lassen sie nochmals Programm cell_demo1 laufen. 		Demonstration des Objekts Range.Cells() sowie der Eigenschaften Row und Column
Option VBASupport 1 ' Nur in LibreOffice

Sub cell_demo1()
Dim r As range
Dim myrow, mycolumn, i
Set r = Range("zeile_x_spalte")
myrow = r.Cells(1, 1).Row
mycolumn = r.Cells(1, 1).Column
MsgBox ("Start:" & Chr(13) & "Column = " _
& mycolumn & Chr(13) & "Row = " & myrow)
For i = 1 To r.Count
myrow = r(i).Row
mycolumn = r(i).Column
r(i).Value = myrow * mycolumn
Next i
End Sub
■ Programm cell_demo2 füllt den gleichen benannten Bereich ebenfalls mit dem Produkt aus Zeile und Spalte, jedoch relativ, d.h. vom Beginn des Bereichs aus gemessen.

■ Beachten sie: Dieses Programm füllt den benannten Bereich an jeder Stelle des Kalkulations-Blatts mit den gleichen Zahlen !

■ Beide Demo-Programme arbeiten mit Bereichen jeder beliebigen Größe. Sie können den Namen neu definieren, um das zu testen.

Die Zellen eines Bereichs werden ohne besondere Anweisung zeilenweise durchlaufen, d.h. zuerst die 1. Zeile von links nach rechts, danach die 2.&sbsp;Zeile usw.
Sub cell_demo2()
Dim r As range
Dim myrow, mycolumn, i, therows, thecolumns, j
Set r = Range("zeile_x_spalte")
myrow = r.Cells(1, 1).Row
mycolumn = r.Cells(1, 1).Column
MsgBox ("Start:" & Chr(13) & "Column = "_
  & mycolumn & Chr(13) & "Row = " & myrow)
therows = r.Rows.Count
thecolumns = r.Columns.Count
MsgBox (thecolumns & " x " & therows)
For i = 1 To therows
For j = 1 To thecolumns
r.Cells(i, j).Value = i * j
Next j
Next i
End Sub
Ausgewählte Unter-Objekte, Eigenschaften und Methoden:
ActiveCell
r.Address, r.Column, r.Formula, r.FormulaR1C1, r.HasFormula, r.Row, r.Value, r.Value2
r.Activate, r.Clear, r.ClearContents, r.Select

Aktivierung - Navigation mit VBA
Die Methode Activate kann zur Navigation verwendet werden. Je komplexer eine Lösung, desto wichtiger ist eine übersichtliche Navigation. Die Navigation kann durch Anklicken von Buttons erfolgen, oder vollautomatisch.

Splash-Screen:

Beim Öffnen eines komplexen Dokuments wird (auch in Kalkulations-Programmen !) eine übersichtliche Startseite angezeigt. Beachten sie: der Event-Handler muss sich im Workbook-Objekt befinden !
Private Sub Workbook_Open()
Worksheets("Start").Activate
End Sub

Sprung zu einem Arbeitsblatt:

Dieses Programm befindet sich in einem VBA-Modul. Wenn sie nicht wollen, dass es in der Makro-Liste angezeigt wird, dann verwenden sie Private Sub.
► Jedes grafische Objekt (Icon-Bild, Button, Shape, Chart, Textfeld...) lässt sich mit diesem Programm verknüpfen.
Sub goto_tabelle1()
Worksheets("Tabelle1").Activate
End Sub

Sprung zu einer Zelle:

So können sie eine bestimmte Zelle oder einen Bereich auswählen. Wenn sie die Angaben von Workbook und Worksheet weglassen, werden diese automatisch durch ActiveWorkbook und ActiveSheet ersetzt. Mit der Aktivierung einer Zelle setzen sie gleichzeitig die Eigenschaft ActiveCell
Sub goto_range()
'Range("A1").Activate
Worksheets("Tabelle1").Range("A1").Activate
End Sub

Namen für Bereiche
An Stelle der Adressierung mit A1-Syntax verwenden sie besser Namen. Hier sehen sie, wie man Namen für Bereiche mit VBA vergeben kann.
Sub name_define()
ActiveWorkbook.Names.Add Name:="test", _
RefersTo:="=Tabelle1!$a$1:$a$12"
MsgBox ("Name 'test' wurde definiert")
End Sub
Dieses Programm zeigt eine Liste aller vergebenen Namen und deren zugeordnete Bereiche an.
Sub names_list()
Dim mynames, thename, therange, i, m
Set mynames = ActiveWorkbook.Names
MsgBox ("Names.Count = " & mynames.Count)
For i = 1 To mynames.Count
m = "Names(" & i & ")"
thename = mynames(i).Name
m = m & Chr(13) & "Name = " & thename
therange = mynames(i).RefersToRange.Address
m = m & Chr(13) & "Range = " & therange
MsgBox (m)
Next
End Sub
So löschen sie Namen in Basic.
Eine Alternative zur expliziten Adressierung ist das Durchlaufen der Liste aller betreffenden Objekte. So können sie gesuchte Objekte an Hand ihrer Eigenschaften identifizieren.

Falls sie diesen Vorgang manuell durchführen wollen (z.B. weil nur einmalig notwendig) dann verwenden sie dazu das Eigenschaften-Fenster von VBA.
Sub name_delete()
Dim mynames, i, thename
Set mynames = ActiveWorkbook.Names
For i = 1 To mynames.Count
thename = mynames(i).Name
If (thename = "test") Then
ActiveWorkbook.Names(i).Delete
MsgBox ("Name 'test' wurde gelöscht")
End If
Next
End Sub

Value - Der Wert einer Zelle
Eigenschaft Value liefert den Wert einer Zelle, genauso wie er angezeigt wird. Dabei ist unerheblich, ob dieser Wert manuell (oder durch Basic) eingegeben wurde oder durch eine ↓ Formel berechnet.
Sub value_demo()
Dim r As Range
Set r = Range("A1")
MsgBox ("Value = " & r.Value)
End Sub

Formula - Formeln in Zellen
Bevor sie mit VBA nach der Formel einer Zelle fragen, sollten sie mit HasFormula nachsehen, ob die Zelle überhaupt eine Formel enthält.

Beachten sie, dass Formeln in Excel als binäres Token gespeichert sind, nicht im eingegebenen und / oder angezeigten Klartext.
• Die Eigenschaft Formula gibt den Namen der Formel in Englisch an: Aus SUMME() wird daher SUM(), aus WENN() wird IF(), usw.
• Die Eigenschaft FormulaLocal gibt den Namen der Formel in der aktuellen Sprache an, z.B. in Deutsch.
Sub formula_demo()
Dim r As Range
Dim m As String
Set r = Range("A1")
If (r.HasFormula) Then
m = "Formula = " & r.Formula & vbCrLf
m = m & "FormulaLocal = " & r.FormulaLocal
Else
m = "Value = " & r.Value
End If
MsgBox (m)
End Sub

Clear - Bereiche löschen
Die methode Clear kennt in VBA ebenso viele Varianten wie der Excel-Menübefehl "Löschen".
In diesem Beispiel wird der Inhalt (die Werte) eines Bereichs gelöscht.
Sub range_clear()
Dim tr As range
Set tr = range("targetrange")
tr.ClearContents
End Sub

Sort - Bereiche sortieren
Für den hier gezeigten Bereich (Daten inkl Überschrift) wurde der Name "sortrange" definiert.

Mit Methode Sort wird der Bereich nach "Nr" sortiert. Entnehmen sie Details der Parameter aus der VBA-Hilfe für diese Methode.
Sub sort_nr()
Dim myrange As range
Set myrange = Worksheets("Tabelle1").range("sortrange")
myrange.Sort _
Key1:=myrange.Cells(1, 1), _
Order1:=xlAscending, _
Header:=xlYes
End Sub
Dieses Programm sortiert den gleichen Bereich nach "Name". Die Art der Sortierung richtet sich nach der Überschrift ( Cell(1,1) = "Nr" oder Cell(1,2) = "Name" ).

Eine elegante Lösung für die Auslöse-Buttons: Legen sie (Menü Ansicht | Symbolleiste | Zeichnen) je ein Rechteck genau über die Zellen der Spalten-Überschriften, verknüpfen sie es mit dem zutreffenden Sort-Programm und formatieren sie es "unsichtbar", d.h. ohne Fläche und Ränder.
• Durch Anklicken der Überschriften wird dann sortiert ...
Sub sort_name()
Dim myrange As range
Set myrange = Worksheets("Tabelle1").range("sortrange")
myrange.Sort _
Key1:=myrange.Cells(1, 2), _
Order1:=xlAscending, _
Header:=xlYes
End Sub

Zum Inhaltsverzeichnis Homepage von PS-Trainer   >   Entwicklung   >   Basic   >   Bereiche und Zellen