Sat 19 Sep 2015

Tips & Tricks 109 - Macro to Change between A1 and R1C1 Notations

By |Saturday, September 19th, 2015|Categories: Tips and Tricks, VBA|Tags: , , , , , , |0 Comments

Sometimes, you find that all of a sudden your column headings are changed into numbers and your formulas are Rs and Cs. And you need to change them back to alphabetical columns.

The option to do it very simple -

File > Excel Options > Formulas > Uncheck R1C1 Reference Style

1

But if that happens very often, you can use a macro which you can click and toggle between A1 and R1C1 style effortlessly. The macro code to do it one line only -

Sub ChangeReferenceStyle()
Application.ReferenceStyle = xlA1 + xlR1C1 - Application.ReferenceStyle
End Sub

Credit - Rick Rothstein (http://blog.contextures.com/archives/2009/12/04/excel-vba-switch-column-headings-to-numbers/)

The above link also talks about how to add the macro to QAT so that it is always available to you.

Sat 05 Sep 2015

Excel Quiz 20 - Excel Interface Quiz - Part I

By |Saturday, September 05th, 2015|Categories: Quizzes|Tags: , , , , , , , , , , , |0 Comments

Excel Quiz 20 – Excel Interface Quiz – Part I

Quiz about Excel interfaces like menu, ribbons etc.

Sat 08 Aug 2015

Tips & Tricks 104 - Hide a Sheet Securely

By |Saturday, August 08th, 2015|Categories: Tips and Tricks|Tags: , , , , , , , , , , |0 Comments

You want to hide a sheet so that nobody else can open that. This may arise in some situations like you have a dump of employee data which has sensitive information like salary, last rating, age etc. You want to keep a copy of this in your workbook but you want to refer to only few fields which are non-sensitive in nature. Then, you can resort to below trick -

1. Save your sheet as macro enabled i.e. with .xlsm extension.
2. ALT+F11 to open VBA window (This can also be opened by taking right click in the tab and choosing View Code)
3. Locate your workbook name in Project Explore window which is in top left corner. If this is not available, you can either do CTRL+R or View > Project Explorer
4. Click your sheet.
5. You have Sheet Properties window below Project Explorer. If not available, do F4 or View > Properties Window
6. Last option in Properties Window is Visible, which you should set to 2- xlSheetVeryHidden

1

7. Go back to your workbook / sheet in Project Explorer. Right Click and Insert Module. - This is an important step and many people generally forget about this step.
8. Tools > VBA Project Properties > Protection tab - Check Lock Project for Viewing and put passwords.

1

Now, your sheet can be unhidden only by those persons who know the password for your VBA project.

Sun 02 Aug 2015

Article 19 – How to Check if a cell contains a date

By |Sunday, August 02nd, 2015|Categories: Articles, VBA|Tags: , , , , |4 Comments

Checking for dates in Excel can be a very interesting problem as Excel stores dates as numbers. For example, 4-Jul-15 is stored as 42189.

To elaborate further on this, 1-Jan-1900 is treated as 1. The difference between a date and 1-Jan-1900 +1 is the serial number of that date. hence, in case of 4-Jul-15, it is 42189.

Hence, if you write 42189 and 4-Jul-15 in two different cells, Excel has no built-in mechanism to differentiate between these two.

(more…)

Sun 19 Jul 2015

Article 18 - LTRIM and RTRIM through Excel Formulas

By |Sunday, July 19th, 2015|Categories: Articles|Tags: , , , , , , , , |0 Comments

So, we have TRIM function in Excel. Almost all programming languages provide LTRIM and RTRIM functions also but Excel doesn't provide LTRIM and RTRIM. The same is provided in VBA but most of the Excel users are not using VBA. They are simple folks who want to accomplish their day to day job through Excel functions only.

Before I delve into LTRIM and RTRIM, let's recap TRIM.

TRIM, basically, removes all spaces from your string and if there are more than one space between your words, it will convert them into only one space. So, it removes all leading spaces, all trailing spaces and all spaces between words except one. If there is only one space between words, it will leave that space untouched.

(more…)

Sat 25 Oct 2014

Tips & Tricks 62 - Set the Scroll Area of a Worksheet

By |Saturday, October 25th, 2014|Categories: Tips and Tricks|Tags: , , , |0 Comments

If you want to set a scroll area in a worksheet, for example A1:G50. User will not be able scroll beyond row 50 and column G.

To do this -

1. Right Click on a Sheet tab and click View Code.

OR

Press ALT+F11.

Now VBE Editor will open.

2. Set A1:G50 in Scroll Area to set Scroll Area to A1:G50.

1

3. If you want to reset this, you will have to make ScrollArea blanks.

CATCH - If you close the workbook and open it again, you will find, ScrollArea is blank i.e. this property gets reset. To handle this problem, you will need to do 3 lines of VBA coding and no need to follow steps 2 and 3 of above.

In VBA Editor which you opened in step 1, double click on This Workbook and copy and paste the below code -

Private Sub Workbook_Open()
Sheet1.ScrollArea = "A1:G50" 'Sheet1 can be replaced with your sheet name
End Sub

Save your file with .xlsm extension.

Close the Workbook and reopen again to make the code effective.

But a person knowing the above trick can follow step 1 and remove the code. To handle this problem -

In VBA Editor > Tool > VBA Project Properties > Protection > Check Lock Project for Viewing box and give passwords. Now, unless a person knows the password, he can not remove the code.

Sat 11 Oct 2014

Article 6 - Convert Text Format into Number Format

By |Saturday, October 11th, 2014|Categories: Articles|Tags: , , , , , , |0 Comments

This article was long pending after I wrote Covert Number Format into Text Format.

This post essentially deals with the fact that if a cell has number in text format, how to convert that value into Number format so that it becomes usable for calculations and for other purposes. (Exclusion - If a number has been entered as accounting format 12- i.e. not with leading minus sign, this article doesn't cover this)

Once again, we will look into this problem statement from 4 angles -

1. Formula Way
2. Manual Way
3. Hybrid Way (Both Manual and formula ways combined together)
4. VBA Way

But first, after conversion, how will you know the cell value is converted into number or not. You can use any one method from below -

(more…)

Mon 05 May 2014

Article 1 - Convert Number Format into Text Format

By |Monday, May 05th, 2014|Categories: Articles|Tags: , , , , , , , |2 Comments

The very first post which I want to write about is conversion of numbers into text. For example, if have a numeric value of 2.23, it should be converted to text value of 2.23. We may need this in many data manipulation scenarios. I will not go into why we need this but directly jump into writing about this topic.

In my view, there are always 4 ways to achieve anything in Excel

1. Formula
2. Manual
3. VBA
4. Hybrid (Combining minimum 2 of above methods)

Let's touch on all 4 ways. For the purpose of this article, let's assume a numeric value of 2.23 is contained in cell A1.

But first, after conversion, how will you know that you number is converted into text. You can use any one method from below -

1. Numbers are aligned right and Texts are aligned left by default unless you change the alignment. Hence, after conversion, you will notice that right aligned number is changed to left aligned number (which is text now, in fact). {Note - This is not a foolproof method}

2. Use the formula ISTEXT. If converted Number is contained in B1, use formula = ISTEXT(B1). If result is TRUE, it is Text. This is the best and foolproof method as it doesn't depend upon alignment.

(more…)