Category Archives: Excel

excel gantt chart

Excel Gantt Chart Tutorial

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

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.

What is a Gantt Chart?

So what is a Gantt Chart?

gantt chart example
An example Gantt Chart

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.
gantt chart explained
Simple Gantt Chart

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

gantt chart tableCreate an Excel Table with the following columns:

  • Tasklist of tasks to be performed
  • Start Datethe start date of the task
  • End Datethe 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:
gantt end date formula
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).
gantt duration formula
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).

Want to set dates manually and have the Duration (in Work Days) calculated instead? Move to my Gantt Tips & Tricks section below.

Create a 2-D Stacked Bar Excel Chart

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.

Create a 2-D Stacked Bar Excel Chart
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.

  1. Right-click on the chart and select Select Data.
  2. Click the Add and add the Duration column data series and click Ok
  3. 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
  4. Click Ok on the Select Data Source form to close
Add Duration series and Task labels to Excel Chart
Add Duration series and Task labels to Excel Chart

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:

Set Start Date data series Fill to "No Fill"
Set Start Date data series Fill to “No Fill”

Our Gantt Chart should now look like the one below:
Excel Gantt Chart after removing the Start Date data series
Excel Gantt Chart after removing the Start Date data series

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!


Excel Gantt Chart Template overview
Excel Gantt Chart Template overview

excel vba evaluate

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

1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 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:

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:

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:

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?

excel vba colorindex

Excel VBA ColorIndex vs VBA Color property

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

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…
vba colorindex

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.

VBA ColorIndex table
VBA ColorIndex table
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

VBA ColorIndex usage examples
VBA ColorIndex usage examples
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

Summary

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.

vba ftp

VBA Download / Upload File using VBA FTP

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

Today we will learn how to download and upload files using a VBA FTP script. FTP is a great way to keep your Excel files connected to your file servers, back up your files and worksheets automatically or simply download/upload document without any additional manual hassle.

FTP is the abbreviation for File Transfer Protocol and is a standard network protocol used to transfer computer files between a client and server on a computer network.

For more read WIKI here.

VBA FTP Download Files

Let us start with learning how to Download Files from FTP using VBA FTP. The below code defines the procedure FtpDownload which does just that:

Syntax

strRemoteFile
A string path to the file on the remote FTP drive which you want to download e.g. “//home/user/text file.txt”

strLocalFile
A string path to the file on the local drive which you want to save the remote file to e.g. “C:\text file.txt”

strHost
A string with the FTP server name e.g. “192.168.0.100” or “myserver.example.com”.

lngPort
A number specifying the FTP port. 21 by default.

strUser
A string with the FTP user name.

strPass
A string with the FTP user password.

Example

Now let us use the above VBA FTP Download procedure to download a file from our FTP server.

VBA FTP Upload Files

Now let us move to learning how to Upload Files from your local drive to FTP using VBA FTP. The below code defines the procedure FtpUpload which does just that:

Syntax

strLocalFile
A string path to the file on the local drive which you want to upload e.g. “C:\text file.txt”
strRemoteFile
A string path with the name of the upload file on the remote drive to e.g. “//home/user/text file.txt”

strHost
A string with the FTP server name e.g. “192.168.0.100” or “myserver.example.com”.

lngPort
A number specifying the FTP port. 21 by default.

strUser
A string with the FTP user name.

strPass
A string with the FTP user password.

Example

Now let us use the above VBA FTP Download procedure to download a file from our FTP server.

Summary and alternatives

Downloading and Uploading files to an FTP server is a very useful task. Although there are several things to remember…

Do not keep your password directly in your Workbook. Your password should be stored in a secure way and there is always a chance you might share your Excel Workbook without deleting the embedded password. A good tip is to save the password in a separate text file on your local drive to which no one else has access. See Reading Files in VBA to do this.

Do you use FTP for file versioning? Use SVN or a dedicated solution instead! Although saving and versioning files to FTP is one way to go don’t rely on custom basic solution to long known problems. Setup SVN on your server of use a dedicated service like OneDrive, DropBox of Google Drive.

Do you want to download files from web servers / pages not from FTP? See this post on Downloading Files using VBA.

vba vlookup

VBA VLOOKUP – Using VLOOKUP in VBA

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

The VLOOKUP function is probably one of the most dreaded functions by beginner Excel users and also one of the most appreciated by the more advanced. VLOOKUP is used to lookup a row in a certain table, based on a value and return a corresponding value in a certain column with that row.

VLOOKUP is easily used in Excel. Today, however, we will explore how to do lookup operations in VBA (VBA VLOOKUP) or how to VLOOKUP in VBA if you prefer.

VBA VLOOKUP WorksheetFunction

First let us explore the simple approach of using the VBA VLOOKUP WorksheetFunction.

For this example table:
vba vlookup table
A simple VLOOKUP operation for say to lookup Roberts age might look like this:
VLOOKUP in Visual Basic equivalent
What about the same in Visual Basic for Applications (VBA)? The below is the equivalent to the VLOOKUP operation in Excel. We can use the WorksheetFunction object to run a VLOOKUP operation as such:

The result:
vba vlookup result

WorksheetFunction Vlookup function

Syntax

The syntax for the Vlookup function in VBA is:

Parameters

Lookup_value
The lookup value – the value which is searched for within the first column of the provided Table_array parameter

Table_array
A VBA Range variable. A table of at least 2 columns or more. The first column is the lookup column.

Col_index_num
The number of the column in the Table_array table VBA Range from which the matching value must be returned.

Range_lookup
An optional parameter. A logical parameter (True or False) that specifies whether you want the VLookup method to find an exact match or an approximate match. True – approximate match, False – exact match.

VBA VLOOKUP Dictionary

Another approach is to use the VBA Dictionary Object. For the same table as above we can use the following code:

What happens above? First we loop through all the rows and create a VBA Dictionary object with a Key-Value pair of all rows. The Dictionary contains only the lookup and matching columns. Next we lookup our desired Key and return the matching Value.

The result:
vba vlookup result
Right. So if both ways provide the same result what is the benefit of building a Dictionary upfront? Well, imagine wanting to lookup multiple values within your VBA code. In the first approach you would have to reintroduce the entire arguments of the Vlookup function. In the second you can run additional simple calls:

The Dictionary approach will in this case prove MUCH FASTER. This is because the Dictionary is built once and then just reused to lookup a certain key as opposed to doing the entire lookup operation on the entire table as a Vlookup operation would do.

If you want to learn more on the most effective way to lookup values in Excel instead, read my article on VLOOKUP vs INDEX MATCH vs SQL vs VBA.