Sun 13 Mar 2016

## Article 37 - VBA - Generating Unique (Non-repeating) Random Numbers Efficiently

By |Sunday, March 13th, 2016|Categories: Articles, VBA||0 Comments

This article is about how to generate unique or non-repeating random numbers given two limits. Hence, if lower limit is 10 and maximum limit is 100, hence, it should generate random numbers between 10 and 100.

The algorithm to ensure uniqueness is following -

1. Given lower limit and upper limit, generate all numbers and populate an array. Now, this array contains all numbers between lower limit and upper limit sequentially. Hence, in case of 10 to 100, it will contain entries 10, 11, 12, .....99,100.
2. Next is to shuffle the array randomly using Fisher Yates algorithm so that the array contains the numbers 10, 11, 12...99,100 in a random order.
(more…)

Sun 14 Feb 2016

## Article 36 - VBA - User Names in Excel

By |Sunday, February 14th, 2016|Categories: Articles, VBA||1 Comment

Many times, we are required to fetch User Names in Excel. Unfortunately, getting User Names in Excel is possible only through VBA. These are very small pieces of VBA codes and even a person not knowing VBA can make use of them by following the instructions here.

I am going to discuss the various type of User Names which we may be required to extract.

Sat 30 Jan 2016

## Article 35 - VBA - Timer Function and Overcoming Midnight Limitation of Timer Function

By |Saturday, January 30th, 2016|Categories: Articles, VBA||1 Comment

The basic and classic and most popular mode to time your code is achieved through Timer function which is natively supplied by VBA. It returns a Single representing the number of seconds elapsed since midnight. Hence, your code will look like following when you want to measure the time and it gives the result in Seconds.

```Dim StartTime As Single, TimeElapsed As Single
StartTime = Timer
< --- Your Code Follows --->
TimeElapsed = Timer - StartTime```

How Accurate is Timer Function

Sat 26 Dec 2015

## Tips & Tricks 124 - VBA - Check if a Range is Blank When Range Contains Formulas returning Blanks

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

In VBA, often, we have situations where we need to check if a range is blank or not. The popular formula which we see following is used to check whether range is blank or not.

WorksheetFunction.CountA(Range("A1:A100"))

```Sub CheckifBlank()
If WorksheetFunction.CountA(Range("A1:A100")) Then
MsgBox "Range is Not Blank"
Else
MsgBox "Range is Blank"
End If
End Sub```

But if your range contains even a single formula which is returning blank, the above will not return the range as blank even though it contains literal blanks.

To handle this, you can use below to check if your range is blank even though the range contains formulas returning blanks.

Range("A1:A100").Rows.Count = WorksheetFunction.CountBlank(Range("A1:A100"))

```Sub TestBlanks()
If Range("A1:A100").Rows.Count = WorksheetFunction.CountBlank(Range("A1:A100")) Then
MsgBox "All Blanks"
Else
MsgBox "Not All Blanks"
End If
End Sub```

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||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)

```Private Sub Workbook_Open()
Worksheets("Sheet Name").Activate
End Sub```

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 *******

```Sub ProtectAll()
Dim Ws As Worksheet
Answer1 = MsgBox("Do you want to Protect Selective Sheets", vbQuestion + vbYesNoCancel)
Case vbCancel: Exit Sub
Case vbYes
For Each Ws In Worksheets
Answer2 = MsgBox("Do you want to Protect " & Ws.Name, vbQuestion + vbYesNoCancel)
Case vbCancel: Exit Sub
End Select
Next Ws
Case vbNo
For Each Ws In Worksheets
Next Ws
End Select
End Sub

Sub UnProtectAll()
Dim Ws As Worksheet
Answer1 = MsgBox("Do you want to Unprotect Selective Sheets", vbQuestion + vbYesNoCancel)
Case vbCancel: Exit Sub
Case vbYes
For Each Ws In Worksheets
Answer2 = MsgBox("Do you want to Unprotect " & Ws.Name, vbQuestion + vbYesNoCancel)
Case vbCancel: Exit Sub
End Select
Next Ws
Case vbNo
For Each Ws In Worksheets
Next Ws
End Select
End Sub```

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

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||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.

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")