Check Cell Formatting in Excel

Question

I'm trying to produce a simple Excel assessment and have come across a stumbling block.

The 'Candidate' needs to correctly format a cell to 'Currency' so that it shows the £ sign in the cell.

Is there a function that can check the presence of the '£' after the cell has been formatted

The function bar only shows the value as a number without the £ sign

On my scoring sheet, I give a value of '1' for correct answer and '0' for incorrect.

I therefore could really use some advice to help me establish if the 'Candidate' correctly formats the cell

Many thanks

Paul

Answer 1

Go into the VBE and add an empty module. Copy into that module the following code:

Option Explicit

Public Function IsFormatted(FormattedCell As Range) As Byte

IsFormatted = 0
If InStr(1, FormattedCell.Cells(1, 1).NumberFormat, "$", vbTextCompare) >= 1 Then IsFormatted = 1

End Function

Then you have the function which you can use like this: =IsFormatted(D2).

Note: the generic number format for currencies is made with a $ sign and not a £. This translates automatically into UK currency based on your windows settings. If you want to make sure that people have set the format of the cell to UK £ then you need to change the following line in the above code:

If InStr(1, FormattedCell.Cells(1, 1).NumberFormat, "[$£-809]", vbTextCompare) >= 1 Then IsFormatted = 1

Please keep in mind that the above function is not volatile. That means the value of the function will not change if the referenced cell is changed. In other words: if you check a cell like so =IsFormatted(D2) and we assume that this formula is in cell F2 then F2 will be 0 when D2 is not correctly formatted. Now, if you format D2 correctly then F2 will still display 0. So, you'll have to force-update the sheet with the functions with F9 or you add Application.Volatile to the above function. Yet, this may significantly slow down your Excel. Look at the following article for more details: http://www.excel-easy.com/vba/examples/volatile-functions.html

Answer 2

You could use a = Right(Left(range("D3").numberformat, 3), 1) to see what format it is in. Based on the string that it pulls, you will be able to find the currency symbol that is showing in the cell.

You could also change the range to a dynamic variable to check any cell that you want.