Quick overview of VBA

This article recap the basics things you can do with VBA.

• The code is read from top to bottom, with loops or not
• You have to totally build the user interface through a userform (insert/userform) and add buttons, labels...
• VBA could be considered as an oriented object programming language as well and you have methods, properties, functions, sub...

1) Userform

Tip : F5 to see how your userform looks like

To add a userform : insert/userform in your VBA Project

You can enlarge the userform, add other controls...If the little window with controls doesn't appear, just click on toolbox in your toolbar.

Useful Methods and properties :

• your_userform.Caption : the name that appears on the top of the userform (it's not the intrinsic name of the userform, which is your_userform.Name)
• your_userform.StarUpPosition (choose the initial position of your userform)
• your_userform.Show : show the userform...
• Userform_Initialize() : this sub is run when your userfom appears

2) Label

This is a text, which can't be modified by the user.




Useful Methods and properties :

• label1.Caption : modify the text
• label1.Font : change the backround color
• label1.Visible = True (or False) Tell if the label is shown to the user or not (useful for no-intrusive error message)

3) Textbox

It's possible to enter a text or a number in a textbox.

Useful Methods and properties :

• textbox1.Text or textbox1.Value : return the expression, which is type in the textbox (it's a string expression. Nothing typed equals to "" )
• textbox1.Font

4) Command button

It allows to run an action, when selecting the button




Useful Methods and properties :

• CommandButton1.Caption : to control the button text

5) Image

Add a picture in your userform (bmp, jpg or gif)

Useful Methods and properties :

• Image1.Picture : select the image you want to show with the syntax : Image1.Picture = LoadPicture("your_path\your_image.jpg")
• Image1.Autosize : automaticaly resize your image

Image1.PictureSizeMode=fmPictureSizeModeClip

(let your image unchanged)

Image1.PictureSizeMode=fmPictureSizeModeStretch

(enlarge and deform your image to fit to your frame)
Image1.PictureSizeMode=fmPictureSizeModeZoom

(enlarge your image with no deformation)


6) Listbox

It's possible to display a list, from which you can select an object

Useful Methods and properties :

• listbox1.AddItem "text" : add an item in the list
• listbox1.Clear : erase all values
• listebox1.RemoveItem(listbox1.Listindex) : remove the n-1 item (where n=listbox1.Listindex)
• listbox1.ListCount : number of values available on the list
• listbox1.Text or Value : to get the selected element
• listbox1.List(n) : to get item n in the list

7) Checkbox and option box

- Checkbox : activate an option
- Optionbox : allow to pick up only one option among others (you have to gather them in a same frame)




Useful Methods and properties :

• checkbox1.Caption
• checkbox1.Value / optionbox1.Value : if ticked = True else = False

For further information, follow this links :
In french
In english

Memento : Main type of data

Numeric :

Non numeric :

Interacting with the user :

Other than the userform there are pre-formatted interface.

• MsgBox

Simple MsgBox :

MsgBox "Your message" 

Error MsgBox :

MsgBox "Are you sure?",vbOKCancel + vbCritical, "Question"





You can get the result in a variable ( result=MsgBox "Are you sure?",vbOKCancel + vbCritical, "Question")

Information MsgBox :

MsgBox "Just click on OK", vbInformation, "Question"






Exclamation MsgBox :

MsgBox "Be careful", vbExclamation, "Error"

• InputBox

syntax : Inputbox (message,title,default. value)

nb_part = InputBox("How many parts do you want ? :", "Part creation", 2)




Back to the summary
Back to the tutorial