Sat 19 Aug 2017

Downloads 17 - Sample CSV Files / Data Sets for Testing (till 1.5 Million Records) - Credit Card

By |Saturday, August 19th, 2017|Categories: Downloads, VBA|Tags: , , , , , , , , , , , , , , , , , |0 Comments

Disclaimer - The datasets are generated through random logic in VBA. These are not real credit card data and should not be used for any other purpose other than testing.

You can download sample csv files ranging from 100 records to 1500000 records. 1.5 Million records will cross 1 million limit of Excel. But 1.5 Million Records are useful for Power Query / Power Pivot. These csv files contain data in various formats like Text and Numbers which should satisfy your need for testing.

This data set can be categorized under "Credit Card" category.

The data generated follow all known rules for credit cards.

Below are the fields which appear as part of these csv files as first line.

(more…)

Sat 12 Aug 2017

Downloads 16 - Sample CSV Files / Data Sets for Testing - Human Resources

By |Saturday, August 12th, 2017|Categories: Downloads, VBA|Tags: , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , |0 Comments

Disclaimer - The datasets are generated through random logic in VBA. These are not real human resource data and should not be used for any other purpose other than testing.

You can download sample csv files ranging from 100 records to 500000 records. These csv files contain data in various formats like Text, Numbers, Date, Time, Percentages which should satisfy your need for testing.

This data set can be categorized under "Human Resources" category.

Below are the fields which appear as part of these csv files as first line.

(more…)

Sat 05 Aug 2017

VBA - Data Masking or Anonymize the Data through a Macro

By |Saturday, August 05th, 2017|Categories: VBA|Tags: , , , , , , , , , , , , , , |0 Comments

Sometimes, you may need to sanitize your data before sharing your file. The below macro would sanitize the data and since this is completely based on random generation, hence, it can not be recreated.

Make sure that you have a copy of the Excel sheet before you sanitize or mask or anonymize the data by running this data. As once done, the data can not be recreated.

The Excel file related to this article can be downloaded from Data Masking Macro

(more…)

Sat 17 Jun 2017

VBA - A Function to Check whether a Given Number is Prime

By |Saturday, June 17th, 2017|Categories: VBA|Tags: , , , , , , , , , |0 Comments

Below is an optimized function to check on the primality of a number. This function takes input number as Double, hence can be used to check upto a number having 15 significant digits. Whereas Long can take up to a maximum of 10 significant digits and maximum number it can support is 2,147,483,648.

First function is when pure number is passed, hence argument can be declared as Double. Hence, you will have to pass the value not the range.

Second function is when variant is passed as argument so that even range can be passed in this function.

Below is time performance for both functions -

The file used for checking time performance - Prime Number Checker

Sat 20 May 2017

Article 45 - How to know if a Cell contains Time

By |Saturday, May 20th, 2017|Categories: Articles, VBA|Tags: , , , |0 Comments

We talked about Article 19 – How to Check if a cell contains a date. Now, in this article, I will talk about checking the cell for time.

The way Excel stores dates as numbers, in the same way it stores time as numbers only. Only change is in range. The numbers get stored between 0 to 1. Hence,  6 AM is stored as .25, 12 PM is stored as 0.5 and so on.

(more…)

Sat 21 Jan 2017

Tips & Tricks 154 - Insert a Space after Each Character

By |Saturday, January 21st, 2017|Categories: Tips and Tricks, VBA|Tags: , , , , , , , |0 Comments

You have a sheet and you want to put a space after each character. For example, A1 contains 12345, you want to make it 1 2 3 4 5. B1 contains Mango12 and you want to make it M a n g o 1 2. The same need to be accomplished by a simple macro. (Flash Fill also works in most of the cases. But Flash Fill will copy in a different range which you will have to copy back and if data is scattered throughout the sheet, it may not be convenient)

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 and change "Sheet1" as per your requirement.
6. Go back to your Workbook and ALT+F8 to display Macro Window
7. Run your Macro from here
8. Delete you Macro if the Macro was needed to be run only once.
9. Otherwise save your file as .xlsm if you intend to reuse Macro again.

 

Sat 07 Jan 2017

Tips & Tricks 153 - Sum only Visible Columns

By |Saturday, January 07th, 2017|Categories: Tips and Tricks, VBA|Tags: , , , , , , , , |0 Comments

You can use SUBTOTAL or AGGREGATE function to sum visible rows but Excel doesn't provide the facility to sum only visible columns. Look below. Columns B and D are hidden. Hence, our function should sum up only A, C, E and F. G2 has the formula =SUM(A2:F2)

If you hide the columns, the sum stays the same whether columns are hidden or visible.

1

Unfortunately, Excel doesn't provide any native functionality to accomplish this. This will have to be done through a VBA function. We will write a VBA function "SumVisCols" which can be called like =SumVisCols(A2:F2)

(You can give any range not only A2:F2)

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 and change the bold lines as per your requirement
6. Go back to your Workbook and ALT+F8 to display Macro Window
7. Run your Macro from here
8. Delete you Macro if the Macro was needed to be run only once.
9. Otherwise save your file as .xlsm if you intend to reuse Macro again.

Sat 24 Dec 2016

Tips & Tricks 152 - While Printing a Sheet, Don't Print Page Number on First Two Pages and Print 1 on 3rd Page

By |Saturday, December 24th, 2016|Categories: Tips and Tricks, VBA|Tags: , , , , , |0 Comments

One user had a specific requirement. His first two pages were cover page and Table of Contents. Hence, he wanted to start his page number from 3rd page. He wanted to display page X of Y. Hence, if the worksheet had 10 pages, his 3rd page should say 1 of 8......Last Page should say 8 of 8.

The above problem will have to be solved through VBA.

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 ThisWorkbook
5. Copy paste the Macro code given
7. Save your file as .xlsm
8. Create a button and attach this macro to that button

 

 

Mon 19 Dec 2016

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

By |Monday, December 19th, 2016|Categories: Solutions, VBA|Tags: , , , , , , , , , , , , |0 Comments

Below is a possible solution to Challenge 56 – Cryptography Challenge 5 – Fully Functional Caesar’s Shift Cipher Decrypter

(more…)

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