Tom. Excel / VBA / C# enthusiast and hobbist. Collecting and sharing my knowledge and experience with beginner/advanced analysts and VBA developers. My posts are written with one thing in mind: teaching analysts how to do things properly.
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:
Click Ok. You should now see a new tab in your Excel ribbon called Developer as shown below:
Click the Visual Basic button on the Developer ribbon
Click the button as shown below:
This will open the Visual Basic editor as shown below:
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.
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).
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:
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!
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
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?
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.
One way is to use the almighty Microsoft Snipping Tool which is great for creating image snapshots and saving them as images (PNG files).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
Copy the selected Excel Range and paste it as a Picture
Copy the Picture to an empty Chart
Ask for the destination file name
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
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.
All that is left is to select the VBA Macro from the Macro window and to hit Run.
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.
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).
Straying from usual VBA topics let us explore today another tool that can be easily created in Excel. Who said you need MS Project to play with Gantt Charts? Today we will explore the Gantt Chart and answer questions like “How to create a gantt chart in excel?”. You will find a useful Excel Gantt Chart Template that automatically regenerates using VBA.
A Gantt Chart is a chart that visualizes the amount of work done or planned with a series of horizontal lines (or rectangles). Gantt Charts are useful in managing projects, planning production and resource management. The Gantt Chart is easily one of the basic tools used in Project Management.
Above you can see a very simple Gantt chart. In its most simple form a Gantt Chart consists of a list of Tasks with Start and End Dates.
From experience I can tell that you rarely need more than this to manage the everyday progress of your projects schedule.
Modern Gantt Charts (e.g. created in MS Project) consist of many more elements such as Dependencies, Milestones, Assigned roles and much more.
Excel Gantt Chart Tutorial
Now that you know what a Gantt Chart is let us explore a simple way to create a Gantt Chart in Excel:
Create an Excel Table
Create an Excel Table with the following columns:
Task– list of tasks to be performed
Start Date – the start date of the task
End Date – the end date of the task (last date when task is to be performed)
Duration (Calendar Days) – the amount of calendar days between the start and end dates
Duration (Work Days) – the amount of work days between the start and end date
The Excel Table contains 2 formulas:
The formula above calculates the End Date of the task based on the duration provided in Work Days.
This formula uses the WORKDAYS function which returns a date (End Date) by adding a specified number of Work Days to a certain date (Start Date).
The formula above calculates the amount of Calendar Days between the Start Date of the Task and the End Date automatically calculated earlier based on the amount of Work Days.
The approach above assumes End Dates are calculated based on the Duration (in Work Days of the activity).
Now that we have our source table we can use it to create an Excel Gantt Chart. Select the Start Date column and create a 2-D Stacked Bar Excel chart.
Add Duration series and Task labels to Gantt Chart
Now we need to add the Duration data series to the Chart as well as add Tasks as labels on our vertical axis.
Right-click on the chart and select Select Data.
Click the Add and add the Duration column data series and click Ok
Now click the Edit button on the right and in Axis label range select the tasks in the Tasks column of your table and click Ok
Click Ok on the Select Data Source form to close
Hide Start Date bar on Gantt Chart
The charts above don’t resemble an actual Gantt Chart yet. What is still bothering is the blue bar on the left. To hide this bar right-click and select Format Data Series. Next go to the Fill section and select the No Fill radio button as shown below:
Our Gantt Chart should now look like the one below:
Again one thing bothers us – the Tasks are in the incorrect order!
Reverse Tasks vertical axis order
The last thing left for us to do is reverse the order of the Tasks on our vertical axis. To do this right-click on the Tasks vertical axis and select Format Axis next in Axis Options select the Categories in reverse order radio button.
That is it! You have now a wonderful and simple Gantt Chart that will refresh automatically based on your Excel Table.
Gantt Chart Template
Don’t have time to create your own Gantt Chart from scratch? Get it now and support new great posts from AnalystCave.com!
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.
The Excel VBA Evaluate function converts a Microsoft Excel Name to an object or a value.
The syntax for the Evaluate function in VBA is:
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.
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
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:
Tip 1: Use brackets instead
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.
Tip 2: Using brackets instead of Range
You can easily replace a VBA Range with square brackets as well:
'This is the same...
'This is the same...
Combining Tip 1 and 2 you can seriously shorten your VBA code:
Tip 3: Simple array definition
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:
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?
There are two ways of setting colors of Cell Backgrounds, Fonts or Borders in Excel. One of them is the VBA ColorIndex property and the other of them is the VBA Color property. But what is the difference between the two, and which is better! So let us explore…
The VBA ColorIndex property
The VBA ColorIndex property can be any value between 1 and 56, and represents a color defined within your currently selected color theme with the provided index.
As you can see in the image on the left this represents a variety of colors. The downside of using the ColorIndex is that it is dependent on the actually selected color theme and you are limited to selecting colors from the 56 color palette.
If you want to print a similar ColorIndex table simply use the code below in any selected worksheet:
VBA ColorIndex example
On the left you can see several examples of setting the VBA ColorIndex to different Excel Range properties such as Interior (background), Font and Borders.
The VBA Color property
The VBA Color property is a more convenient way of defining the color of a Cell Interior, Border or Font. The Excel VBA Color property accepts Long values representing a RGB color. The easiest way to set this property is to use the VBA RGB function:
Example: Setting cell background to red
Example: Setting cell font to blue
Example: Setting cell borders to yellow
Using colors is easily achieved in Excel VBA. The Excel VBA ColorIndex property limits you however to 56 theme or default Excel colors and is rarely used in practice. The Excel VBA Color on the other side allows you to set any color to any Excel Cell property using the VBA RGB function.