• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Get Started With VBA – 3 Easy Examples

You are here: Home / Excel VBA / Get Started With VBA – 3 Easy Examples
Simple Code to get Started With VBA
March 15, 2016 by Philip Treacy

You can create very complicated and powerful VBA routines in Excel, but not all VBA code has to be like this.

VBA is here to make your life easier. Even simple, little things can speed up what you do.

Whether you use the macro recorder, or write your own code, they are not hard to create.

In this post I'm going to look at three easy VBA routines that can make your life just that little bit easier.

Hopefully you'll see that it's not hard to do these things, and maybe you'll be inspired to learn more VBA.

Example 1 : Adding a Worksheet and Naming It

Normally you create a new sheet, then rename it.

How about a macro that asks you for the sheet name then adds that sheet? Doing two things at once, that's a 100% increase in efficiency isn't it? 😉

Input box asking for new worksheet name

We use an InputBox (shown above) to ask what to name the new sheet, and we store that name in the Sheetname variable.

If you need to get more complicated information for the macro you could create a userform, but for this example that would be overkill.

We check that the Sheetname is not blank, then we create the sheet and give it its new name. That's it.

Sub CreateSheetandNameIt()

    Dim Sheetname As String
    Dim NewSheet As Worksheet

    ' Get the new sheet name    
    Sheetname = InputBox("What is the new sheet name?", "Name new sheet")
    
    ' Remove white space with Trim() and then check Sheetname is not blank    
    If Trim(Sheetname) <> "" Then
    
        With CurrentWorkbook
            
            ' Add the sheet and name it
            Set NewSheet = .Sheets.Add(After:=.Sheets(.Sheets.Count))
            NewSheet.Name = Sheetname
        
        End With
    
    End If
    

End Sub


Example 2 : Changing the Sign of a Number

If you have negative numbers and you want to make them positive, this macro will do it. It will also make positive numbers negative.

There are other approaches to this, for example you can use Paste Special->Multiply, to copy the numbers and then paste the results when you multiply those numbers by -1.

But in this macro we are again aiming for the lowest number of clicks to perform a task. We just select the range of numbers we want to change, then click the Change Sign button.

Change the sign of a number

The code uses one line of VBA to call the Evaluate function

Sub ChangeSign()

    Selection = Evaluate(Selection.Address & "*-1")
    
End Sub

Example 3 : Saving a Worksheet as a Workbook

Another handy little piece of code allows you to save your current worksheet as a new workbook.

I'm using FileDialog to ask what folder to save the file into, then creating the new workbook with the same name as the active sheet, and saving that new workbook.

FileDialog asking for folder to save file in

This code will work for a single worksheet; whatever sheet is currently active in the workbook containing the macro.

If you are planning on saving lots (or all) the sheets as workbooks, you'll need to alter the code to go through each sheet in turn.

As with all code, this is written to perform a specific task. If you wanted to save the sheets as CSV's, or save them with a particular name (maybe appending the current date to the name), then you'll have to modify the code.

Limitations

Perhaps the word limitations conjures up the incorrect image, working boundaries may be a more appropriate term. After all, if you want your Pivot table to work correctly, you shouldn't have blank rows or columns in your source data.

Any code you write will only work within certain boundaries. If you go beyond those, you may well encounter an error or get some unplanned behaviour.

It is always recommended that you plan for, and write error handling into your code.

Yes I know, I haven't put a lot of error handling into the above code. If you want to add it, think of it as your homework 🙂

Looking at the Evaluate function, as long as we pass it numbers it works great. But if the range we select contains a formula, text or a blank cell, you probably won't get the result you were expecting.

If we pass text to Evaluate the result is a #VALUE! error. If we give it a blank cell, we get 0 as the result. If we give it a formula, it overwrites that formula with the result it generates.

As long as we plan for these possibilities then we can handle them. We could use ISTEXT, ISNUMBER and ISBLANK to check for text, numbers and empty cells respectively. But those functions have their own limitations so we'd need to bear that in mind when using them.

Getting Started With VBA Shouldn't Be Scary

Now I know these aren't exactly Earth shattering bits of code, but the idea is to show you that writing VBA doesn't have to be scary and intimidating.

Even just doing simple things like this can make you a bit faster at your job, but more importantly for me, I feel it gives me confidence that I am taking more control of Excel and getting it to do what I want.

At the end of the day Excel is just a tool, and VBA is part of it. Learn to use the tools you have to become better at what you do and you will enjoy it more.

Example Code

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

These three examples are available to download in a single workbook. Have a look through the code to understand how things work and try some modifications yourself.

Running the Code

There are numerous ways to run your macros. In the example workbook I've assigned macros to the shapes on each sheet. You could also create a keyboard shortcut to do the same thing.

You could create an icon on the Ribbon or create an icon on your QAT (Quick Access Toolbar).

Generally, a macro can only be run within the workbook you created it in, but you can store all your macros in PERSONAL.XLSB, so they can be used in any workbook you want.

What Else Can You Do With VBA?

(Almost) whatever you like. Here are some other examples

Excel Date Picker

Highlight Selected Cells and Preserve Cell Formatting

Find Missing Numbers

Create a hyperlinked list of files from a folder

Create barcodes

Convert numbers to words

Send worksheets by email

Simple Code to get Started With VBA

More Excel VBA Posts

Display All Matches from Search in Userform ListBox

Display All Matches from Search in Userform ListBox

Search a range for all partial and full matches of a string, and display matching records (entire rows) in a userform listbox. Sample code and userform.
animating excel charts

Animating Excel Charts

Use animation correctly to enhance the story your data is telling. Don't animate your chart just for some eye candy. Sample code and workbook to download.
dynamic data validation lists in userforms

Dynamic Data Validation Lists in Userforms

Data validation lists using the same source that are dynamically modified to prevent the same choice being made in each list.
show report filter pages for power pivot pivottables

Show Report Filter Pages for Power Pivot PivotTables

PivotTables created from Power Pivot can't use the 'Show Report Filter Pages' option. But this piece of VBA allows you to do just that.
charting real time data in excel

Charting Real Time Data in Excel

Receive data in real time and chart the data as it arrives. Can be used to chart things like stock prices or sensor readings. Sample code and workbook
select multiple items from drop down data validation list

Select Multiple Items from Drop Down (Data Validation) List

Choose multiple items from a data validation (drop down) list and store them all in the same cell. Sample workbook with working VBA.
Excel Calendar (Date Picker) to Use in Worksheets and Userforms

Multi-Language Excel Calendar (Date Picker) for Worksheets and Userforms

Easy to use, highly customizable and multi-language. This date picker is implemented as a userform that is simple to integrate into your workbook.
automating and emailing pivot table reports

Automating and Emailing Pivot Table Reports

Automate the creation and distribution of pivot table reports with some VBA. Send reports to multiple email recipients using Outlook.
search for data with userform

Searching for Data With a User Form

Search a list of records (like a table) using a user form, and then populate the fields of the search form when the record is found.
Checking values in range objects with vba

Checking Values in Range Objects With VBA

Use built in tools or your own code to examine the values contained within Range objects in VBA. Sample code to download.


Category: Excel VBA
Previous Post:selecting multiple items in data validation listPopulating Multiple Cells from Single Data Validation (Drop Down) List
Next Post:Avoid Writing Complex Excel Formulascomplex excel formulas

Reader Interactions

Comments

  1. Rukhsana

    March 4, 2017 at 4:16 pm

    Very productive indeed

    Reply
  2. Higgins James

    July 20, 2016 at 10:30 am

    I downloaded the 3 easy examples file and tested all 3 macros and they worked fine and they also work when in another file as long as this file is open. How do I place the macros in my personal.XLSB file so that they can work at all times?

    Thanks

    Reply
    • Philip Treacy

      July 20, 2016 at 10:38 am

      Hi James,

      This blog post explains how to set up PERSONAL.XLSB

      Create a personal macro workbook

      You then copy/move the code into a PERSONAL.XLSB module.

      Phil

      Reply
  3. Karen

    March 16, 2016 at 2:29 am

    Interesting VBA code examples. Your group is always inspiring me.

    Reply
    • Philip Treacy

      March 16, 2016 at 8:25 am

      Thanks Karen.

      Phil

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.