• Home
  • Other Stuff
  • Contact
Black Tor

VBA: A Few Tips

Simple VBA

Excel VBA

Below is some simple VBA code which you can use for a variety of purposes. Starting with these simple building blocks it is possible to build both your confidence, knowledge and ability to work with VBA completing complex appearing sub-routines.
Please revisit the site over the next few days and weeks as more content and helpful code is added.

Get User Input

On many occasions you will need to ask the user to supply some information to you. Below we ask the user for a number so that we can loop through a number of iterations.
Here is the code...

Sub AskForInformation()
' get information from the user so that a process can be repeated.
Dim lAnswer As Long
lAnswer =
' now user the number to loop the required number of times.
Loop i = 1 to lAnswer
MsgBox "The sum result is " & lAnswer
End Sub

This utilises a simple worksheet function within the VBA code. This could be used for a number of different purposes within a larger more complex VBA project.

Return to the  TOP Up Arrow

Find A Directory

When importing or creating files in a process the location can be hard coded into the VBA code. However this can present issues for you and your users when the files move or your process changes. For this reason you may wish to present a the user with a search box to locate the folder themselves.
Here is the code...

Sub FndDir_Click()
Dim diaFolder As FileDialog
' Open the file dialog
Set diaFolder = Application.FileDialog(msoFileDialogFolderPicker)
diaFolder.AllowMultiSelect = False
sFolder = diaFolder.SelectedItems(1)
Set diaFolder = Nothing
End Sub

Having located the folder and directory you can store this address to be utilised again and again in your code.

Return to the  TOP Up Arrow


Working on it!

Currently this site is under re-construction and this page amongst many is constantly being updated with information. Please revisit the site over the next few days and weeks as we add more content.