Sat 12 Dec 2015

Tips & Tricks 122 - Always Open a Specific Worksheet when Workbook is Opened

By |Saturday, December 12th, 2015|Categories: Tips and Tricks, VBA|Tags: , , , , , |0 Comments

This can only be accomplished through VBA.

1. Save your file as .xlsm
2. ALT+F11
3. Locate your Workbook name in Project Explorer Window
4. Double click on ThisWorkbook
5. Put following code in ThisWorkbook (Replace Sheet Name appropriately)

 

Sat 12 Dec 2015

Tips & Tricks 121 - Macro to Protect / Un-protect All or Selective Sheets

By |Saturday, December 12th, 2015|Categories: Tips and Tricks, VBA|Tags: , , , , , |0 Comments

There may be scenarios where you want the facility to protect or unprotect all sheets in one go with added option to choose sheets where to perform this operation. Below is the code to perform this.

1. Save your file as .xlsm
2. ALT+F11
3. Locate your Workbook name in Project Explorer Window
4. Right click on your workbook name > Insert > Module
5. Copy paste the below code in this
6. ALT+F8 to display Macro Window
7. Run your Macro from here

'******* Protect / Un-protect Sheets *******

 

Sat 21 Nov 2015

Article 30 - VBA - Approaches for Unique Count and Time Performance Results for the Same

By |Saturday, November 21st, 2015|Categories: Articles, VBA|0 Comments

As part of this article, we will look into various approaches for counting unique in VBA and also see what time do they take to determine the best approach on the basis of "Time Taken". I have used Charles William's MicroTimer for timing the time.

https://msdn.microsoft.com/en-us/library/aa730921%28v=office.12%29.aspx

We will see performance of these approaches with following number of records

- 100000
- 50000
- 33000
- 10000
- 1000
- 100

(more…)

Sat 14 Nov 2015

Tips & Tricks 117 - VBA - How to Count a particular character in a String

By |Saturday, November 14th, 2015|Categories: Tips and Tricks, VBA|Tags: , , , , |0 Comments

When we use formulas inside Excel, we have following stock formula to count the number of times a character appears in a string -

=LEN(A1)-LEN(SUBSTITUTE(LOWER(A1),"a",""))

In this, we are trying to count the occurrence of character "a" in A1. Let's assume that A1 = "Abraham Arthurway". Hence, the answer which we would get will be 5.

To count the same in a range of cells -

=SUMPRODUCT(LEN(A1:A10)-LEN(SUBSTITUTE(LOWER(A1:A10),"a","")))

Now, how to do the same thing in VBA. In VBA, you can use following expression to count this -

UBound(Split(LCase(Range("A1")), "a"))

To count the same in a range of cells -

UBound(Split(LCase(Join(WorksheetFunction.Transpose(Range("A1:A10")))), "a"))

Sat 07 Nov 2015

Article 29 - Traffic Lights Conditional Formatting for Project Status for Text R / A / G

By |Saturday, November 07th, 2015|Categories: Articles, VBA|Tags: , , , , , , , , |1 Comment

We all know that Traffic Lights are great visuals to communicate Project Status. But, we also know that Conditional Formatting for Traffic Lights can be done only through Numbers not through Text.

We need a flexible system that if I enter R (for Red), A (for Amber) and G (for Green), the required Traffic Light should be displayed. (A for Amber can be substituted with Y for Yellow also). Below is a method to achieve the required Traffic Lights.

(more…)

Sun 25 Oct 2015

Article 28 - How to Unhide all Tabs (Worksheets)

By |Sunday, October 25th, 2015|Categories: Articles, VBA|Tags: , , , , , , , , |0 Comments

If you have many hidden worksheets and you want to hide them in one go, Excel doesn't provide an options to do so. There are many ways to to accomplish the same.

Option 1 - Use Custom Views

1. Unhide all of your sheets (you may use Option 2 or Option 3 below for the same also)
2. Views tab > Custom Views > Add and give this view a name (for example "ShowAll")

(more…)

Sat 10 Oct 2015

Article 27 - Remove Leading Apostrophe

By |Saturday, October 10th, 2015|Categories: Articles, VBA|Tags: , , , , , |0 Comments

Leading apostrophe is one of the widely used features of Excel. This is mostly used to make a cell treat its content as a character. It can also come through import of .csv files from various third party systems or data entries can be made with leading apostrophes. If you enter a number of more than 15 significant digits, Excel will immediately convert this to scientific notation. But there are business situations where you deal with numbers larger than 15 digits like credit card numbers which may be 16 digits. Now if you input credit card numbers as numeric, you will not be able to capture all 16 digits, so a solution is to enter them with leading apostrophe. It forces the cell to treat the entry as character and in character format you can show these 16 digits.(You can enter 32,767 characters in a cell though only 1024 characters will be displayed)

(more…)

Sat 03 Oct 2015

Tips & Tricks 111 - Extract URLs from Hyperlinks

By |Saturday, October 03rd, 2015|Categories: Tips and Tricks, VBA|Tags: , , , , , |0 Comments

In Excel, sometimes you encounters words which are actually Hyperlinks. Say a cell contains a word Microsoft and you notice that this is in Blue and when you click it, it takes you to http://www.microsoft.com. Another cell contains, the word Latest Yahoo Movie Blockbuster and when you click it, it takes you to http://www.yahoo.com/movies#LatestBlockbuster

Now, how to extract this. If there are very few entries like this, you can simply right click the cell > Edit Hyperlink > Copy the URL

But, you have a bunch of entries like this and you need to extract the URLs. For this, the only method is to use a simple piece of code. Don't be frighten even if you have never touched VBA before. Simply follow the following steps -

1. ALT+F11 or Right Click on tab name > View Code to open VBA window.
2. Now locate your workbook on the left side in Project Explorer window though it should be visible by default.
3. Right click on your Workbook Name in Project Explorer window > Insert Module
4. Double click on the module inserted and copy and paste following code

5. Now, in your sheet you can use GetURL as a function like a regular function. If your Hyperlink is in A1, you can write =GetURL(A1) to extract

Credit - http://blog.contextures.com/archives/2010/12/13/get-the-url-from-an-excel-hyperlink/

Sat 26 Sep 2015

Article 26 - Converting Amount into Words (Indian Currency - Rupees and Paise)

By |Saturday, September 26th, 2015|Categories: Articles, VBA|Tags: , , , , , , , |4 Comments

Below is the VBA function where you can convert a given amount into Words on the basis of Indian Currency. India utilizes a system which is based on Hundred, Thousand, Lakh.....unlike English system which is based on Hundred, Million, Billion..(Indian Numbering System)

Below are numerical equivalent of Indian words

Lakh - 1,00,000
Crore - 1,00,00,000
Arab - 1,00,00,00,000 (more…)

Sat 19 Sep 2015

Tips & Tricks 109 - Macro to Change between A1 and R1C1 Notations

By |Saturday, September 19th, 2015|Categories: Tips and Tricks, VBA|Tags: , , , , , , |0 Comments

Sometimes, you find that all of a sudden your column headings are changed into numbers and your formulas are Rs and Cs. And you need to change them back to alphabetical columns.

The option to do it very simple -

File > Excel Options > Formulas > Uncheck R1C1 Reference Style

1

But if that happens very often, you can use a macro which you can click and toggle between A1 and R1C1 style effortlessly. The macro code to do it one line only -

Sub ChangeReferenceStyle()
Application.ReferenceStyle = xlA1 + xlR1C1 - Application.ReferenceStyle
End Sub

Credit - Rick Rothstein (http://blog.contextures.com/archives/2009/12/04/excel-vba-switch-column-headings-to-numbers/)

The above link also talks about how to add the macro to QAT so that it is always available to you.