Add excel macro to Quick Access Tool

Problem:

I have created few macros in different files. Now to access those macros, always I have to open the files and click on the button to run the macro. Sometime even I was making mistake, like I wanted to run the Macro1 which is created under File1, but by mistake I opened File2 and run Macro2!!

 

Solution:

Many time, I wondered if I will be able to add these macro in File menu or somewhere in ribbon, it will be quicker and easily accessible. One solution I learned is that we can add our macros to the Quick Access Toolbar button. I browsed thru Microsoft website and found the way to do this. Link to the original article.

Steps to do this:

1. Navigate to File -> Options

2. Select Quick Access Tool bar from Left hand side

3. Choose “Macros” from the “Choose commands from:” drop down

4. When we select “Macros”, the bottom list shows – list of macros created

5. From the list select the macro that you wish to add to “Quick Access Toolbar” and then click on “Add” button

6. Select “OK” button and we are done.

 

The macro is added to “Quick Access Toolbar” as shortcut. E.g. Following screen print shows the macro location:

 

 

 

 

 

 

 

 

 

 

Do let me know your thoughts on this solutions and also share any other solution/approach that you have used to do this task.

How to find the number of rows in a worksheet using Excel VBA

Problem: How to count the number of rows that are non blank or used in a worksheet using Excel VBA?

or

How to find the number of rows in a worksheet using Excel VBA?

Solution(s):

Suppose there is a column A – which contains First Name of an Employee. Now following are the solutions that I tried for this problem:

1. Run a loop which check for the cell being null and break when there is a null value encountered.

Sample Code:

For i=2 to 99999

if (Sheet1.Cell(i, 1).Value = “”) then

      noOfRows = i-1

      msgbox “The no of rows are =” & noOfRows

      Exit For

end if

Next i

 

2. Use the worksheet function Count in the Code. Earlier I was using the solution described above. But after learning this method, I have tried using this, since this is faster as it does not have to go thru each and every row and check for blank value.

Sample Code:

Dim noOfRows As Integer
Dim rng As Range
Sheet1.Activate
Set rng = Sheet1.Range(“A:A”)
noOfRows = Sheet1.Application.WorksheetFunction.Count(rng)
msgbox “The no of rows are = “ & noOfRows

 

Seems easy! Let me know your thoughts on this. Also share any other way that you have used for this kind of situation.

Convert large numbers to text using Excel VBA

During one of the project, I was trying to read a text file content and place them into the Excel workbook. I was able to get all the text file content to Excel file, but later I came to know that some of the numbers (that were too large) were automatically getting converted to Exponential format. When the numbers were converted to exponential format, the actual  value was not getting retained. There were zeros after some digits.

 

What I tried:

1. Using VBA, stored the large numbers to string and then assigned it to Cell value. Result – The numbers were still getting converted to exponential format.

2. Manually tried to change the column format to text first and then tried to read the file with large number. Result – This worked but I have to make sure that column has to be formatted and someone accidentally does not change the column format. So thought to use this option only if I could not solve this using VBA.

3.  Using VBA, thought to execute Step 2. I searched google for this issue and found that there is a NumberFormat property that I can use for solving this. E.g. the column I want to format is K then, I have first activate the sheet, then select the column and use the NumberFormat property as below:

Sheet1.Activate
Sheet1.Columns(“AT:AT”).Select
Selection.NumberFormat = “@”

Further reference available @ http://msdn.microsoft.com/en-us/library/office/aa224873(v=office.11).aspx

 

Share your thoughts/comments here… or mail me – anandvshah@gmail.com

Import test cases to Test Rail using Excel VBA

We were thinking to use TestRail for our project. But the only problem we were facing was to convert our existing test cases in excel (xls) format to TestRail XML format. 

We can import test cases to TestRail using XML format which is specified by TestRail only. So our problem was to convert the test case xls file into XML format which can be recognized by TestRail. 

I have created a macro for this task. The linked file can be used for this purpose.

Share your thoughts using comments or by mailing me: anandvshah@gmail.com

Happy Testing!

Create dummy test data file for specific size

Problem:
At times, we need to check if particular file size is allowed to be uploaded or not. In such cases, the headache is to create file of specific size. 

Solution:
Following steps will suffice the requirement:

1. Open command prompt with admin rights in Windows
2. Type the following command:
fsutil file createnew filename.txt filesizeinbytes

E.g. I want to create a file with 1 MB size. So I can use the following command:
fsutil file createnew oneMBFile.txt 1048576

Further reference available @ http://www.windows-commandline.com/2012/02/create-empty-file.html