Visual Basic is a programming language from Microsoft. A VBA editor can be opened using certain programs such as Excel. Below we show you how to open this editor and give you a brief introduction to the principles of Visual Basic so that you can learn how to use VBA programming.
Open the VBA editor in Excel
It is very easy to open the VBA editor in Excel and then use it to create some macros. Simply press the key combination [Alt] + [F11] .
What can I do with Visual Basic?
You can use Visual Basic to program macros. These macros will help you through your everyday office life. You can automate processes and start a whole range of operators and commands with a single click or adapt Excel documents exactly to your requirements and, for example, provide them with additional input or output windows. You can also use VBA to establish direct connections with other Microsoft Office products, for example to import files from Outlook into Excel. Further tips on the versatile uses of Visual Basic in Excel can be found directly from Microsoft.
Visual Basic programming with the Excel VBA editor
An overview of the most important entries
To create a new program, you must carry out the following two steps. Then you can fill your skeleton with data, such as loops and variables..
1st step:
Open the VBA editor as above and insert a new module via the " Insert " tab and then " Module ". This corresponds to a file or a program that you want to write.
2nd step:
Enter " Option Explicit " and " Option Base 1 ". You can then insert subs and functions into the module. Subs are programs without a return value - i.e. programs in which only commands are entered. A function is a program with a return value. There you can, for example, enter a command for a mathematical calculation and then display it in the dialog window. This is shown as an example in the image below.
Using variables
You can now insert variables which you have to declare as a data type. You can do this using the " Dim [variable] as [data type] " command . The choice of the data type is linked to the content of the variable:
- Integer: Whole numbers from -32768 to 32767
- Long: Whole numbers around plus / minus 2147483647
- Double: floating point numbers of around plus / minus 1.79 * 10 ^ 308
- String: Character string that can contain 0 to 65535 characters
- Boolean: Logical values, i.e. only true or false
- Variant: Can contain any data
For loop The loop is executed here as soon as the variable has reached the start value. The variable is then increased according to the value specified after "Step" until the end value is reached.
For
Variable = [Startwert] To [Endwert] Step [Wert]
Befehl
Next Variable
Do loop This is where commands are executed that are dependent on an expression. With "while" the commands are executed as long as the expression is valid. With "until" the commands are executed until the expression is valid. You can program a loop that always adds 1 to a value until this value reaches 100. This can be done either with the expression "Loop While i <101" or with the expression "Loop Until i = 100". Note that the value of the "While" expression must actually be 101. The loop ends when i = 100 because this is the smallest expression under 101.
Do
Befehle
Loop [While/Until] Ausdruck
If decision
The program automatically makes decisions with an If command. The code above checks that Expression1 is true. If so, Instruction1 is executed. If this is not the case, it is checked whether Expression2 is correct. If Expression2 is correct, Command2 is executed. Command 3 is executed. For example, the loop could say: In this example, the commands are that a message window appears. In the following we will show you how you can use commands for data output.
If [Ausdruck1] Then
Befehl1
Elseif [Ausdruck2] Then
Befehl2
Else
Befehl3
End if
If i>0 Then
MsgBox(Die Variable ist größer als 0.)
Elseif i<0 Then
MsgBox(Die Variable ist kleiner als 0.)
Else
MsgBox(Die Variable ist gleich 0.)
End if
Outputting values
You can include one of the following commands so that your data is output directly, e.g. in the Excel table:
- Debug.Print [value] The value is output in the direct area of the VBA editor.
- MsgBox (message) The output takes place in a separate window, a so-called message box .
- [Value] = InputBox (input question, title of the window) Here the values are entered by the user in a separate window, the title of which you can determine yourself. You also need to formulate a question on which the input depends.
- TableX.Cells (row, column) = [value] The value is output in the specified table within the specified rows and columns.
- [Value] = TableX.Cells (row, column) The values from the specified table from the specified rows and columns are read.
In any case, pay attention to the position of [Value] , i.e. whether this expression is at the beginning or at the end. It depends on whether your data is read in from a table or output in this table.