Don't worry you won't need to fully understand the language to write some really useful scripts. We will show you just how easy it is to create & run VBA code in your excel spreadsheets just using some of the basics. You can take it further from there if you wish. Lets just begin by expaining what a couple of the key features are.
The VBA editor, or VB Editor, or VBE, is an interface for creating scripts using the programming language "Visual Basic For Applications" or VBA (or often just VB) for short.
The VBA editor looks like this:-
In the example above the workbook that is open is called Splash.xlsm and the VBA Editor is displaying the code windowfor a module nammed "Module1" and displaying the code within it.
To start coding in Excel you will need to first open the "Developer Tab". You can do this by either using the keyboard shortcut Alt + F11 or going to the Developer Tab on the main menu and selecting the visual basic button.
If the Developer Tab is not there, then you can add it by going to File > Options > Customize Ribbon and ensuring that "Developer" box has a checkmark in it, then click Ok.
You will see in the image of the VBA editor above that in the left hand pane (the project view pane), there is a folder caled modules. This folder is where all the modules for a particular workbook are held. Think of a module as a container for code in your workbook. When a Macro is written or recorded it will be stored in a module.
Modules are a container for various pieces or snippets of code called sub-routines or functions. These routines can be asked to run or "called" from the click of a button or from other pieces of code elsewhere in the workbook. There will be more in-depth information about modules elsewhere on this site.
A module, in this case "module1" will be created as below and it's as yet empty code window will automatically open for you to add code to.
Put simply a macro is a set of commands or instructions often called Subprocedures or Functions, they are stored in a Workbook in the form of VBA code, either in Modules, behind each Worksheet, or even Userforms. You can think of it as a small program to perform a predefined sequence of actions. Once created, macros can be re-used anytime. Running a macro executes the commands it contains, and code in macros can also be instructed to run or "called" from other pieces of code.
You may have seen that you can record macro's, but when you do this it is stored in the VBA editor anyway. We always tend to write our macros directly into the VBE editor as this gives you more flexibility and control.
These simple steps explain how to add a UserForm to your spreadsheet, but you will also need to add content such as labels & images, and add whatever coding is required to tell your UserForm how to behave and what to do. There are a million different things that you can get a UserForm to do for you, it all depends on how creative you want to be. We will look at some possible applications for a UserForm in various other parts of this site.
Your UserForm will now appear and be avaiable for you to create the design you require.
Where to add the coding required to tell a UserForm how to behave. In this example the UserForm is called UserForm1.
The UserForm code window below will open for you to add code to. You can now write or paste the code into this window that will tell the UserForm what to do & how to behave.
Clicking a button that is placed anywhere in Excel is an event, and all events can have code specified for them.
So by adding a command button to a Userform we can provide the user with a quickly accessible way to execute some code of our choice, pretty much whatever we want.
