by This is a short step-by-step tutorial for beginners showing how to add VBA code (Visual Basic for Applications code) to your Excel workbook and run this macro to solve your spreadsheet tasks.
Most people like me and you are not real Microsoft Office gurus. So, we may not know all specificities of calling this or that option, and we cannot tell the difference between VBA execution speed in different Excel versions. We use Excel as a tool for processing our applied data.
Suppose you need to change your data in some way. You googled a lot and found a VBA macro that solves your task. However, your knowledge of VBA leaves much to be desired. Feel free to study this step-by-step guide to be able to use the code you found:
Insert VBA code to Excel Workbook
For this example, we are going to use a VBA macro to remove line breaks from the current worksheet.
- Open your workbook in Excel.
- Press Alt + F11 to open Visual Basic Editor (VBE).
- Right-click on your workbook name in the "Project-VBAProject" pane (at the top left corner of the editor window) and select Insert -> Module from the context menu.
- Copy the VBA code (from a web-page etc.) and paste it to the right pane of the VBA editor ("Module1" window).
- Tip: Speed up macro execution
If the code of your VBA macro does not contain the following lines in the beginning:
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManualThen add the following lines to get your macro to work faster (see the screenshots above):
- To the very beginning of the code, after all code lines that start with Dim (if there are no "Dim" lines, then add them right after the Sub line):
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual - To the very of the code, before End Sub:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
These lines, as their names suggest, turn off screen refresh and recalculating the workbook's formulas before running the macro.
After the code is executed, everything is turned back on. As a result, the performance is increased from 10% to 500% (aha, the macro works 5 times faster if it continuously manipulates the cells' contents).
- To the very beginning of the code, after all code lines that start with Dim (if there are no "Dim" lines, then add them right after the Sub line):
- Save your workbook as "Excel macro-enabled workbook".
Press Crl + S, then click the "No" button in the "The following features cannot be saved in macro-free workbook" warning dialog.
The "Save as" dialog will open. Choose "Excel macro-enabled workbook" from the "Save as type" drop-down list and click the Save button.
- Press Alt + Q to close the Editor window and switch back to your workbook.
How to run VBA macros in Excel
When you want to run the VBA code that you added as described in the section above: press Alt+F8 to open the "Macro" dialog.
Then select the wanted macro from the "Macro Name" list and click the "Run" button.
256 comments
VBA Macro for Change Case works well
thank you very much
Dear hoe to i set vba project in colum as(rohit kumar parasshar)in single colum i read in a video (by right click view code entir.excel.com
(Private Sub Worksheet_SelectionChange(ByVal Target As Range)
...................what i write heare
End Sub)
There are many mistakes in this post
Im afraid horrible text and doesnt work
This does not work
I want to thank you for this code and tutorial. I don't write VB code and just need a quick solution to a problem and this resolved it. Thank you so much for your time and effort.
Hi I am trying to create a template workbook for some data analysis. I have worked it up but now need to delete the data and save it as a template.
I was just wondering if you have a workbook that has a VBA code attached if it is save as a macro enabled workbook do you have to add the code again when you reopen the workbook?
Many thanks,
Karen
Karen:
When you put the VBA in the module and saved it, the code would be in Excel. It would be available to be called from inside that workbook. Click Alt-F8 and you can see what code is available in that workbook. You should see the names of the modules you built and saved.
Dear Sir / Madam.
I have made a user form with 7 entries. I would like to have an Even number to become a Red font through my user form. So if the number is 346 then this number and all the other 7 boxes become a red font. Any help would be greatly appreciated. I just don't know what to write.
Thanks and Kind regards
Rob
Thank you. Works perfectly!!
Hi there,
I tried your code but it doesn't do anything. No messages, nothing. What am I doing wrong?
the same here!!
Same for me
I just want to say thank you for this fantastic, simple solution and for your awesome products in general.
Hello,
I want a code which is used to cut the rows having the red color and paste in another sheet and when color changes to white should transfer to same sheet (online spread sheets)
IT IS VERY NICE THANK YOU
Hellow Team,
we need your help for VBA Coding in excel i don't know VBA coding how to start
learning VBA Coding.
Please help me to reference link to enhance skills I vBa
Hello,
Anyone who can assist me to provide a vba code on how to generate a responses that will be consolidated to another worsheet. Example, Response with "Will be included in the remediation" and "Partial Remediation" will be generated to the consolidated worksheet. Thanks
Hi All,
I would appreciate if someone could help me with some VBA coding I need to do. I am creating a userform for an excel document. It is quite simple but I really dont know how VBA coding works. I have 6 categories in my spreadsheet that have got to filled up in the form with numbers from 1/10 and then there is a comments box to for extra comments and a name box for the user name. finally it has 2 command boxes: Add company and close form.
I have done the form now and given the usual names to the boxes such as:
txtname
cboquality
cboprice
cbostock
cmdAdd
cmdClose
Can someone help me out with the code so that once the form is filled and the command box for add clicked all the information goes to the spreadsheet?
It would be greatly appreciated
Thank You! Big help!
Hi!
If i can't save file after i add this VBA code. It says that there is an error and Excel may able to save file by removing or repairing some features. Options are to click continue or cancel. When clicking continue it want to save file as another one, but in the end it says :file was not saved".
Thank you so much. Big help.
Excellent guideline. Thanks.