Outlook Email using VBA (Updated)

This is an update to the code supplied here.  The previous article enabled you to send an email though Outlook using early binding.  The code below uses late binding, this means you do not need to worry about Outlook versions.
As before it includes the subject and message body fields as well as the To, Cc and Bcc fields within a standard email format. 
The code could easily be changed to loop through arrays or collections of values for many of these fields.
Private Sub SendOutlookEmail()
'-- Creates and sends a new e-mail message with Outlook --

    ' These are members of the referenced object model which _
      are unavailable due to late binding.  They have been _
      replaced with the numbers they represent
    Const olMailItem    As Integer = 0
    Const olTo          As Integer = 1
    Const olCc          As Integer = 2

    Dim oOutlook        As Object 'Outlook Application
    Dim oMailMsg        As Object 'Outlook MailItem
    Dim oRecipient      As Object 'Outlook Recipient
    Dim oRecipType      As Object 'Outlook Recipient Type

    On Error GoTo ErrTrap

    ' Create the Outlook session
    Set oOutlook = CreateObject("Outlook.Application")

    ' Create the message
    Set oMailMsg = oOutlook.CreateItem(olMailItem)

    With oMailMsg
        ' Request a receipt ?
        .ReadReceiptRequested = False
        ' Keep copy in 'Sent Items' ?
        .DeleteAfterSubmit = False
        ' Your email address or 'team mailbox' address
        .SentOnBehalfOfName = "YourEmail@Address.com"
        ' Message subject
        .Subject = "Subject Here"
        ' Add message body
        .Body = "Email Message Here"

         'Add 'To' recipient(s)
        Set oRecipient = .Recipients.Add _
            ("SomeoneElses@EmailAddress.com; AnotherPerson@Email.com")
        oRecipient.Type = olTo
         'Add another 'To' recipient
        Set oRecipient = .Recipients.Add _
            ("SomeoneElses@EmailAddress.com")
        oRecipient.Type = olTo
        ' Add 'Cc' recipient(s)
        Set oRecipient = .Recipients.Add _
            ("SomeoneElses@EmailAddress.com")
        oRecipient.Type = olCc

        ' Loop through an array to attach files
        For l = 0 To UBound(varFile)
            If Not IsEmpty(varFile(l)) Then
                .Attachments.Add varFile(l)
            End If
        Next l

        ' Display to user or Send email
        '.Send
        .Display

    End With

ErrTrap:
    Set oOutlook = Nothing
    Set oMailMsg = Nothing
    Set oRecipient = Nothing
    Set oRecipType = Nothing

    Select Case Err.Number
        Case Is = 0
            ' All okay, continue
        Case Is = -284147707
            MsgBox "You have exceeded the storage limit on your mailbox. " _
                & "Delete some mail from your mailbox or contact your " _
                & "system administrator to adjust your storage limit." _
                 , vbInformation, "Error Message"
        Case Else
            MsgBox "An error has occured:" & vbCrLf & vbCrLf _
                & Err.Number & " - " & Err.Description, "OutlookEmailFunction"
    End Select
End Sub

As always, if you have any questions do let us know via the comments section.

Lotus Notes Email using VBA

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()

    Dim WS As Object
    Dim Session As Object
    Dim DB As Object
    Dim uiDB As Object
    Dim NotesAttach As Object
    Dim NotesDoc As Object
    Dim RichTextBody As Object
    Dim RichTextAttachment As Object
    Dim StyleBold As Object
    Dim StyleNorm As Object
    Dim StyleUnderline As Object
    Dim StyleFont10 As Object
    Dim Server As String
    Dim MailFile As String
    Dim TheUser As String
    Dim UserSig As String

    Dim strEmailTo As String        ' email To field
    Dim strEmailCc As String        ' email Cc field
    Dim strEmailBcc As String       ' email Bcc field
    Dim strEmailSbj As String       ' email Subject
    Dim strEmailBdy As String       ' email Body (message text)
    Dim strEmailAtt As String       ' email attachment

    Application.DisplayAlerts = False   ' turn off Excel alerts

    On Error GoTo ErrorMsg              ' on error goto ErrorMsg section...

' --- Set-up connection to Lotus Notes and Create Email object
    Set WS = CreateObject("Notes.NotesUIWorkspace")
    Set Session = CreateObject("Notes.NotesSession")

    TheUser = Session.UserName
    UserSig = Session.CommonUserName
    Server = Session.GetEnvironmentString("MailServer", True)
    MailFile = Session.GetEnvironmentString("MailFile", True)

    Set DB = Session.GetDatabase(Server, MailFile)
    Set uiDB = WS.CURRENTDATABASE
    Set NotesDoc = DB.CreateDocument

    Set RichTextBody = NotesDoc.CreateRichTextItem("Body")

' --- Set-up dist list, message and attachments
    strEmailTo = ""
    strEmailCc = ""
    strEmailBcc = ""
    strEmailSbj = ""
    strEmailBdy = ""
    strEmailAtt = ""

    NotesDoc.SendTo = strEmailTo        ' To...
    NotesDoc.CopyTo = strEmailCc        ' Cc...
    NotesDoc.BlindCopyTo = strEmailBcc  ' Bcc...
    NotesDoc.Subject = strEmailSbj      ' The subject
    NotesDoc.Body = strEmailBdy         ' Any text to be in the email

    ' Attach a file
    If strEmailAtt <> "" Then
        Set RichTextAttachment = NotesDoc.CreateRichTextItem("Attachment")
        Set NotesAttach = RichTextAttachment.EmbedObject(1454, "", strEmailAtt)
    End If

' --- Send the email / save the message in 'Sent' items
    ' False would not save the sent email to the sent items folder
    NotesDoc.SAVEMESSAGEONSEND = True
    ' Not sure why, but false send the eamil ?
    NotesDoc.SEND False

' --- Close connection to free memory
    Set Session = Nothing
    Set DB = Nothing
    Set NotesAttach = Nothing
    Set NotesDoc = Nothing
    Set WS = Nothing

    ' Turn on Excel alerts
    Application.DisplayAlerts = True

    Exit Sub

' --- if an error occurs display a message... then exit the macro
ErrorMsg:
    If Err.Number = 7225 Then
        MsgBox "The file " & strEmailAtt & " cannot be found in the specified location", vbOKOnly, "Error"
    Else
        MsgBox Err.Number & Err.Description
    End If

    Application.DisplayAlerts = True

End Sub

As always, if you have any questions do let us know via the comments section.

Access, Email, Excel, Lotus Notes, VBA

Excel 2007 – Useful Microsoft Office Shortcuts

The basic Microsoft Office document shortcuts

• For a New Document – CTRL+N
• To Open a Previously Saved Document – CTRL+O
• To Print your Document / Open the Print Settings – CTRL+P
• To Save your Document – CTRL+S
• To Close your Document – CTRL+W

Text Adjustments to your Documents

• Cut the Highlighted text – CTRL+X
• Copy the Highlighted text – CTRL+C
• Paste the Highlighted text – CTRL+V
• Select All the Text in the Document – CTRL+A

Change the Highlighted Text to your set Normal Font Style – CTRL+SHIFT+N

• To Change the Font – CTRL+D
• To Insert a Hyperlink – CTRL+K
• To make the Text Bold – CTRL+B
• To make the Text Italic – CTRL+I
• To Underline the Text – CTRL+U
• To Justify the Paragraph – CTRL+J
• To Left Align the Text – CTRL+L
• To Right Align the Text – CTRL+R
• To Centre the Text – CTRL+E

For the minor mistakes that creep into your Document

• To Find something in your Document – CTRL+F
• To Undo – CTRL+Z
• To check your Spelling and Grammar – F7
• To use the Thesaurus – Shift+F7

And if anything goes wrong you can always use

• Help – F1

Quick Zoom, assuming you are using a computer with a mouse and it has a wheel, then you can quickly and easily zoom in and out of a document.

• To Zoom In – CTRL+Scroll up
• To Zoom Out – CTRL+Scroll Down

Website Design and Development

Hello again, it’s been a while since the last blog post so I thought it a good idea to show you what we’d been up to.

We’ve been working on a couple of websites, hope you like our work.

Some of the above are still a work in progress, but we hope they show a few options from the range of styles we have to offer.

Using VBA to get a File’s Last Modified Date

It 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.

Outlook Email using VBA

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()
'-- Requires a reference to the Microsoft Outlook 8.0 Object Library or higher --

    ' Creates and sends a new e-mail message with Outlook
    Dim OLF As Outlook.MAPIFolder, olMailItem As Outlook.MailItem
    Dim ToContact As Outlook.Recipient

    Set OLF = GetObject("", "Outlook.Application").GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)
    Set olMailItem = OLF.Items.Add ' creates a new e-mail message
    With olMailItem
        ' message subject
        .Subject = "This where the subject text goes..."

        ' add a recipient
        Set ToContact = .Recipients.Add("name@org.net")
        ' add another recipient
        Set ToContact = .Recipients.Add("name@org.net")
        ' set latest recipient as CC
        ToContact.Type = olCC
        ' add another recipient
        Set ToContact = .Recipients.Add("name@org.net")
        ' set latest recipient as BCC
        ToContact.Type = olBCC

        ' the message text with a line break
        .Body = "email message"

        ' insert attachment
        .Attachments.Add "C:\FolderName\Filename.txt", olByValue, , "filename goes here"
        ' insert shortcut
        .Attachments.Add "C:\FolderName\Filename.txt", olByReference, , "Shortcut to Attachment"
        ' embedded attachment
        .Attachments.Add "C:\FolderName\Filename.txt", olEmbeddeditem, , "Embedded Attachment"
        ' OLE attachment
        .Attachments.Add "C:\FolderName\Filename.txt", olOLE, , "OLE Attachment"

        .OriginatorDeliveryReportRequested = False ' True would be delivery confirmation
        .ReadReceiptRequested = False ' True would be read confirmation
        '.Save      ' saves the message for later editing
        '.Display   ' displays the email message for editing
        .Send       ' sends the e-mail message (puts it in the Outbox)

    End With

    Set ToContact = Nothing
    Set olMailItem = Nothing
    Set OLF = Nothing

End Sub

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.

An introduction to Excel VBA (Macros)

The purpose of my next few blog posts will be to go over the basics of the Visual Basic for Applications (VBA) programming language that comes within the Microsoft Office suite.  VBA is a programming language aimed at ‘normal’ people, it’s written in a style similar to normal written English. 
Programming with VBA in Excel means that you can instruct Excel to automatically do things that you would normally do manually — saving you time.
This blog post will concentrate on the Visual basic Editor (VBE). The VBE is the tool you will use to write your VBA macro code and create userforms.  Macro code is the instructions you are giving to Excel to follow, userforms are what you use to allow the user to interact with the macro.
Below are some screen shots of the VBE and notes to explain what each of the sections are for.  You can open the VBE by pressing Alt+F11 (this will switch between the VBE and Excel).  In Excel 97 through 2003 you can also use the command bar menu; Tools-Macro-Visual Basic Editor.  In Excel 2007 you will need the ‘Developer’ ribbon turned on, and press the ‘Visual Basic’ button.

The VBE (Visual Basic Editor)

The Project Explorer

The Project Explorer displays all of the workbooks currently open in Excel (including Add-Ins and hidden workbooks).  A tree diagram is used to display the objects under each project (worksheets, userforms, modules and class modules).  Modules contain the macro code.  Class Modules are covered in another blog post which can be found here.

The Code Window

The code window is where the macro code is written / displayed.  All of the objects in your project will have an associated Code window.  To open them double-click the object in the Project Explorer window to bring up the Code Window. For example, to view the Code window for the Sheet1 object, double-click Sheet1 in the Project Explorer window. Unless you’ve added some VBA macro code, the Code window will be empty.

The Properties Window

The properties window allows you to amend the properties for all of the objects in your project.  The example above is for a Sheet1, it is more commonly used to edit the properties of userforms and controls (buttons, list boxes, combo boxes etc).  For the worksheet above you can change the visible property, disable cell selection etc.

Excel 2007 Developer Ribbon

To do any sort of VBA coding within Microsoft Excel 2007 it will prove useful to turn on the Developer Ribbon.  You can use Alt+F11 to switch between the VBE and Excel, but having the Developer Ribbon turned on will make life easier.  The Developer Ribbon is like the Visual basic toolbar in Excel 97 through 2003.

To turn on the Developer Ribbon you will need to click the Office button , then select Excel Options, bottom right of the menu.  Within the ‘Popular’ category select the ‘Show Developer tab in the Ribbon’.

Once this is checked you will see the following controls now available within Excel.

[SetupFunctionIDs] [PickPlatform]...?

A company I work for has recently upgrade from MS Office 97 to MS Office 2003.  Yes, I’m aware that even with this upgrade they’ve still moved to a package which Microsoft withdrew support for in 2009.

Anyways, since the upgrade a few of the more advanced users have noticed that every time they open the VBE (Visual Basic Editor) some code is already present in the Immediate Window:

[auto_open] <
[SetupFunctionIDs] <
[SetupFunctionIDs] >
[PickPlatform] <
[PickPlatform] >
[VerifyOpen] <
[VerifyOpen] > 1
[RegisterFunctionIDs] <
[RegisterFunctionIDs] >
[auto_open] >

I had to do a little searching to remind myself about this and came across a thread on the MrExcel message boards.  The general gist is that it appears if you are using the

(atpvbaen.xla) add-in.  It appears that it is a Microsoft error, apparently it was part of their debugging method to see which routines were running and which weren't (< means starting the sub, and the > means exiting it).

Technorati Tags: VBA,VBE,[SetupFunctionID],Analysis Tookpak - VBA

Playing a sound file using VBA

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.

Do… Loop and For… Next in VBA

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").Select 
    Do
        ActiveCell.Offset(0, 1).FormulaR1C1 = "=SUM(RC[-1]+5)"
        ActiveCell.Offset(1, 0).Select
    Loop Until IsEmpty(ActiveCell)

This 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").Select
    Do Until IsEmpty(ActiveCell)
        ActiveCell.Offset(0, 1).FormulaR1C1 = "=SUM(RC[-1]+5)"
        ActiveCell.Offset(1, 0).Select
    Loop

Moving 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 Workbook
    Dim ws As Worksheet

    Set wb = ThisWorkbook

    For Each ws In wb.Worksheets
        Debug.Print ws.Name
    Next ws

The above code would be quicker then:

    Dim l As Long
    Dim wb As Workbook

    Set wb = ThisWorkbook

    For l = 1 To wb.Worksheets.Count
        Debug.Print wb.Worksheets(l).Name
    Next l

As always, I hope the above examples have been helpful.  If you have any questions or comments please do let us know.

Working with Collections

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_Employee
    Dim colEmp As Collection

    Set colEmp = New Collection

    ' Loop through your existing list
    For Each Item In SomeList

        ' Create a new instance of the employee class
        clsEmp = New cls_Employee

        ' Set the properties of the class
        ' As per the example in the previous blog post

        'Load the class to the collection, _
         using the employee's name as the 'key'
        colEmp.Add clsEmp, clsEmp.Name

    Next Item

Now, 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 employee
    Dim clsEmp As cls_Employee
    clsEmp = colEmp("Fred Smith")

    ' Use the key to remove a specific employee
    Dim clsEmp As cls_Employee
    colEmp.Remove "Fred Smith"

Something 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.

Excel Picture Quiz 3

Right, so finally to stage 3, adding the counter and locking the cells. First choose where you are going to put the counter, and use the following formula;

=countif(B5:J17, "correct")

Again changing the italicised bits to fit your quiz;

B5:j17 - the range of cells where the answers are marked
correct - or whatever you have set your quiz to say for correct answers





The final stage is to set the cells you want people to put their answers into as unlocked so we can protect the sheet and stop anyone from checking the formulas to get the answers.

To do this either select all the answer cells using the control button, or one at a time right click and select properties, and uncheck the locked setting under protection











Finally protect the sheet; select 'Protect Sheet' from the Review ribbon, you can set a password if you wish. This will mean you can only select the cells we set as unlocked.

Once you have selected ok your quiz is finished.

I'll upload a copy of my incomplete version here for anyone to play with, please let me know if you have any hints or tips to make this better.

Thanks
Dan

Working with Class Modules

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 Explicit

Private strName As String
Private strDept As String
Private lngAge As Long
Private dblSalary As Double

Now 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 property
Public Property Get Name() As String
    Name = strName
End Property
Public Property Let Name(value As String)
    strName = value
End Property

' Department property
Public Property Get Dept() As String
    Dept = strDept
End Property
Public Property Let Dept(value As String)
    strDept = value
End Property

' Age property
Public Property Get Age() As Long
    Age = lngAge
End Property
Public Property Let Age(value As Long)
    lngAge = value
End Property

' Salary property
Public Property Get Salary() As String
    Salary = dblSalary
End Property
Public Property Let Salary(value As String)
    dblSalary = value
End Property

The 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 property
Public Property Get Tax() As Double
    Tax = Calculate value
End Property

Class modules can also contain sub routines and functions, such as the PrintEmployeeData below.

' Print employee data routine
Public Sub PrintEmployeeData()
    ' You code appears here
End Sub

Now 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 Explicit

Sub AddEmployee()
    Dim clsEmp As cls_Employee

End Sub

ClsEmp 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_Employee
    clsEmp.Name = "Fred Smith"
    clsEmp.Dept = "Accounts"
    clsEmp.Age = 22 
    clsEmp.Salary = 25000

You 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.

Excel Picture Quiz stage 2

In stage 2 we get the quiz to mark the questions, there are several ways to do this however for this tutorial I am going to use an IF statement. This allows us to have two responses, correct and incorrect, these can be substituted for your own message.

I am going to have the answer in cell B4 and the responce in B5, so the code for B5 is

=IF(B4="Johnny Depp", "Correct", "Wrong")

the italicised parts are the ones you will have to change for each picture.

B4 - the cell reference for the answer you are checking.

"Johnny Depp" - the correct answer.

It is important to note that for the counter I am going to create later to work the 'correct' message must be the same for every picture.

Set the answer cells to change colour using conditional formatting, in Office 2007 this is to the right on the home tab.











Simply set the cell to change colour when the cell is equal to your 'correct' message.

Excel Picture Quiz

So I thought it was about time I put something on here and I thought what better than to start with some instructions for creating a simple and fun picture quiz in Excel.

The instructions presume you have some basic knowledge of Excel to start with, but if there are any steps which you want explaining in more detail feel free to contact me using the quick contact on my website www.zypher.co.uk

I will split this into the following stages / lessons

1. Set out the quiz
2. Add functions and conditional formatting
3. Add a score counter
4. block access to the answers

We start by setting up the layout, set the column widths so you have a thin column followed by a wider one and repeating across the screen, you should start and end with thin columns. The wider columns will hold the images so set these appropriately.

Next set the height of some of the rows to allow them to hold the images, leaving at least 2 rows normal height, this is where the answer and correct or incorrect message will go.

Set the background colour of the cells and add a title to the centre of your top row.

Insert your images into the larger cells and add instructions to the side, I have used a text box though you could just as easily merge some cells for this.

Transpose an array

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 Variant
' Custom Function to Transpose a 0-based array (v)

    Dim x As Long, y As Long, Xupper As Long, Yupper As Long
    Dim tempArray As Variant

    Xupper = UBound(v, 2)   'rows
    Yupper = UBound(v, 1)   'columns

    ReDim tempArray(Xupper, Yupper)
    For x = 0 To Xupper
        For y = 0 To Yupper
            tempArray(x, y) = v(y, x)
        Next y
    Next x

    TransposeArray = tempArray

End Function

I 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.

Current User and Current PC

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.

Access and conditional formatting reports

This isn't the important, in-depth blog post I'd hoped to start with, but is a subject which has come up in discussions a couple of times recently, so thought it worth posting.
Access 2007 allows you to apply conditional formatting to reports.  This means you can highlight one field, either because of it's value, or the values within in other fields.  You do this by right clicking the relevant field and selecting ‘Conditional Formatting’.
The next step is to choose which field(s) you want the formatting to rely upon.  To do this choose either ‘Field Value Is’ or ‘Expression Is’.  The first choice makes the conditional format dependant on the selected field, the second allows you to enter a formula which can then look at other fields.
In the example that follows I have chosen to highlight the ‘Product’ field where the ‘Quantity’ is higher then 2 using ‘Expression Is’.

When using IF statements, Access, unlike Excel wants to see IIF.

And now for the reason I’m posting this.  When choosing to change the back colour you need to ensure that the control’s (Label, TextBox etc) back style is set to ‘Normal’.  When creating a report the default back style is ‘Transparent’, this means the back colour format is not shown. 

Below are two pics, the first where the control’s back style is ‘Transparent’.
And the  second after changing the back style to ‘Normal.

Well, I hope that proves useful to someone, if you have any comments or questions let us know.

Brand new blog.

Hi and welcome to the brand new blog from the guys at Zypher.co.uk.  
This blog will predominantly be about automating Microsoft Office and building MS Office tools and applications.  The occasional post may well cover over areas of ICT (Information Communication Technology) including, but not limited to our ICT care package for home and business - ICT support, Network design, management and upgrades, Database development, Website development and ICT Training.

We'll be putting our first few articles up over the coming days so hope you visit us again soon.

The team at Zypher.


You can also visit our site by clicking here.