I came across this recently while considering the best way to create a function to deal with a SQL statement that could accept a varying number of parameters.
SELECT * FROM YourTable WHERE TableId = 2 AND Param1 = '%' AND Param2 = '%';
It means you don't need to use an overload function. Just swap out blank parameters for wildcard characters, meaning you don't need to do anything with the query as well. You could quite easily do this with any database engine, from Access to SQL Server.
It is sometimes necessary to update your code, a quick find and replace (Ctrl+H) will often be enough. I’d also recommend the MZ Tools VBE toolbar, it has a lot to add to your VBA coding experience, including a very good search tool.
Sometimes the above is not quite want you want. So below is a simple bit of code you can use to replace one line of code for another.
Public Sub FindAndReplace()SL / SC is the start line / start column and EL / EC is the end line / end column. These can be changed to target code lines within a specific part of your code.
I hope the above proves useful. As always, any questions let us know via the comments.
It is sometimes useful to keep data in a text file. For example one of our clients has a resource file which has onOpen and onClose routines that log the current user, the date and time and whether the file was opened read/write or read only.
The code below shows how this can be done.
Sub Create_A_File()Hope that proves useful.
Some example functions for finding out the last modified date of a file, copying a file and deleting a file using VBA.
Public Sub File_Last_Modified_Date()
' Use a function to return the last modified date of a file
MsgBox f_File_Last_Modified_Date("C:\Put\the\full\file\address\here.xlsx")
End Sub
Private Function f_File_Last_Modified_Date(FileName As String) As String
' In the VBE, set a reference to Microsoft Scripting runtime
' Tools -> References... ->
Dim fso As Scripting.FileSystemObject
Dim fsof As Scripting.File
Dim strPath As String
Set fso = New FileSystemObject
strPath = FileName
Set fsof = fso.GetFile(strPath)
With fsof
f_File_Last_Modified_Date = .DateLastModified
End With
Set fso = Nothing
Set fsof = Nothing
End Function
Public Sub CopyFile()
' Copy a file, the file cannot be open when the copy is attempted
Dim SourceFile As String
Dim DestFile As String
SourceFile = "C:\Put\the\full\file\address\here.doc"
DestFile = "C:\Put\the\new\file\address\here.doc"
FileCopy SourceFile, DestFile
End Sub
Public Sub DeleteFile()
' Delete a file
Dim FileName As String
FileName = "C:\Put\the\full\file\address\here.txt"
' Check the file exists
If Dir(FileName) = "" Then
' File does not exist
Else
' Delete the file
Kill FileName
End If
End Sub
As always, any questions or comments please let us know via the comments section.
Recently I have needed to truncate a decimal value to a set number of decimal places.
For example; If you wanted the number 55.446 to two decimal places rounding the number would return 55.45. Truncating the number to two decimal places returns 55.44.
To do this I created the function below. Pass in a decimal value and the number of decimal places you want the number truncated too and the function returns the value as a ‘Double’.
Public Function TruncTo(dblValue As Double, lngPlaces As Long) As DoubleFollowing on from the code here (Using VBA Speech) the code below is used to make adjust the speaker volume, including turning mute on / off.
Private Declare Sub keybd_event Lib "user32" ( _Again, this requires the PC to a sound card and speakers.
With April Fools day approaching (well, four weeks) I though I’d post a couple of amusing VBA tricks and jokes you could use. The first is using the speech and the CD trey. The code below can be copied straight into a VBA code module.
Declare Sub mciSendStringA Lib "winmm.dll" (ByVal lpstrCommand As String, _This does require the PC to have speakers.
There are several methods to get find out the file paths for Microsoft Windows’ special folders (Systems folder, Temporary folder etc). The example below uses the FileSystemObject.
Sub Special_Folders()This routine requires a reference Microsoft Scripting Runtime.
The code I’m sharing with you today will enable you to send an email using VBA through Lotus Notes. It uses late binding so you do not need to use references to Lotus Notes, which in turn means you do not need to worry about your users Lotus Notes version. I say this, though in practise this may not always be the case. Lotus Notes is often rather frustrating, as many users will testify :)
As well as the subject and message body the routine allows you to specify the To, Cc and Bcc fields within a standard email format.
The example below shows the code to attach a single file. This could easily be amended to loop through an array or collection of file addresses.
Sub SendAnEmailViaLotusNotes()As always, if you have any questions do let us know via the comments section.
Access, Email, Excel, Lotus Notes, VBAIt is sometimes useful to know the date and time a file was last modified. this is particularly useful if you have a folder full of similar files and need to use the latest copy.
Today the code I’m going to share below enables you to pass a file address into a function which will return the date and time it was last saved. The example below passes a file address to the function, which returns the modified date, this is then displayed in a message box.
The function requires a reference to Microsoft Scripting Runtime within the VBE.
Sub MyMacroRoutine()
Dim ModDate As Date
ModDate = FileLastModDate("C:\YourFileAddressGoesHere")
MsgBox ModDate
End Sub
Function FileLastModDate(strFileAddress As String) As Date
' In the VBE, set a reference to Microsoft Scripting runtime
Dim fso As Scripting.FileSystemObject
Dim fsof As Scripting.File
Dim strPath As String
Set fso = New FileSystemObject
strPath = strFileAddress
Set fsof = fso.GetFile(strPath)
With fsof
FileLastModDate = .DateLastModified
End With
Set fso = Nothing
Set fsof = Nothing
End Function
As always, if you have any questions do let us know via the comments section.
The code I’m sharing with you today will enable you to send an email using VBA through Outlook. It uses early binding so you will need to use a reference to Outlook. You may need to look into what version(s) Outlook will be available to your users.
As well as the subject and message body the routine allows you to specify the To, Cc and Bcc fields within a standard email format.
The example below shows the code to attach four files using four different formats: Insert the file, insert a shortcut to the file, Embed and OLE.
The code could easily be changed to loop through arrays or collections of values for many of these fields.
Sub SendAnEmailWithOutlook()Hope the text wrapping doesn’t make it too awkward to read.
As always, if you have any questions do let us know via the comments section.
Edit (18-Dec-2010): Due to a ‘technical’ error this blog entry has changed from it’s original version. The original explained how to send an email through Outlook using late binding, this post will be re-written and posted again soon. Apologies for any inconvenience this may cause.
I recently received a request from a client to add a specific sound when displaying a message box warning. I knew I’d done this before, and from memory knew it was fairly simple. So, after ploughing through some old code I came across the code I have put below. I hope it proves useful to you.
The code uses a call to an API, below. The API code needs to be copied and pasted to the top of your module code.
' This is required to play a .wav sound with VBA
Public Declare Function sndPlaySound32 _
Lib "winmm.dll" _
Alias "sndPlaySoundA" ( _
ByVal lpszSoundName As String, _
ByVal uFlags As Long) As Long
Below is the routine which plays the sound, this can be placed in a utilities module and called by various routines, each passing in the name of the sound file you want to be played.
Windows has a folder full of .wav files, they can be found here; C:\Windows\Media. The routine below allows you to pass in the full file path of a music file, or just the filename. If you pass in a filename the routine assumes you want to play a Windows default sound and guesses the file path.
Public Sub PlaySound(ByVal SoundFileName As String)
If Dir(SoundFileName, vbNormal) = "" Then
' SoundFileName is not a file. Get the file named by
' SoundFileName from the Windows\Media directory.
SoundFileName = Environ("SystemRoot") & "\Media\" & SoundFileName
If InStr(1, SoundFileName , ".") = 0 Then
' If SoundFileName does not have a .wav extension, add one.
SoundFileName = SoundFileName & ".wav"
End If
If Dir(SoundFileName, vbNormal) = vbNullString Then
' Can't find the file. Just use a beep.
Beep
Exit Sub
End If
Else
' SoundFileName is a file, Use it.
End If
' Play the sound, before continuing code
sndPlaySound32 SoundFileName, 0&
' Play sound and continue code.
sndPlaySound32 SoundFileName, 1&)
End Sub
I have included two lines at the bottom of the code, you can comment out which-ever you choose not to use. The first plays the sound before allowing the code to continue, the second plays the code and allows the code to continue. This means you can play a specific sound as you display a message box for example. Have a play, see which way suits you best.
As always, if you have any questions do let us know via the comments section.
Loop statements are used to get Excel to repeat a piece of code a certain number of times. The number of times the code gets repeated can be specified as a fixed number (e.g. do this 10 times), or as a variable (e.g. do this until there are no more rows of data).
The are several ways in which loops can be constructed, depending on the circumstances in which they are going to be used. Quite often the wanted result can be obtained in different ways, depending on your preferences.
There are two basic kinds of loops, examples of both: Do…Loop and For…Next loops. The code to be repeated is placed between the key words.
For our first example open a new workbook and place the numbers 1 to 10 in cells A1 through to A10.
We are going to loop down the list, adding 5 to the number in column ‘A’.
Now open the VBE, add a new module and start a new sub routine call MyLoopStatement. Copy the code below into your new routine.
Public Sub MyLoopStatement()
Range("A1").Select
Do While IsEmpty(ActiveCell) = False
ActiveCell.Offset(0, 1).FormulaR1C1 = "=SUM(RC[-1]+5)"
ActiveCell.Offset(1, 0).Select 
Loop
End Sub
This routine loops down the list placing a formula in each of the cells to the right of the activecell. It will continue WHILE the active cell is not empty.
The next example uses Do until.
Range("A1").SelectThis example loops until it finds a blank cell. It is generally considered better practise to use Do Until, rather then Do While. Both of the above examples runs the code within the loop at least once before testing whether to move on. You could write the same Do Until / Do While loops in the following way.
Range("A1").SelectMoving the test to the start of the loop means it is carried out before carrying out any of the instructions within the loop. This could be useful if, for example, sometimes cells A1:A10 were blank.
if you know or can use VBA to find out how many times you want to repeat the code you can use a For… Next loop.
In the following example we know that we have 10 rows of data, so we can tell the code to loop 10 times.
Dim l As Long
Dim lRowCount As Long
lRowCount = Range("A1").CurrentRegion.Rows.Count - 1
' You could also try
' lRowCount = Cells.SpecialCells(xlCellTypeLastCell).Row
For l = 1 To lRowCount
ActiveCell.Offset(0, 1).FormulaR1C1 = "=SUM(RC[-1]+5)"
ActiveCell.Offset(1, 0).Select
Next l
For… Next loops are particularly good a looping through objects.
Dim wb As WorkbookThe above code would be quicker then:
Dim l As LongAs always, I hope the above examples have been helpful. If you have any questions or comments please do let us know.
By using Collections within VBA you can store multiple objects, similar to an array. The key advantage with Collections is that the objects can be added, removed and used by referring to the objects ‘Key’, rather then having to loop through an array.
In this blog post I am going to use the example code provided in my previous blog post Working with Class Modules.
Within the class module cls_Employee we have the following properties, Name, Department, Age and Salary. In this blog post I will run though how to create multiple employee class objects and add them to a collection. We will then loop through the collection and retrieve and remove an employee.
To store multiple instances of a class, in this example a group of employees, create several instances of the class and load them to a collection as follows.
Dim clsEmp As cls_EmployeeNow, you can use a simple For Each loop to loop through the collection and iterate through the collection and access each instance of cls_Employee sequentially:
Dim clsEmp As cls_Employee
' Loop through a collection to retrieve each item
For Each clsEmp In colEmp
Debug.Print clsEmp.Name
Debug.Print clsEmp.Dept
Debug.Print clsEmp.Age
Debug.Print clsEmp.Salary
Next clsEmp
To retrieve or remove a specific employee you can use the object’s ‘key’ to refer to it.
' Use the key to retrieve a specific employeeSomething worth noting is that the key needs to be a text value. Other values can be used but may require the Str() function wrapped around them.
I’d been working with Excel and VBA for more then 6 years before I caught on to just how useful class modules can be. Class modules can be a powerful tool used within intermediate to advanced level VBA programming. Below is something I hope you will find a useful introduction to how to use them.
This example does assume some basic knowledge of the VBE (Visual Basic Editor).
We’ll start where almost all examples for class modules start; with an employee, the employee is called Fred Smith (original, I know), works in accounts, is 25 and earns £22k.
To start, add a new class module to your VBA project. For this example change the name from Class1 to cls_Employee. Do this by editing the (Name) item in the ‘Properties’ list for the class module.
You then need to create the variables which are going to be used within the class module, since they are declared Private, they are not available outside of the class module.
Option ExplicitNow we need to add the property procedures which allow us to write to and read from the above variables. To do this we need Property Get and Property Let functions. (Property Set is used for objects).
' Name propertyThe Let procedure is used to assign values to the class. The get procedure is used to get the values from the class. Please note that the data types must be the same for the ‘value’ be passed through and the Get procedure.
A property can be made read-only simply by omitting the Let procedure. For example, a read-only property might be tax, which is calculated when it is called. E.g.,
'Tax propertyClass modules can also contain sub routines and functions, such as the PrintEmployeeData below.
' Print employee data routineNow that we have the class module ready we can create objects based on the class module. Add a standard code module to your project. Within a new sub routine declare a new variable as type cls_Employee like the example below.
Option ExplicitClsEmp has to be set as a new instance of the the class before we can assign values to it’s properties, like below.
Set clsEmp = New cls_EmployeeYou can retrieve the data value from the class using code similar to the below example.
MsgBox "Employee Name: " & clsEmp.Name;I hope this has helped with your understanding of class modules within VBA. The above example code will work in Excel and Access.
I would like to add that this blog post and code examples, although my own, is based a on the article from which I learned about class modules. That article can be found at Chip Pearson’s website: http://www.cpearson.com/.
In my next blog post I will cover how to store multiple objects (employees) in a collection.
If you have ever used the paste special transpose option within an Excel worksheet you will know how handy it is. The transpose option allows you copy a list, for example 10 rows with 2 columns, and turn it sideways, leaving you with 2 rows and 10 columns.
Well below is a simple function you can copy straight into any VBA module. It allows you to pass in an array of any size and passes back the ‘transposed’ array results.
Public Function TransposeArray(v As Variant) As VariantI hope it proves useful. If you have any questions or ideas for future blog posts please let us know via the comments section.
I’d like to add a thank you to a possible future Zypher-Blog colleague Niall for the example code.
In this post I thought I'd show two very useful functions which use Windows API calls, the username and PC name functions. Windows API calls allow you to get information or features from the operating system which Excel and Access do not have.
The first step in using an API call is to tell VBA the function exists, where to find it, what arguments it takes and what data type it returns. This is done using the Declare statement, such as the example below.
Private Declare Function GetUserNameA _
Lib "advapi32.dll" _
(ByVal lpBuffer As String, nSize As Long) As Long
This statement tells the VBA interpreter that there is a function called GetUserNameA located in the file advapi32.dll that takes two arguments, the first a string and the second a Long value and returns a Long value. Once defined, we can call GetUserNameA in exactly the same way as if it is the VBA function:
lng = GetUserNameA(strUserName, intLength)
The Function below uses the GetUserNameA API function and turns the results into a string which shows the current users Windows login.
Public Function CurrentUser() As String
'-- used to get current user's login id --
Dim strUserName As String
Dim lngLength As Long
Dim lngResult As Long
' Set up the buffer
strUserName = VBA.String(255, 0)
lngLength = 255
' Make the call
lngResult = GetUserNameA(strUserName, lngLength)
' Assign the value
CurrentUser = Left(strUserName, _
InStr(1, strUserName, VBA.Chr(0)) - 1)
End Function
You can add this this code to any normal VBA module within either Excel or Access. The API ‘Private Declare Function’ should be placed at the top of the module, before any other Sub Routines or Functions.
The current PC function, below, works in much the same way.
Private Declare Function GetComputerNameA Lib "kernel32" _
(ByVal lpBuffer As String, nSize As Long) As Long
Public Function CurrentPC() As String
'-- used to get current user's login id --
Dim strPCName As String
Dim lngLength As Long
Dim lngResult As Long
' Set up the buffer
strPCName = VBA.String(255, 0)
lngLength = 255
' Make the call
lngResult = GetComputerNameA(strPCName, lngLength)
' Assign the value
CurrentPC = Left(strPCName, _
InStr(1, strPCName, VBA.Chr(0)) - 1)
End Function
Google / Yahoo as always will be able to provide more information on using API calls, this site is also probably worth a read.
I’ll add a sorry about the colour scheme, guess that’s what happens when you choose black as a background.
