Get Started With VBA – 3 Easy Examples

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? 😉

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.

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.

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

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