Tag Archives: macro

excel vba evaluate

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

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

vba last row featured

Excel VBA Last Row, Last Column, Last Cell

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

Today I am going to take on one of the most frequent question people ask about Excel VBA – how to the the last row, column or cell of a spreadsheet using VBA. The Worksheet range used by Excel is not often the same as the Excel last row and column with values. Therefore I will be careful to explain the differences and nuisances in our quest to find the last row, column or cell in and Excel spreadsheet.

CONTENTS

VBA Last Row

The Last Row may as be interpreted as:

Last Row in a Column

vba last row
Last Row in a Column

To get the Last Row with data in a Column we need to use the End property of an Excel VBA Range.

Dim lastRow as Range

'Get Last Row with Data in Column
Debug.Print Range("A1").End(xlDown).Row 'Result: 5
Set lastRow = Range("A1").End(xlDown).EntireRow

'Get Last Cell with Data in Row
Dim lastRow as Range
Set lastRow = Range("A1").End(xlDown)

Last Row with Data in Worksheet

vba last used row
Last Used Row with Data in Worksheet

To get the Last Row with data in a Worksheet we need to use the SpecialCells or Find properties of an Excel VBA Range.

Dim lastRow as Range, ws As Worksheet
Set ws = ActiveSheet

'Get Last Row with Data in Worksheet using SpecialCells
Debug.Print ws.Cells.SpecialCells(xlCellTypeLastCell).Row
Set lastRow = ws.Cells.SpecialCells(xlCellTypeLastCell).EntireRow 

'Get Last Row with Data in Worksheet using Find
Debug.Print Debug.Print ws.Cells.Find(What:="*", _
        After:=ws.Cells(1), _
        Lookat:=xlPart, _
        LookIn:=xlFormulas, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False).Row
Set lastRow = Debug.Print ws.Cells.Find(What:="*", _
        After:=ws.Cells(1), _
        Lookat:=xlPart, _
        LookIn:=xlFormulas, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False).EntireRow

Last Row in Worksheet UsedRange

vba last used row usedrange
Last Used Row in Worksheet UsedRange

To get the Last Row in the Worksheet UsedRange we need to use the UsedRange property of an VBA Worksheet.

'Get Last Row in Worksheet UsedRange
Dim lastRow as Range, ws As Worksheet
Set ws = ActiveSheet
Debug.Print ws.UsedRange.Rows(ws.UsedRange.Rows.Count).Row
Set lastRow = ws.UsedRange.Rows(ws.UsedRange.Rows.Count).EntireRow
The UsedRange represents a Range used by an Excel Worksheet. The Used Range starts at the first used cell and ends with the most right, down cell that is used by Excel. This last cell does not need to have any values or formulas as long as it was edited or formatted in any point in time

VBA Last Column

The Last Column may as be interpreted as:

Last Column with Data in a Row

vba last column
Last Column in Row

To get the Last Column with data in a Row we need to use the End property of an Excel VBA Range.

Dim lastColumn as Range

'Get Last Column with Data in Row
Debug.Print Range("A1").End(xlRight).Row 'Result: 5
Set lastColumn = Range("A1").End(xlRight).EntireColumn

'Get Last Cell with Data in Column
Dim lastColumn as Range
Set lastColumn = Range("A1").End(xlRight)

Last Column with Data in Worksheet

vba last used column
Last Column with Data in a Worksheet

To get the Last Column with data in a Worksheet we need to use the SpecialCells or Find properties of an Excel VBA Range.

Dim lastColumn as Range, ws As Worksheet
Set ws = ActiveSheet

'Get Last Column with Data in Worksheet using SpecialCells
Debug.Print ws.Cells.SpecialCells(xlCellTypeLastCell).Column
Set lastColumn = ws.Cells.SpecialCells(xlCellTypeLastCell).EntireColumn 

'Get Last Column with Data in Worksheet using Find
Debug.Print Debug.Print ws.Cells.Find(What:="*", _
        After:=ws.Cells(1), _
        Lookat:=xlPart, _
        LookIn:=xlFormulas, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False).Column
Set lastColumn = Debug.Print ws.Cells.Find(What:="*", _
        After:=ws.Cells(1), _
        Lookat:=xlPart, _
        LookIn:=xlFormulas, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False).EntireColumn

Last Column in Worksheet UsedRange

vba last used column usedrange
Last Used Column in Worksheet UsedRange

To get the Last Column in the Worksheet UsedRange we need to use the UsedRange property of an VBA Worksheet.

'Get Last Column in Worksheet UsedRange
Dim lastColumn as Range, ws As Worksheet
Set ws = ActiveSheet
Debug.Print ws.UsedRange.Columns(ws.UsedRange.Columns.Count).Column
Set lastColumn = ws.UsedRange.Columns(ws.UsedRange.Columns.Count).EntireColumn
The UsedRange represents a Range used by an Excel Worksheet. The Used Range starts at the first used cell and ends with the most right, down cell that is used by Excel. This last cell does not need to have any values or formulas as long as it was edited or formatted in any point in time

VBA Last Cell

The Last Cell may as be interpreted as:

Last Cell in a series of data

vba last cell in data series
Last Cell in data series

To get the Last Cell in a series of data (table with non-blank values) we need to use the End property of an Excel VBA Range.

Dim lastCell as Range

'Get Last Cell in a series of data
Dim lastCell as Range
Set lastCell = Range("A1").End(xlRight).End(xlDown)
Debug.Print "Row: " & lastCell.row & ", Column: " & lastCell.column

Last Cells with Data in Worksheet

vba last cell in worksheet
Last Cell with data in Worksheet

To get the Last Cell with data in a Worksheet we need to use the SpecialCells or Find properties of an Excel VBA Range.

Dim lastCell as Range, ws As Worksheet
Set ws = ActiveSheet

'Get Last Cell with Data in Worksheet using SpecialCells
Set lastCell = ws.Cells.SpecialCells(xlCellTypeLastCell)
Debug.Print "Row: " & lastCell.row & ", Column: " & lastCell.column

'Get Last Cell with Data in Worksheet using Find
Set lastColumn = Debug.Print ws.Cells.Find(What:="*", _
        After:=ws.Cells(1), _
        Lookat:=xlPart, _
        LookIn:=xlFormulas, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False)
Debug.Print "Row: " & lastCell.row & ", Column: " & lastCell.column

Last Cell in Worksheet UsedRange

vba last cell in usedrange
Last Cell in UsedRange

To get the Last Cell in the Worksheet UsedRange we need to use the UsedRange property of an VBA Worksheet.

'Get Last Cell in Worksheet UsedRange
Dim lastCell as Range, ws As Worksheet
Set ws = ActiveSheet

Set lastCell = ws.UsedRange.Cells(ws.UsedRange.Rows.Count,ws.UsedRange.Columns.Count)
Debug.Print "Row: " & lastCell.row & ", Column: " & lastCell.column
The UsedRange represents a Range used by an Excel Worksheet. The Used Range starts at the first used cell and ends with the most right, down cell that is used by Excel. This last cell does not need to have any values or formulas as long as it was edited or formatted in any point in time

VBA UsedRange

vba usedrange
The UsedRange in a Worksheet with not Values or Formulas

The VBA UsedRange represents the area reserved and saved by Excel as the currently used Range on and Excel Worksheet. The UsedRange constantly expands the moment you modify in any way a cell outside of the previously Used Range of your Worksheet.

The UsedRange is not reduced if you Clear the Contents of Range. The only way to reduce a UsedRange is to delete the unused rows and columns.

How to check the UsedRange

The easiest way to check the currently UsedRange in an Excel Worksheet is to select a cell (best A1) and hitting the following key combination: CTRL+SHIFT+END. The highlighted Range starts at the cell you selected and ends with the last cell in the current UsedRange.

Often bloated, large Excel files are effects of misuse of UsedRange. If you want to know more about reducing Excel file size read my post

Check UsedRange in VBA

Use the code below to check the area of the UsedRange in VBA:

Dim lastCell As Range, firstCell As Range, ws As Worksheet
Set ws = ActiveSheet
    
Set lastCell = ws.UsedRange.Cells(ws.UsedRange.Rows.Count, ws.UsedRange.Columns.Count)
Set firstCell = ws.UsedRange.Cells(1, 1)
Debug.Print "First Cell in UsedRange. Row: " & firstCell.Row & ", Column: " & firstCell.Column
Debug.Print "Last Cell in UsedRange. Row: " & lastCell.Row & ", Column: " & lastCell.Column    

For the screen above the result will be:

First Cell in UsedRange; Row: 2, Column: 2
Last Cell in UsedRange; Row: 5, Column: 6

First UsedCell in UsedRange

The below will return get the first cell of the VBA UsedRange and print its row and column:

Dim firstCell as Range
Set firstCell = ws.UsedRange.Cells(1, 1)
Debug.Print "First Cell in UsedRange. Row: " & firstCell.Row & ", Column: " & firstCell.Column

Last UsedCell in UsedRange

The below will return get the first cell of the VBA UsedRange and print its row and column:

Dim lastCell as Range
Set lastCell = ws.UsedRange.Cells(ws.UsedRange.Rows.Count, ws.UsedRange.Columns.Count)
Debug.Print "Last Cell in UsedRange; Row: " & lastCell.Row & ", Column: " & lastCell.Column
excel vba worksheets tutorial

Excel VBA Worksheets VBA Activesheet vs Worksheets, Select, Activate etc.

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

Today we are going to learn about VBA Worksheets. We will cover all the nuisances e.g. VBA Activesheet and how it compares to regular Worksheets, how to Select Worksheets, how to Activate Worksheets, Selecting vs Activating Worksheets… and everything else you need to know about the VBA Worksheet in general.

ThisWorkbook vs ActiveWorkbook

Some Excel Worksheets
Some Excel Worksheets
Lets start with the basics. Before we start I want to stress and remind the difference between ActiveWorkbooks and ThisWorksbooks. In short:

  • ThisWorkbook – refers to the Workbook in which the VBA macro is running
  • ActiveWorkbook – refers to the Workbook which is in the topmost Excel Window

It is extra important to understand this difference and I encourage you to read my post on this topic first.

The Excel VBA Object Hierarchy

Excel vba object hierarchySecondly it makes sense to remind the Excel Object hierarchy.

At the top, at the root we have our Excel Application. The Excel Application represents the entire Excel process. Further down the tree we have our Workbooks. Each Application contains a collection of Workbooks. Looking into a single Workbook we will notice it contains a collection of Worksheets. Worksheets on the other hand as you know can define Ranges (not the same a single cells). Using a Range we can access its cells Values or Formulas.

Accessing VBA Worksheets

Now that we have that behind us lets explore the different ways in which we can access Worksheets in VBA:

ActiveWorkbook VBA Worksheets

The Sheets and Worksheets collections

Sheets within the ActiveWorkbook:

Dim ws as Worksheet, wsCollection as Sheets
Set wsCollection = Sheets 'Get entire collection of Worksheets
Set ws = Sheets(1) 'Get first Worksheet in ActiveWorkbook
Set ws = Sheets("Sheet1") 'Get Worksheet named "Sheet1" in ActiveWorkbook

Similarly we can use Worksheets instead of Sheets.

Dim ws as Worksheet, wsCollection as Sheets
Set wsCollection = Worksheets 'Get entire collection of Worksheets
Set ws = Worksheets(1) 'Get first Worksheet in ActiveWorkbook
Set ws = Worksheets("Sheet1") 'Get Worksheet named "Sheet1" in ActiveWorkbook

ThisWorkbook VBA Worksheets

The Sheets and Worksheets collections

Sheets within the ThisWorkbook:

Dim ws as Worksheet, wsCollection as Sheets
Set wsCollection = Sheets 'Get entire collection of Worksheets
Set ws = ThisWorkbook.Sheets(1) 'Get first Worksheet in ThisWorkbook
Set ws = ThisWorkbook.Sheets("Sheet1") 'Get Worksheet named "Sheet1" in ThisWorkbook

Similarly we can use Worksheets instead of Sheets.

Dim ws as Worksheet, wsCollection as Sheets
Set wsCollection = Worksheets 'Get entire collection of Worksheets
Set ws = ThisWorkbook.Worksheets(1) 'Get first Worksheet in ActiveWorkbook
Set ws = ThisWorkbook.Worksheets("Sheet1") 'Get Worksheet named "Sheet1" in ThisWorkbook

Worksheet VBA Name vs Excel Name

caption id=”attachment_11373″ align=”alignright” width=”213″]Worksheet VBA Name vs Excel Name Worksheet VBA Name vs Excel Name[/caption]When speaking about Worksheets in ThisWorkbook, the VBA Name of a Worksheet is not the same as the Excel Name of a Worksheet. Let us understand this different. On the right we have a screen from an example Workbook. The VBAName string is the VBA Name of our Worksheet, on the other hand the Excel Name string is our Excel Name. You can use both names to refer to the same Worksheet but in different ways.

Using the VBA Name of a Worksheet

Worksheet VBA Name
Worksheet VBA Name
We can refer to a VBA Worksheet by its VBA Name directly – just by typing it. This is very convenient and a good practice. That is because the VBA Name can’t be changed by a user by mistake from the level of Excel (not the VBE). Hence whatever name the user give from the Workbook level to your Worksheet – its VBA Name stays the same.

Using the Excel Name of a Worksheet

Referring to an Excel Worksheet from VBA is not a recommended practice. Below I am referring to the Worksheet I named Excel Name in my example above.

Dim ws as Worksheet

Set ws = Worksheets("Excel Name") 'Get Worksheet named "Sheet1" in ActiveWorkbook

Not what you notice is that compared to acquiring the Worksheet by its VBA Name, when using the defaults Worksheets or Sheets object you land with the said Worsheet, but from the ActiveWorkbook. Usually this doesn’t do much of a difference. But I am highlighting that this is simply another place to make a common mistake. See both versions below:

Dim ws as Worksheet

'---Sheet by Excel Name in ActiveWorkbook---
Set ws = Worksheets("Excel Name") 'Get Worksheet named "Sheet1" in ActiveWorkbook
Set ws = ActiveWorkbook.Worksheets("Excel Name") 'Get Worksheet named "Sheet1" in ActiveWorkbook

'---Sheet by Excel Name in ThisWorkbook---
Set ws = ThisWorkbook.Worksheets("Excel Name") 'Get Worksheet named "Sheet1" in ActiveWorkbook

The VBA ActiveSheet

As with Active vs ThisWorkbook you need to first understand the difference between Selecting a Worksheet and Activating it.

Selected vs Activated Worksheet, the differences:

  • Selected Worksheet – one or more Worksheets that have been selected within an Excel Window. Each Workbook has its own set of Selected Worksheets
  • ActiveWorksheet – the current Worksheet you are viewing and working in. The only top-most Worksheet in all Application Workbooks

From the definition above you see that there can be more than 1 Selected Worksheet, while only 1 ActiveSheet. In fact both are totally different things. Lets explore this in some examples:

Example 1: Explaining ActiveSheet

vba worksheets exampleLet us say we have 2 Workbooks open. One is Book1.xlsm and there other is Book2.xlsm.

Lets open VBE in Book1.xlsm. We will add the following piece of code:

Sub TestActiveSheet()
 MsgBox ActiveSheet.Name
End Sub

Now lets activate Book2.xlsm. Rename any Worksheet e.g. to MyActiveWorksheet. Next go to the Developer ribbon select Macros and run Books1.xlsm!TestActiveSheet. What happened? You probably got something like this:
vba activesheet
Although you ran a macro from Book1.xlsm, VBA considers always the top-most worksheet as the ActiveSheet.

Example 2: Explaining Selected vs ActiveSheet

Let us consider the same example. Open Book1.xlsm and add the following code to the VBE:

Sub SelectedVSActive()
  Dim res As String, ws As Worksheet
  res = "Selected sheets:" & vbNewLine
  For Each ws In ActiveWindow.SelectedSheets
    res = res & ws.Name & vbNewLine
  Next ws
  MsgBox res & vbNewLine & "ActiveSheet:" & vbNewLine & ActiveSheet.Name
End Sub

Now do the following:

  • Activate workbook Book2.xlsm
  • Select a number of Worksheets (at least 2)
  • Run Book1.xlsm!SelectedVSActive macro

What happens? You should get something like this:
vba activesheet vs selected
You see that:

  • Only the top-most Worksheet is considered as the ActiveSheet
  • The ActiveSheet is ALWAYS considered as a Selected Worksheet within the ActiveWindow (this last part is important)

What happens if you change ActiveWindow to e.g. Windows(2)? You will see a different set of Selected Worksheets. Why? Because all Workbooks have Selected Worksheets. Hope you see the difference.

Activating VBA Worksheets

Activating Worksheet is easy in VBA. You do it using the Activate property of Worksheets. How to activate any Worksheet using VBA?

Dim ws as Worksheet
'...Set ws to some Worksheet
ws.Activate

Activate Worksheet examples

A few examples below:

Sheets(1).Activate 'Activate first Worksheet in ActiveWorkbook
Sheet1.Activate 'Activate Sheet1 in ThisWorkbook
Worksheets("MyNamedWorksheet").Activate 'Activate Excel named Worksheet in ActiveWorkbook

Selecting VBA Worksheets

Selecting Worksheets is a little more difficult as we may want to select a single Worksheet or more.

Selecting a single VBA Worksheet

Selecting a single VBA Worksheet is simple. Just use the Select property of a Worksheet.

Dim ws as Worksheet
'...Set ws to some Worksheet
ws.Select

Selecting a single Worksheet examples

A few examples below:

Sheets(1).Select 'Select first Worksheet in ActiveWorkbook
Sheet1.Select 'Select Sheet1 in ThisWorkbook
Worksheets("MyNamedWorksheet").Select 'Select Excel named Worksheet in ActiveWorkbook

Selecting multiple VBA Worksheets

Selecting more than 1 Worksheet is a little more complex as we need to leverage the VBA Array function:

Sheets(Array(1, 2)).Select 'Select first and second Worksheet in ActiveWorkbook
Sheets(Array("Named1", "Named2")).Select 'Select 2 named Worksheets in ActiveWorkbook

This is not the most elegant way of doing it. Fortunately the Select function has an argument called Replace. Setting it to false will mean that the previously selected is not to be deselected. Thus we can extend our selections like this:

Sheets(1).Select 
Call Sheets(2).Select(False)
Call Sheets(3).Select(False)

This will select the first three Worksheets in our ActiveWorkbook.

Deleting VBA Worksheets

To delete a VBA Worksheet we need to turn off the Application.DisplayAlerts

Application.DisplayAlerts = False
Sheets(1).Delete
Application.DisplayAlerts = True

You can delete multiple Worksheets in a similar fashion as how you can do a multiple Worksheet Select:

Application.DisplayAlerts = False
Sheets(Array(1, 2)).Delete
Application.DisplayAlerts = True

Copying VBA Worksheets

You can copy Worksheets within a Workbook or from other open Workbooks.

'Copy Sheet1 and paste before Sheet2
Worksheets("Sheet1").Copy Before:=Sheets("Sheet2")

'Copy Sheet1 and paste before Sheet2 in Workbook Book1.xlsm
Worksheets("Sheet1").Copy Before:=Workbooks("Book1.xlsm").Sheets("Sheet2")

Moving VBA Worksheets

You can move Worksheets within a Workbook or from other open Workbooks.

'Move Sheet1 and paste before Sheet2
Worksheets("Sheet1").Move Before:=Sheets("Sheet2")

'Move Sheet1 and paste before Sheet2 in Workbook Book1.xlsm
Worksheets("Sheet1").Move Before:=Workbooks("Book1.xlsm").Sheets("Sheet2")

Other VBA Worksheets properties

Below are other VBA Worksheet properties worth mentioning:

Worksheet property Description Example
Calculate Calculates all dirty formulas on the current Worksheet
Dim ws as Worksheet
'...Set ws
ws.Calculate
Name Get or set the Excel Name of the Worksheet
Dim ws as Worksheet
'...Set ws
Debug.Print ws.Name 'Print Excel Name of Worksheet
ws.Name = "New name" 'Set new name for Worksheet
Visible Display, Hide or make Worksheet VeryHidden. Possible values:

  • xlSheetVisible
  • xlSheetVeryHidden
  • xlSheetHidden
Dim ws as Worksheet
'...Set ws
ws.Visible = xlSheetHidden 'Make ws Worksheet Hidden
visual basic for applications

Do you really need Visual Basic for Applications macro for that?

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

Visual Basic for Applications (abbrv. VBA) is often misused causing issues with maintaining code, backward compatibility with older Excel version and other issues. Reason being is often that some of us don’t know, or better still, don’t make the minimal effort to learn ready available tools in Excel or other Office applications. From time to time I see some Excel macro that does the job of a Excel PivotTable or more often that of a simple MS Query (SQL). So I am dedicating today’s post to those struggling with their doubts on whether to open up the VBE (Visual Basic Editor).

More often than not, Excel users tend to create macros in situations where they could easily leverage an Excel formula, PivotTable, AddIn or other ready solution instead of writing VBA Code. So let us ask ourselves today:

Should I use a Visual Basic for Applications macro for that?

Do your research

Google your problem!
Google your problem!
What is the reason you consider resorting to VBA? Did you really do your research and you are sure there are no native Excel functions that will do the job? Too often I see people using VBA Macros because they didn’t do a 10 min Google search to see if there are Excel formulas that will do the job.

Does Excel have a built in tool for that?

Excel is the best entry level Data Analysis Tool out there. Are you sure you know all the tools natively available in Excel? Check the Data ribbon tab first!

None of them fit the issue?

Can you use an Array Formulas (CTRL+SHIFT+ENTER)?

Often what we need is to create a complex formula that will run on an entire array of cells or rows, not just a single cell.
Are you aware of Array Formulas? They allow you to do operations on whole arrays (rows/columns) of Excel data. A simple example below to demonstrate the power of Array Formulas:

The Table

A
2
5
11
12

The Array Formula (type and hit CTRL+SHIFT+ENTER):

=COUNT(IF(A1:A10 > 10;A1:A10))

The result (number of cells with value above 10):

2

As you can see the formula above can easily count all cells between A1:A10 of value higher that 1. Nice right? That is just the peak of the iceberg of what Array Formulas can do. Check out this Array Formula tutorial to learn more.

Is there an App or AddIn for that?

I know some of us like to be creative and look to code stuff their selves. However, I do encourage that you check for existing solutions:

No alternatives – need to use VBA

Ok so there doesn’t seem to be any alternative to writing a VBA macro. Where to start before you begin?

Need help / support

If you stumble across issues when developing your VBA code, feel free to post your questions (do some research first!) on one of these forums:

  • StackOverflow – in my opinion the best technical forum out there
  • AnalystCave.com forums – got a question to one of my posts or tools? Feel free to reach out!
  • MSDN Excel forum – the official Excel forum by Microsoft
  • MSDN VBA forum – the official VBA forum by Microsoft
  • MrExcel forum – no longer the power house it used to be, but still hosts a bunch of Excel users that share their knowledge and can help
Words of advice when posting questions: most forums have a policy to ignore (at best) questions which don’t show that you made any effort to resolve the problem yourself. Therefore when posting a question remember to follow the above steps or at least:

  • Google your question
  • Search the Forum database for similar questions.. you would be surprised how many people had the same problem as you did
  • Include a summary of your research in the question
  • Include your sourcecode (line that throws error even better) or be sure to explain the issue in detail

On the other hand don’t:

  • Post questions showing negligence to details and quality – lots of typos, misuse of formatting etc.
  • Write general statements that nothing works or post questions with general titles like – Excel Problem or VBA Issue
  • Demand that someone write the code for you (really, it happens)

Why am I discouraging the use of Visual Basic for Applications?

Don’t get me wrong – my blog is ALL about VBA. It is not that I discourage the use of VBA, in many cases you won’t have an alternative. It is just that in so many cases we are incorrectly lazy enough to avoid doing proper research that we actually spend more time writing unnecessary VBA scripts that copy often built in Excel capabilities. So many enterprises rely on VBA scripts that often do nothing more than a simple Pivot Table, Array Formula or PowerQuery/MS Query could do in seconds.

So seriously Help Yourself and do your research first. You will appreciate VBA when you will really need it.

vba option explicit

VBA Option Explicit – explicit variable declaration

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

The VBA Option Explicit statement is such useful feature in Excel VBA, it is strange, this is the first time I am writing a post about it. It proves incredibly useful in preventing typos and other stupid mistakes when developing Visual Basic for Applications macros. But what does it actually do?

What does Option Explicit do?

The VBA Option Explicit statement can either:

  • Forces an explicit declaration of all variables in a VBA Module
  • Allow the implicit declaration of variables in VBA Modules

However, the first question most people have is…

What is explicit and implicit variable declaration?

Explicit variable declaration in VBA is the declaration of a variable with the use of the VBA Dim statement:

Dim x as Long 'Explicit declaration
x = 1

Implicit variable declaration on the other hand is the opposite – the usage of a variable without any preceding declaration. The VBA Compiler will create a variable that has been used even if it was not preceded with an explicit declaration:

x = 1 'Implicit declaration

Option Explicit usage

Now that we know what implicit and explicit declarations we can ponder on a second on why we would want to force explicit variable declaration. Well, for a number of reasons:

  • To prevent variable name typos
  • Clean up VBA Code
  • Optimize VBA performance
By default all implicit variables are Variant types. Variants are much less efficient than other data types (Longs, Integers, Strings etc.). Read more on VBA Performance here

Using Option Explicit

Option Explicit 'Declare that all variables are to be explicitly declared

Sub Main
 Dim x as Long
 x = 1 'OK!
 y = 10 'ERROR!
End Sub

option explicit errorWhat happens when you try to declare a variable implicitly. Believe me, it is better to fix Variable not defined errors than scramble to find variable name typos.

Turn on Option Explicit by default

For me the Option Explicit statement is a must requirement. I turn it on by default in all my macros. Even when using Variant variables.

turn on option explicitHow to turn on Option Explicit by default for all VBA Code Modules? Go to Tools->Options.... In the Options window select Require variable declaration. That is it! Similarly you can turn off the addition of the Option Explicit statement in all your VBA Code Modules by unchecking this option.