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.

Screenshots in Excel

This is quite a useful tip.

You probably already know how to include an entire screen shot within your Excel worksheet (Press “PrintScreen” and paste the screen shot where you want it with Ctrl+V or ‘Paste’). However, if you only want to show a selected region like a small part of the screen in your spreadsheet, try this:

To Copy:
Select the area you wish to copy from your Excel worksheet
If you have Excel 2007, go to “Paste”, then “As Picture”, then “Copy as Picture”
If you have Excel 2010, go to the “Copy” link and click “Copy as Picture”

To Paste:
Ctrl+V or ‘Paste’

Once the ‘image’ is placed within your workbook, you’ll be able to move it and resize it in the same way as you can any other graphic.

If you are copying text/data from the worksheet; When copy/creating the image, after selecting “Paste”->”Copy as Picture” a pop-up will appear (below):

Selecting “As shown on screen” will copy the gridlines as well as the text/data.  Selecting “As shown when printed” will not.

(Remember that adding graphics to the worksheet will increase the size of the file in which the workbook is saved.)