What is VBA?

The abbreviation VBA stands for Visual Basic for Applications. This is the Visual Basic programming language with special additions for the various applications in Microsoft Office.

With Microsoft Word, Microsoft Excel and the other Microsoft Office applications, many everyday tasks in professional and private areas can already be mastered well. However, there are often:

• Certain recurring tasks that can be solved faster through additional programming with VBA as well
• special problems that can only be solved by VBA programs.

What are macros?

In some cases, even small automation routines, so-called macros, are sufficient. A simple macro consists of a stored sequence of activities that are carried out one after the other. A certain operation is carried out, for example in Microsoft Excel:

• read a number from an external data source,
• written in a table cell,
• this number is offset against other numbers,
• formatted as euro value and
• graphically represented within a diagram.

A macro is created by recording the individual activities and saved in the VBA language. VBA programs can go far beyond simple macros and control complex processes.

Developers and users

In professional practice there are often two groups of users:

• Developers, i.e. experienced Microsoft Office users who deal with the development of complex VBA programs and
• Users, i.e. beginners in Microsoft Office, who only deal with simple topics, for example entering data and using VBA programs.

The developers work on behalf of the users. The creation of VBA programs can take place in a different department of the same company. However, it can also be an externally developed, i.e. purchased, Microsoft Office application.

What gets better with macros and VBA?

A faster problem solving by VBA results:

• because the developer can finish creating his Microsoft Office application faster by programming with VBA and
• because the results can be calculated faster for the user.

Here are some typical scenarios for VBA (here using Microsoft Excel as an example), which should show you what you can use VBA for and the advantages that result from it:

• Large amounts of data are to be read in from a text file. Only the structure of the data is known, not the amount. In addition, depending on the current content of the text file, only certain data should be read. After reading in, the data should be processed, formatted, summarized and displayed graphically for clarity
• A diagram is to be created from a table. The current size and content of the table should determine the type of diagram and its display.
• After entering the Microsoft Excel application, users have a dialog box in front of them. Depending on the current situation, you make certain settings, select the desired data and then start further processing.
• There are regular (for example, every week or every month) data that have to be further processed in a similar (not identical) way.
• The processing of certain data is complex, has to take place in several steps and in the correct order. With VBA you can simplify the coordination and the implementation of the individual steps.
• After calling Microsoft Excel, users should only be able to use certain functions. Your special Microsoft Excel can through VBA at the same time:
  • are restricted, which ideally leads to fewer operating errors and
  • can be expanded, making special functions and processes available to them that go beyond Microsoft Excel.
• The intention is to change existing Microsoft Excel applications that were previously created or purchased by other developers in the company. These are already being used on a regular basis to manage ongoing business processes. Since certain business processes have changed, the associated Microsoft Excel applications must also be expanded and adapted.

Objects, properties and methods

In addition to the language components, VBA offers an extensive object library. It contains many objects with their properties and methods. These objects enable access to the components of the various Microsoft Office applications. With Microsoft Word, for example, documents, paragraphs, words and individual characters can be accessed. With Microsoft Excel, workbooks, worksheets, rows, columns, individual cells and the contents of cells can be accessed.

An example program

An example of VBA with Excel follows in this section. If you have no programming experience yet, this example is intended to illustrate a typical application: The automated creation and modification of a diagram based on a table. If you already have knowledge of another programming language, you will find many familiar elements in the code and the short comments, but also VBA-specific elements.

Of course, this demo example is not yet suitable for a thorough learning of VBA.

It’s about the minimum and maximum temperatures that have prevailed in one place for seven consecutive days. You can see the values ​​in Figure 1.

With the help of the VBA procedure Create Diagram () (see code below), the values ​​are displayed in an embedded line diagram next to the table, see Figure 2.

With the help of the VBA procedure DiagramAendern () (see code below), the entire diagram and many individual parts of the diagram are formatted in a more complex manner, see Figure 3.

This is followed by the code of the VBA procedure ChartErreate () in the Excel folder “Temperatur.xlsm”: This is followed by the code of the VBA procedures ChartAendern () and Aendern (), also in the Excel folder “Temperatur.xlsm”:
Sub DiagrammErstellen()
‘ Verweis auf ein Container-Objekt, hier für ein eingebettetes Diagramm
Dim CO As ChartObject
‘ Verweis auf ein Diagramm-Objekt
Dim CH As Chart

‘ Hinzufügen eines weiteren Container-Objekts zur Liste der Container-Objekte
Set CO = ThisWorkbook.Worksheets(“Tabelle1”). _
ChartObjects.Add(200, 10, 300, 150)

‘ Erstellung eines Diagramm-Objekts innerhalb des Container-Objekts
Set CH = CO.Chart
‘ Typ des Diagramms: Linie
CH.ChartType = xlLine
‘ Quelldaten des Diagramms
CH.SetSourceData Worksheets(“Tabelle1”).Range(“A1:C8”)
End Sub

Sub DiagrammAendern()
Dim CO As ChartObject
Dim CH As Chart

‘ Zugriff auf erstes Element der Liste der Container-Objekte
Set CO = ThisWorkbook.Worksheets(“Tabelle1”).ChartObjects(1)

‘ Größe und Ort des Container-Objekts
CO.Left = 220
CO.Top = 30
CO.Width = 400
CO.Height = 300

‘ Zugriff auf Diagramm-Objekt innerhalb des Container-Objekts
Set CH = CO.Chart

‘ Aufruf der Prozedur zum Ändern eines Diagramms
Aendern CH
End Sub

Sub Aendern(CH As Chart)
‘ Diagrammfläche ändern
CH.ChartArea.Interior.Color = vbCyan

‘ Zeichnungsfläche ändern
CH.PlotArea.Interior.Color = vbYellow

‘ Titel hinzufügen und einstellen
CH.HasTitle = True
CH.ChartTitle.Text = “Temperatur”

‘ Legende ändern
With CH.Legend
.Interior.Color = vbYellow
.Border.Color = vbBlue
.Border.Weight = xlThick
End With

‘ Kategorienachse: Beschriftung hinzufügen, Format einstellen
With CH.Axes(xlCategory)
.HasTitle = True
.AxisTitle.Text = “Datum”
.TickLabels.NumberFormatLocal = “TT.MM.”
End With

‘ Werteachse: Beschriftung hinzufügen, Format einstellen
With CH.Axes(xlValue)
.HasTitle = True
.AxisTitle.Text = “Grad”
.MinimumScale = 5
.MaximumScale = 35
End With

‘ Datenreihe ändern, Markierungen setzen
With CH.SeriesCollection(1)
.Border.Color = vbRed
.MarkerStyle = xlMarkerStyleCircle
.MarkerForegroundColor = vbRed
.MarkerBackgroundColor = vbRed
End With

‘ Datenpunkt ändern
With CH.SeriesCollection(1).Points(3)
.Border.Color = vbBlue
.ApplyDataLabels xlShowValue
.MarkerStyle = xlMarkerStyleSquare
.MarkerForegroundColor = vbBlue
.MarkerBackgroundColor = vbBlue
End With
End Sub