Sat 26 Nov 2016

Tips & Tricks 150 - Save Each Worksheet as Different Workbooks

By |Saturday, November 26th, 2016|Categories: Tips and Tricks, VBA|Tags: , , , , , , , |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.

(more…)

Sat 19 Nov 2016

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

By |Saturday, November 19th, 2016|Categories: Challenges, VBA|Tags: , , , , , , , , , , , , |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.

1

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|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. 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|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. 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|Tags: , , , , , |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|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

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

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