Sat 26 Nov 2016

## Tips & Tricks 150 - Save Each Worksheet as Different Workbooks

By |Saturday, November 26th, 2016|Categories: Tips and Tricks, VBA||0 Comments

You have got a workbook and you want to save each worksheet as different workbook - You can adopt following method to do so if worksheets are not many in number.

1. Right click on a worksheet tab
2. Move or Copy
3. Select new book
4. Save this new workbook
5. Do it for all 20 worksheets.

Sat 19 Nov 2016

## Challenge 56 – Cryptography Challenge 5 – Fully Functional Caesar’s Shift Cipher Decrypter

By |Saturday, November 19th, 2016|Categories: Challenges, VBA||1 Comment

We made ourselves familiarized with Caesar's Shift in Challenge 39 - Cryptography Challenge 1 - Caesar's Shift Cipher and made its decrypter in Challenge 53 - Cryptography Challenge 4 - Caesar's Shift Cipher Decrypter. We also made a fully functional encrypter in Challenge 40 – Cryptography Challenge 2 – Fully Functional Caesar’s Shift Cipher based on VBA solution as formula based solution has its own limitation.

The challenge before you is to write a VBA function to create a fully functional Caesar's Shift Cipher Decrypter. This should take cell or text as first argument and amount of shift as second argument to generate the Decrypted Text.

Hence, to generate answer in A3, we have called the function as =DecryptCS(A2,C2)

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 Excel file related to this challenge can be downloaded from Challenge 56 – Cryptography Challenge 5 – Decryption - Fully Functional Caesar’s Shift Cipher

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

Sat 12 Nov 2016

## Tips & Tricks 149 - Battery Charge Remaining

By |Saturday, November 12th, 2016|Categories: Tips and Tricks, VBA||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. Right click on your workbook name > Insert > Module
(more…)

Sat 29 Oct 2016

## Tips & Tricks 148 - Get Battery Charging Status

By |Saturday, October 29th, 2016|Categories: Tips and Tricks, VBA||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. Right click on your workbook name > Insert > Module
(more…)

Sat 15 Oct 2016

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

By |Saturday, October 15th, 2016|Categories: Tips and Tricks, VBA||8 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||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||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

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.

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

```On Error Resume Next
Range("A1:A13").SpecialCells(xlBlanks).EntireRow.Delete
On Error GoTo 0```

Mon 09 May 2016

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

By |Monday, May 09th, 2016|Categories: Solutions, VBA||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)

```Function EncryptCS(PText, Shift) As String
Dim Ws As Worksheet
Dim i As Long
Dim CText As String
Dim PArr, CArr
Application.Volatile
PArr = Split(StrConv(PText, vbUnicode), vbNullChar)
ReDim CArr(UBound(PArr) - 1)
For i = LBound(PArr) To UBound(PArr) - 1
If Abs(77.5 - Asc(UCase(PArr(i)))) < 13 Then
If Asc(LCase(PArr(i))) + Shift > 122 Then
CArr(i) = Chr(Asc(PArr(i)) + Shift - 26)
Else
CArr(i) = Chr(Asc(PArr(i)) + Shift)
End If
Else
CArr(i) = PArr(i)
End If
Next i
EncryptCS = Join(CArr, "")
End Function```

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

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.