Find and replace VBA code using VBA

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()
'-----------------------------------------------------------------------------' Procedure : FindAndReplace
' Purpose   : Searches the active workbook for a specific code line and
'             replaces it with a new code line
'-----------------------------------------------------------------------------
    Dim SL As Long, EL As Long, SC As Long, EC As Long
    Dim S As String
    Dim Found As Boolean
    Dim sFind As String
    Dim sReplace As String

    sFind = "my old line of code"
    sReplace = "my new line of code"

    With ActiveWorkbook.VBProject.VBComponents("MyCodeModule").CodeModule
        SL = 1
        SC = 1
        EL = 99999
        EC = 999
        Found = .Find(sFind, SL, SC, EL, EC, True, False, False)
        If Found = True Then
            S = .Lines(SL, 1)
            S = Replace(S, sFind, sReplace)
            .ReplaceLine SL, S
        End If
    End With

End Sub

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.

Aprils Fools ‘Quit Excel’ Workbook_Open Event :)

I just came across this Aprils Fools trick while looking through some old code files.  Figured as it’s the right time of year I’d post it.  Put this code in the ‘ThisWorkbook’ code module, save the file and wait for the users to shout ;)

Private Sub Workbook_Open()
'-------------------------------------------------------------------------
' Procedure : Workbook_Open
' Author    : Matthew Sims - Zypher.co.uk
' Date      : 01/03/2005
' Purpose   : To annoy ;)
'             Quit Excel 30 times out of 100 when the user opens this file
'-------------------------------------------------------------------------
'
    ' Set the annoying level (percentage chance that Exel will close)
    Const annoying_level As Long = 30
    Dim annoying_number As Long

    Application.DisplayAlerts = False

    Randomize
    annoying_number = 100 * Rnd

    ' If the randomly chosen 'annoying_number' is lower then the
    ' preset 'annoying_level' then quite Excel
    If annoying_number < annoying_level Then
        ' Quit Excel
        Application.Quit
    End If

End Sub

Using VBA to Truncate Decimal Values

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 Double
'-------------------------------------------------------------------------
' Procedure : TruncTo
' Author    : Matthew Sims
' Date      : 08-Oct-2010
' Purpose   : Truncate a decimal value to the requested number of decimal places
'-------------------------------------------------------------------------
'
    On Error GoTo TruncTo_Error

    If IsNumeric(dblValue) Then
        TruncTo = Int(dblValue * 10 ^ lngPlaces) / 10 ^ lngPlaces
    Else
        TruncTo = 0
    End If

    On Error GoTo 0
    Exit Function

TruncTo_Error:
    ' Add some error handling code here
End Function

VBA Adjusting Speaker Volume (and Mute)

Following 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" ( _
   ByVal bVk As Byte, ByVal bScan As Byte, _
   ByVal dwFlags As Long, ByVal dwExtraInfo As Long)

Sub VolUp()
'-- Turn volumn up --
   keybd_event VK_VOLUME_UP, 0, 1, 0
   keybd_event VK_VOLUME_UP, 0, 3, 0
End Sub

Sub VolDown()
'-- Turn volumn down --
   keybd_event VK_VOLUME_DOWN, 0, 1, 0
   keybd_event VK_VOLUME_DOWN, 0, 3, 0
End Sub

Sub VolToggle()
'-- Toggle mute on / off --
   keybd_event VK_VOLUME_MUTE, 0, 1, 0
End Sub

Again, this requires the PC to a sound card and speakers.

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.

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