Sat 15 Oct 2016

Tips & Tricks 147 - Find the Location of Excel.Exe

By |Saturday, October 15th, 2016|Categories: Tips and Tricks, VBA|Tags: , , , , , |4 Comments

Generally Excel.Exe will be located in C:\Program Files (x86)\Microsoft Office\Office15 in case of Excel 2013 / Windows 7. During installation, if another path is chosen, Excel.Exe's location changes. Default Path is also dependent upon Office and Windows combinations. Sometimes, it may not be locatable at all.

To find the location of Excel.Exe -

1. Start Excel
2. Right click on Windows Task Bar > Start Task Manager > Processes tab (Task Manager can also be opened by pressing CTRL+SHIFT+ESC)
(more…)

Sat 20 Aug 2016

Tips and Tricks 143 - Increment a Number when Workbook is Opened (Invoice or PO Number)

By |Saturday, August 20th, 2016|Categories: Tips and Tricks, VBA|Tags: , , , , , , , |0 Comments

1. Make a backup of your workbook.
2. Open your workbook and ALT+F11
3. Locate your Workbook name in Project Explorer Window
4. Double Click on "This Workbook"
5. Copy paste the Macro code given - Replace Sheet1 and B1 as per your need
6. Save your file as .xlsm if you intend to reuse Macro again.

< A workbook illustrating this can be downloaded from Invoice Number Generator >

'**** Macro Starts

Private Sub Workbook_Open()
Worksheets("Sheet1").Range("B1") = Worksheets("Sheet1").Range("B1") + 1
End Sub

Sat 13 Aug 2016

Article 42 - Generating an Odd Order Magic Square in Excel (VBA)

By |Saturday, August 13th, 2016|Categories: Articles, VBA|Tags: , , , , , , , , , , , |1 Comment

A magic square needs no introduction and we come across it many times. A magic square is a square grid and the minimum size of a magic square is 3x3. The whole numbers in magic square appear only once and all cells are filled. The horizontal rows, vertical columns and main and secondary diagonals all add up to the same number. This number is called magic constant. The more about magic square can be read here - https://en.wikipedia.org/wiki/Magic_square

(more…)

Sat 28 May 2016

Tips & Tricks 137 - VBA - Delete Blank Rows for a Range Quickly (without Looping)

By |Saturday, May 28th, 2016|Categories: Tips and Tricks, VBA|Tags: , , , , |0 Comments

Suppose, you have data like this and you want to delete rows 3,4,6,8,11 & 12 as they are blanks.

1

You need not loop through all cells but you can perform this operation is one shot. You can use below code for the same

 

Mon 09 May 2016

Solution - Challenge 40 – Cryptography Challenge 2 – Fully Functional Caesar’s Shift Cipher

By |Monday, May 09th, 2016|Categories: Solutions, VBA|Tags: , , , , , , , , , , , |1 Comment

Below is a possible solution to the challenge - Challenge 40 – Cryptography Challenge 2 – Fully Functional Caesar’s Shift Cipher

1. Make a backup of your workbook
2. Open your workbook and ALT+F11
3. Locate your Workbook name in Project Explorer Window
4. Right click on your workbook name > Insert > Module
5. Copy paste the Macro code given below
6. Save your file as .xlsm
7. Call your function as =EncryptCS(A1,$B$2)

A workbook containing the above solution can be downloaded from Solution - Challenge 40 – Cryptography Challenge 2 – Fully Functional Caesar’s Shift Cipher.

 

Sat 09 Apr 2016

Challenge 40 – Cryptography Challenge 2 – Fully Functional Caesar’s Shift Cipher

By |Saturday, April 09th, 2016|Categories: Challenges, VBA|Tags: , , , , , , , , , , , |1 Comment

We made ourselves familiarized with Caesar's Shift in last challenge - Challenge 39 - Cryptography Challenge 1 - Caesar's Shift Cipher

That challenge was for writing a formula to perform Caesar's Shift on a single alphabet. But now, we need to have a fully functional Caesar's Shift Cipher.

The challenge before you is to write a VBA function which takes cell or text as first argument and amount of shift as second argument to generate the Cipher Text.

1

Hence, to generate answer in A2, we have called the function as =EncryptCS(A1,B2)

Note - The shift is only for the English Alphabets and case sensitive. Any other character like comma, space etc. should appear as they are.

The answer to this challenge would be published after a month i.e. on 09-May-16.

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.

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

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