Tag Archives: VBA

Unhide Sheets in Excel

How to unhide sheets in Excel? Unhide all Sheets in Excel VBA

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

To unhide Sheets in Excel you need to right click on the Worksheet tab and select the Worksheet you want to unhide from the Unhide Window.

I will start by showing a way to manually unhide an Excel Worksheet using the Excel Unhide Window. Then I will show you a few tricks of how to unhide all Sheets in Excel using VBA. Lastly

Unhide Sheets in Excel

To unhide a Hidden Worksheet in Excel proceed as follows:

  1. Right click on a the Worksheets tab
  2. Click the Unhide button in the opened tab
  3. Select the Worksheet you want to unhide and click Ok

Unhide hidden Sheets in Excel

Unhide Sheet using VBA

To Unhide a single Worksheet in Excel using VBA we need to use open the Visual Basic Editor. To do this quickly simply us this Excel Keyboard shortcut ALT+F11.

You can type the below in the Immediate window and hit Enter:

Where NameOfWorksheet is obviously the name of the Worksheet you want to Unhide in Excel.

Hide Sheet using VBA

If you want to Hide a Worksheet in Excel using VBA you can modify the code above. Instead of using xlSheetVisible just use xlSheetHidden:

This will hide the worksheet named NameOfWorksheet.

Unhide All Sheets in Excel using VBA

To unhide all Sheets in Excel we must revert to VBA as there is no other way to select multiple worksheets using the built in Excel Unhide window. Again let us open the Visual Basic Editor by using the Excel Keyboard shortcut ALT+F11. Next in the Immediate Window let us type:

Unhide all Sheets in Excel using VBA
Unhide all Sheets in Excel using VBA

Below the same code as above but spread across multiple lines. Let us run through this:

Unhide all Sheets by Name

In many cases you don’t necessarily want to Unhide all Sheets in Excel. Instead you might want to Unhide only a subset of the Hidden Worksheets using a name pattern.

Assume you want to Unhide all Worksheets that fall into a certain pattern where part of the name can be any sequence of characters. For this we can amend the code above using the VBA Like operator:

Similarly as above we can wrap it up to a oneliner to run in the Immediate Window:

The code above will unhide all Worksheets which name starts with Hidden and suffixed by any number of characters e.g. numbers like in the example below:
Hidden worksheets Excel

What is happening is using the VBA For Each loop we are iterating through the VBA Collection of Worksheets. When a certain Worksheet name matches our VBA Like function statement we make it visible.

Button to Hide/Unhide Sheets

Lastly to learn how to Unhide Sheets in Excel we will sum up what we have learned and make a simple VBA UserForm to be able to quick manage visibily across the entire list of Excel Worksheets.

Create the UserForm

First you need to create a VBA UserForm with a VBA ListBox and a VBA CommandButton:
Manage Sheets UserForm

Program Initialize and Button Click

If you named your objects correctly past the following code into the VBA UserForm source code:

Run the UserForm

To put our code to the test all we need to do is create and show the ManageWorksheets VBA UserForm. The following VBA Procedures code needs to created in a VBA Module:

When executed this is how it will look like:
Unhide Multiple Sheets in Excel

VBA Tricks

VBA Tips and Tricks

1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)
Loading...

Instant Print in the Immediate Window

Use the “?” preceding a line of code that can evaluate to a value in the VBE Immediate Window. Hit Enter to evalute the code in the next line. See example below:

VBA Trick Use question mark for instant Debug Print
VBA Trick Use question mark for instant Debug Print

This is a VBA Trick I was not aware for a lot time but saves a lot of time when debugging code or trying to evaluate a single line or bit of code without needing to debug a whole functions or procedure.

If you are not familiar with the Immediate Window read my VBE Project tutorial

Evaluate formulas shortcut

One of my favorite VBA Trick is using the evaluate function using square brackets “[]”. If you want to read more read my VBA Evaluate article.

Evaluate works both on:

  • Evaluation of Named Ranges
  • Evaluation of Excel Worksheet Functions

Evaluating Named Ranges in VBA

Here’s an example of Named Range evaluation in VBA:

VBA Trick: VBA Evaluate Ramed Range
VBA Trick: VBA Evaluate Ramed Range

Evaluating Worksheet Functions in VBA

Here’s an example of Worksheet Function evaluation in VBA:

VBA Trick VBA Evaluate Worksheet Function
VBA Trick VBA Evaluate Worksheet Function

This is one of the most useful trick as VBA does not allow you to otherwise easily do a lookup of data is a worsheet.

Remeber to always use the “,” list separator when defining the arguments as VBA is not not dependent on your system country settings even if you separate arguments in regular Excel worksheet functions.

Use VBA. to view all VBA Built in functions and constants

Often you stumble not being able to remind yourself of that one useful VBA Function and need to search the function online. A great VBA Trick is to type in “VBA.” and then you will be provided with a list of available VBA Functions and Constants.

VBA Trick Use VBA to list all built in functions and constants
VBA Trick Use VBA to list all built in functions and constants

User Defined Functions

Excel has a multitude of functions, half of which you probably don’t know. However, what happens when you lack a particular function or want to save time bundling up common functions? Let me introduce you to the little known world of User Defined Functions (UDF).

Simple UDF

A simple UDF can be found below:
VBA Trick Simple UDF

Advanced UDF

Let us not try a more advanced UDF showing how powerful it can be. First I will create a simple table:

VBA Trick UDF Table
VBA Trick UDF Table

Now what I need is to somehow get a number of matches or these records against various criteria e.g. sex, sales, age, country. One way would be to go for a Pivot Table but sometimes a simple UDF can be more useful especially if we want to embed this value in your calculations.

So I will create a UDF that takes these arguments and provides the number of matches:

Now we can use the UDF Function in Excel:

VBA Trick Complex UDF
VBA Trick Complex UDF

Speeding up your VBA Macros

Your macro running slow? A lot of CPU overhead goes into updating your screen in case and recalculations of formulas impacted by your macros. With a simple VBA Trick you can turn off ScreenUpdating and Calculations until your macro is done.

What to do when your macros needs to run for a long time and you need to update your screen every now and then? Use the DoEvents procedure to refresh your screen only in selected intervals.
Read here how learn of more ways of optimizing your VBA performance.

visual basic editor

Visual Basic Editor Tutorial for Excel – How to use the VBE?

1 Star2 Stars3 Stars4 Stars5 Stars (7 votes, average: 4.57 out of 5)
Loading...

The Excel Visual Basic Editor is also sometimes referred to as the VBA Project window. The Visual Basic Editor (VBE) is a simple developer environment available in Excel, Access, PowerPoint, Word and all other MS Office applications. It allows you to code simple Visual Basic for Applications macros. If you are new to VBA checkout my VBA Tutorial.

Today we will learn the basics as well as some tips and tricks on how to master the VBE (Visual Basic Editor).

Think you know it all? Scroll down to the Tips and Tricks!

How to open the Visual Basic Editor?

The Developer tab in the Excel ribbon is required to work with Excel VBA. Apart from enabling you to access your VBA Project, it also contains other useful features and shortcuts which we will dive into later e.g. Macro recording, Form Controls (buttons, checkboxes etc.).

Did you know… you can use the ALT+F11 keyboard shortcut to open the Visual Basic Editor (VBE) window?

Add the Developer tab to your Excel ribbon

To open the options window go to:
File->Options->Customize Ribbon
Next select the Developer tab to add it to the Excel ribbon as shown below:

Excel VBA Tutorial: Select the
Select the “Developer” tab to add it to the Excel ribbon

Click Ok. You should now see a new tab in your Excel ribbon called Developer as shown below:

Excel VBA Tutorial: The Developer tab in the Excel ribbon
The Developer tab in the Excel ribbon

Click the Visual Basic button on the Developer ribbon

Click the button as shown below:

Excel VBA Tutorial: Click the Visual Basic button
Click the Visual Basic button

This will open the Visual Basic editor as shown below:

Excel VBA Tutorial: The VBA editor window
The VBA editor window

The Visual Basic Editor (VBE)

The VBE Project Window

The VBE Project Windows allows you to manage your VBA Project objects such as VBA Modules, Classes and Forms.

Excel Visual Basic Editor Overview
Excel Visual Basic Editor Overview

The VBE Code Window

THe VBE Code Window allows you to edit your VBA code – by selecting a VBA Module, Classes or Form in the VBE Project Window (see above).

Excel VBE Code Window Overview
Excel VBE Code Window Overview

VBE Tips and Tricks

Now for some tips and tricks.

Tip 1: Open the VBE (Visual Basic Editor) with VBA

May sound weird but it is actually a pretty neat trick. The following VBA code snippet can be set as a Button event to open the VBA Project Window:

Below a quick gallery tutorial of How to setup a button to open the VBE from an Excel Worksheet:

Tip 2: Open VBE Window on selected Macro

If opening the VBE (Visual Basic Editor) Window on the click of a Button is no specific enough.. why not a Macro that allows you to open the Visual Basic Project Window to not only open but also open the VBE on a specific Macro of your choice! The VBA code snippet below does precisely that.

The VBA code snippet will open the VBE Window on the “Test” VBA Macro:

Summary

The VBE (Visual Basic Editor) is where all the magic happens. Got any useful VBE tips or tricks of your own? Share them in the comments below!

excel to image

Excel to Image – VBA Save Range or Workbook as Image

1 Star2 Stars3 Stars4 Stars5 Stars (4 votes, average: 4.00 out of 5)
Loading...

How to save an Excel Range as an Image file (JPG)? Today’s post is inspired by my readers emails. In some of my previous emails I considered other Excel Image scenarios such as Adding Images to Excel using VBA or using the Excel Camera Tool to create an image snapshot in Excel. However today I stumbled across a different scenario: Saving an Excel Range as an Image File.

Excel to Image: Save Excel Range as Image

Excel Range to Image
Excel Range to Image
Let us start with an example. The easiest way to create an image in Excel is to Select an Excel Range, next hit the Copy button from Home ribbon and finally by clicking Paste with Picture.

Seems at first like we are just one step away from saving the image as an image file right?

Excel: Right click only to find... there is no Save As Image
Excel: Right click only to find… there is no Save As Image
Wrong! Only problem being… there is no Save As Image button easily available in Excel!

Of course you might say – why no copy and paste to MS Paint or another Image Editor? Yes that is always an option. But let me show you a much better and dedicated tool for taking Snapshots in any Windows application. Next I will show a way to achieve the task above with a simple VBA Macro.

Snipping Tool

Windows Snipping ToolOne way is to use the almighty Microsoft Snipping Tool which is great for creating image snapshots and saving them as images (PNG files).

Windows Snipping Tool Example
Windows Snipping Tool Example
You can see how the Snipping Tool works easily.

The problem however is that the Snipping Tool is not very precise and often detailed Excel Range images are cumbersome to achieve. This is where as usually we can use a bit of Visual Basic for Applications to automate this task…

Excel to Image with VBA

Now let us create an Image from an Excel Range like a pro. First copy the Excel VBA code below to an existing or new VBA Module:

What does the VBA Macro do? The Sub will do the following

  1. Copy the selected Excel Range and paste it as a Picture

  2. Copy the Picture to an empty Chart

  3. Ask for the destination file name

  4. Save the Chart as a JPG image

Now you may as Why not use VBA to save the pasted Picture as an Image file?. Well, you can’t (not so straight forward at least). The workaround above works pretty well however.

Using the Excel to Image VBA

run excel to image macro
Select Excel Range and open Macros
First select the Excel Range you want to save as an Image. To run the VBA Macro click the Macros button to open the Macros window.

run the excel to image macro 2
Select the VBA Macro and click Run
All that is left is to select the VBA Macro from the Macro window and to hit Run.
excel to image save window
Lastly name your destination file and hit Save
There may a short pause for the macro to process the image, shortly after the Save As file dialog should appear. Simply select your destination file name and hit Save and that is it!

Save Excel as Static Image Workbook

As usual I am leaving the best for last. Say you want to share an Excel Workbook as readonly. You can try protecting the Password Protecting your Excel documents but the safety is limited if you want to protect your underlying formulas.

excel to image vba macro
Save your Excel Workbook as a Static Image Workbook
What better way to protect your formulas then to send an Excel Workbook with print screen images of each and every Worksheet? The VBA Macro will additionally save the copy of your file as an XLSX file which means all VBA Macros will be removed as well.

Excel to Static Image Workbook VBA

Below the VBA code snippet to create a copy of your Workbook where every Worksheet is an image copy of the original.

Great right? I have noticed that in most cases the Image Workbook might however be a bit larger than the original file which has its slight downside. Then again this will depend heavily on the type of Workbook (more formatting larger file size).

Let me know what you think!

excel vba evaluate

Excel VBA Evaluate – Tips and Tricks to use Application.Evaluate

1 Star2 Stars3 Stars4 Stars5 Stars (7 votes, average: 4.86 out of 5)
Loading...

Some call it the most powerful command in Excel VBA, while others struggle with simple VBA macros oblivious of its existence – today we with explore the VBA Evaluate function or more precisely the VBA Application.Evaluate function.

We will start with the basics and then move on to more elaborate examples and uses of the VBA Evaluate function.

VBA Evaluate

The Excel VBA Evaluate function converts a Microsoft Excel Name to an object or a value.

Syntax

The syntax for the Evaluate function in VBA is:

Parameters

Name
A formula or the Name of the object you want to evaluate. The length of the Name must be less than or equal to 255 characters.

Examples

Example 1: Simple formula evaluation

Example 2: Using Excel cells in formula evaluation

What out for the first example above! As it might evaluate differently depending on the Activeworksheet!

Example 3: Using functions in formula evaluation

vlookup table
This example is going to be a bit more complex for several reasons I will explain after the example.

Consider the table on the right I will use to run a simple VLOOKUP function. Let us pull up the value associated with the lookup name “Andrew”.

Now there are several things you need to learn from the example above:

  • Escaping strings enclosed within double apostrophes with another set of apostrophes “” – otherwise expect errors
  • Use a comma (,) to separate all function arguments REGARDLESS of your default system list separator as VBA assumes the comma by default
  • You don’t need an equals sign before your formula. The Excel VBA Evaluate functions assumes this by default

Now that we know the basics of the Excel VBA Evaluate function lets us explores some useful tips:

Use Evaluate brackets instead of Range

Did you know you can encapsulate Excel formulas in VBA in brackets to achieve the same results are embracing them within the Evaluate function? See example on the right. The Evaluate function can be easily swapped with square brackets to achieve same resutl.

You can easily replace a VBA Range with square brackets as well:

CYou can seriously shorten your VBA code:

Array definition using VBA Evaluate

You can also define elements in a VBA Array in just a single line without needing to resort to the VBA Substring function like so:

Summary

The VBA Evaluate function is a very powerful tool if you want to easily reference cells in your workbook in your VBA Macros. There are however many pitfalls in using this function as well as its square brackets equivalent. Here are some:

  • Prone to errors – typical error includes forgetting that without preceding cells with the worksheet name the statement with always reference the Activesheet
  • Slightly slower – if you intend to run your code multiple times you might see a slight performance disadvantage. Usually you won’t notice this at all

In summary I personally think it make sense to know the VBA Application.Evaluate function as sometimes quick and dirty is all you need. However, I would avoid using it in complex VBA macros as it is easy to make a mistake, this comes especially as I am not a fan of embedding static cell references e.g. Range(“A1”) vs Cells(1,1). What is your take?