Sun 13 Mar 2016

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

By |Sunday, March 13th, 2016|Categories: Articles, VBA|Tags: , , , , , , , , |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|Tags: , , , , , , , , |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.

(more…)

Sat 30 Jan 2016

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

By |Saturday, January 30th, 2016|Categories: Articles, VBA|Tags: , , , , , , |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

(more…)

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

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
    Dim Pass As String, Answer1 As String, Answer2 As String
    Answer1 = MsgBox("Do you want to Protect Selective Sheets", vbQuestion + vbYesNoCancel)
    Select Case Answer1
        Case vbCancel: Exit Sub
        Case vbYes
            Pass = InputBox("Input Your Password to Protect Sheets", "Password")
            For Each Ws In Worksheets
                Answer2 = MsgBox("Do you want to Protect " & Ws.Name, vbQuestion + vbYesNoCancel)
                Select Case Answer2
                    Case vbYes: Ws.Protect Password:=Pass, UserInterFaceOnly:=True
                    Case vbCancel: Exit Sub
                End Select
            Next Ws
        Case vbNo
            Pass = InputBox("Input Your Password to Protect Sheets", "Password")
            For Each Ws In Worksheets
                Ws.Protect Password:=Pass, UserInterFaceOnly:=True
            Next Ws
    End Select
End Sub



Sub UnProtectAll()
    Dim Ws As Worksheet
    Dim Pass As String, Answer1 As String, Answer2 As String
    Answer1 = MsgBox("Do you want to Unprotect Selective Sheets", vbQuestion + vbYesNoCancel)
    Select Case Answer1
        Case vbCancel: Exit Sub
        Case vbYes
            Pass = InputBox("Input Your Password to Unprotect Sheets", "Password")
            For Each Ws In Worksheets
                Answer2 = MsgBox("Do you want to Unprotect " & Ws.Name, vbQuestion + vbYesNoCancel)
                Select Case Answer2
                    Case vbYes: Ws.Unprotect Password:=Pass
                    Case vbCancel: Exit Sub
                End Select
            Next Ws
        Case vbNo
            Pass = InputBox("Input Your Password to Unprotect Sheets", "Password")
            For Each Ws In Worksheets
                Ws.Unprotect Password:=Pass
            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

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