Hey guys, welcome back. So in this video, we're going to speak about the range DOT number format property. So the number format property for the range object allows you to change the number format of a cell using Excel VBA. And actually what the number format of a cell is, is basically how the value in the cell looks OK. So it does not change the value of a cell, it just changes how it looks. So for example, here in the range called Molly underscore table here, I've put the value of 10 in all the cells in the range, I can actually give this for example, a percentage number format, okay, so if I click on this button here, it's going to change the number format of the cell.

It's changed it to percentage, so it's now looking like 1,000%. It hasn't changed the value of this cell because 1,000% is still 10. Okay, because 100% is a one. So if you multiply that by 10, this gives you 1,000%. So it actually has not changed the value of the cell, the value of the cell is still 10. However, it just looks like 1,000%.

Okay, so to actually change the number format of a cell, you can change one of these quick formats available here in this list, or you can actually highlight the cells and then right click, click on format cells. Or alternatively, you can actually press Ctrl and one on your keyboard, and this will get you this same window here. And as you can see here, we've got different categories for number formats. So we've gotten a number, currency, accounting date and time percentage fraction, you know, all kinds of number formats. Let's say we're going to choose percentage. And this is actually how you can apply it here using the Excel environment.

So you can choose one of these formats and press OK. And there is also an option to use custom number formatting which is actually creating a Custom format by yourself. So you won't use one of these ready made formats, you will create a custom number format by yourself. And actually, I've got a video posted on YouTube on my YouTube channel on custom number formatting. So how to create your own custom number format and what all these signs mean. So what the pound sign means and what the zero means and all that and a question mark. Okay, so you can learn how to decipher these codes.

Using that video, I'd really recommend you check it out. It's a 45 minute video. It's a long video, and I hope it would be useful for you, it would be actually useful for any Excel user. Okay, anyways, so to actually apply a custom number formatting in Excel, you would actually need to get the code for that number formatting. So the code is here in this type window. And the code for the percentage number formatting that we've applied is this zero percent code here, so I'll just copy that and I'll press cancel here on my window.

I'll actually put it back to the general number format. Okay, so I'll just put it back to the default number format. And I'll go back to my Visual Basic environment here. And I've created a procedure called number underscore format. And in order to use the number format property, you would write the name of your range. So range Maya underscore table DOT number format equals, and then you put your number format between double quotes.

So here, I've got my number format put between double quotes. And if we run that, you can see here the number format has been applied. And you can apply any number format as long as you got its code. I want to also show you a small detail here. That's a bit tricky. Okay.

So if you apply a currency number format here, so I'll just apply this dollar currency number format and if you right click on it, click on format cells, because here, here's the number format applied. I'll go to the custom tab here. And you can see here that this number format actually contains some double quotes, as you can see here, two double quotes having a small hyphen between them. Okay? So if you try to apply this number format using VBA, I'll just copy it here. I'll press cancel here, I'll go back to the general because I want to apply it using VBA.

So go back to my Visual Basic environment, and here, if you copy and paste that between the double quotes, and you try to run this code, you'll actually get an error. Okay, so why did we get an error here, so press n here. The reason for that is that we've got double quotes in the number format and when you have double quotes that you need to put between double quotes, Excel VBA will not recognize is that these are double quotes to be put between two double quotes here. So we need to put these double quotes Between these two double quotes on the very right and the very left. So in order to make VBA recognize that these are actually double quotes because what VBA would think is that actually, this is the start of the double quotes here, the one on the very left and then here you can see that the first double quotes meets, it will actually think that this is actually the end of the double quotes, not actually double quotes to be put between double quotes, I hope that you're getting what I mean.

Anyways, you'd actually need to double the double quotes in the middle in order to make Excel recognize that this is not the ending double quote, this is not a closing double quote. This is actually just a double quotes, you know in the middle, and this is double quote in the middle and this is actually the closing double quote and this is your opening double quote. So hope you got the idea. Now if you run that the macro will work just fine. Okay. And of course, you can actually get the number format of a certain cell using the number format properly To hear, so I can just copy this and put that in a message box.

I can just comment that line of code in order for it not to run and if you run that, you actually got the number format in a message box, okay, so this is you know, how you can obtain the number format of a certain cell using VBA if you want to. Okay, so that's it for the range number format property guys, thank you very much watching this video and I'll see you on the next one.