top of page

Create your first VBA script and learn how to declare variables

create your first VBA script and learn how to declare your variables.

Create your first VBA script.

If you don't have VBE module access (what you write your VBA in), please click on "File, Options, Customize Ribbon, and tick in the Developer tab box on the right side.". Then press "OK" and now you have access to the VBA toolkit. Click on "Visual basic" button on the left side, alternatively you can open your VBE with the following keyboard keys: "Alt" + "F11".

Here you can right click on the "module" folder and do "Insert", then "Module".

In this module you can paste in the following code:

Sub Standard_msg()
    MsgBox "Hello Analyst!"
End Sub
vba standard messagebox. msgbox pop up

Now press F5 key, or the green "Run" button the top that looks like a play button. Your screen should now have a message box pop-up saying Hello Analyst!

Congratrulations, you have just written your first script!!!

Learn how to declare your different variables

In order to learn how to write more scripts it is useful to know how to store the information that you want to print out to your excel sheet or screen. We will therefore go through something called "Declaration". Declaration is when you initialize your variable and say it is of a type text or number for example.

Our Variables:

String = Text
Integer = Whole number
Double = Decimal number
Boolean = True or False
Date = Date value
Variant = All non-declared variables. Uses the most memory. 

We initialize a variable with the phrase Dim variablename As variabletype.

Once initialized, we can go ahead and add data to the variable.

Please find some examples below:

Dim MyString As String
MyString = "PlsFixThx" 'All text needs quotationmarks.
Dim MyWholeValue As Integer
MyWholeValue = 7
Dim MyDecimalValue As Integer
MyDecimalValue = 7.77
Dim Bol As Boolean
Bol = True
Dim MyDate As Date
'Dateserial is a function that provides the date of the passed in variables.
MyDate = DateSerial(2030, 7, 7)

Global declaration

A global declaration is a term for initializing a variable globally, which means that it is accessible to all procedures and in all modules in a workbook. It is rarely ideal, as it is a better praxis to pass variables through the Subs or Functions in my opinion. Declaraing a gloabl variable is done by placing the following text above a Sub or Function, at the top of your screen. Public MyString As String

42 views0 comments


bottom of page