Hello Excellers. Today’s Blog post is a little different for #macromondays. I want to show you work with some Excel VBA that is already written and how to copy it into your workbook. Sometimes you can find code already partly or wholly suitable for your requirement from other Excellers that have posted it to blog posts, Excel User Forums or other good sources of information online. So instead of starting from scratch you can get a a head start with some code, copy it into your Excel project. So lets run through how to work with Excel VBA that is pre written and add it your Excel project.
This article will cover how to add the Excel VBA code to
- A regular Excel VBA Module
- An Excel worksheet
- Your Excel workbook
Copying Code To A Regular Excel VBA Module.
It is really simple to copy code to a regular Excel VBA module if you want to store a regular macro. This includes UDF’s (User Defined Functions), or global variables. Follow the steps below.
- Copy the code you want to use
- Hit ALT+F11 to open the Excel VBA Editor
- Select Insert | Module
- Paste your Code into the new module you created using Edit | Paste
- Job Done!
If you need an example of this then please feel free to play with the Excel VBA code below. With this Excel VBA macro you can delete any rows of data that so not contain the work ‘Invoice’. If you want to adjust this VBA macro to with with your Excel worksheet then change the word ‘Invoice’ to a string of your choice.
Sub DeleteRowsNotMeetingCriteria()
‘Macro by www.howtoexcelatexcel.com
With ActiveSheet
.AutoFilterMode = False
With Range(“A1”, Range(“A” & Rows.Count).End(xlUp))
.AutoFilter 1, “<>Invoice”
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False
End With
End Sub
Copying Code To An Excel Workbook.
If you need to copy and paste code into an Excel workbook due to the code controlling a specific event in the workbook such as the Workbook_Open Event, then that code needs to be added to the Workbook module.
- Copy the code you want to use
- Select where workbook where you want to store the code
- Hit ALT+F11 to open the Excel VBA Editor
- In the Project Explorer, find your workbook, and open the list of Microsoft Excel Objects
- Right-click on the ThisWorkbook object, and choose View Code
- Choose Edit | Paste
Again if you need an example of this then please feel free to play with the Excel VBA code below. This Excel VBA macro will open the workbook to a specific tab. In the particular example I have instructed Excel to open the workbook to worksheet “MM22”. Cool right?.
Private Sub Workbook_Open()
Sheets (“MM22”).Select
End Sub
Copying Excel VBA Code To An Excel Worksheet.
If you need to copy and paste Excel VBA that runs automatically when something specific happens in the workbook like a cell entering a number in a cell then this is classed as a Worksheet_Change Event and the code needs to be stored in the worksheet module. This again is very easy to do.
- Copy the code you want to use
- Right click on the worksheet where you want to store the code and the code to run
- Select View Code
- Hit Edit | Paste and paste your code
- Job Done!
So, like all of the other ways to work With Excel VBA I have provided some sample code to work with. Feel free to copy the code and test for yourself. This macro will save the Excel workbook when
Sub Worksheet_Change(byVal Target As Range)
‘Macro by www.howtoexcelatexcel.com
.If Intersect(Target, Range(“C5:C6”)) Is Nothing Then
Exit Sub
Else
ActiveWorkbook.Save
End If
End Sub
Running An Excel Macro From A Regular Module.
Once you have copied and pasted your code into a Regular Module, you should be able to see the name of the Macro in the Macro Window.
- In the list of macros, click on the macro that you want to run
- Click the Run button
Always make a backup of your Excel workbook when you run any macro. In particular if you have copied the code from another source. Many people share their Excel VBA code and macros for other users to work with. Just test it and back up your Excel workbooks.
If you want to see all of my blog posts in the Macro Mondays Series you can find them all in the link below. Why not book mark it?, Yes it is updated EVERY Monday.
How To Excel At Excel – Macro Mondays Blog Posts.
