Get a month of TabletWise Pro for free! Click here to redeem 
TabletWise.com
 

The Formula Property

Software Microsoft Excel Excel VBA for Beginners 3- The Range object - Dealing with cells
3 minutes
Share the link to this page
Copied
  Completed
You need to purchase the class to view this lesson.
One-time Purchase
$99.99
List Price:  $139.99
You save:  $40
د.إ367.26
List Price:  د.إ514.18
You save:  د.إ146.92
A$143.87
List Price:  A$201.42
You save:  A$57.55
৳8,489.32
List Price:  ৳11,885.38
You save:  ৳3,396.06
CA$135.87
List Price:  CA$190.22
You save:  CA$54.35
CHF 94.11
List Price:  CHF 131.76
You save:  CHF 37.64
kr659.09
List Price:  kr922.75
You save:  kr263.66
€88.48
List Price:  €123.88
You save:  €35.39
£79.21
List Price:  £110.90
You save:  £31.68
HK$775.18
List Price:  HK$1,085.28
You save:  HK$310.10
₹7,518.79
List Price:  ₹10,526.61
You save:  ₹3,007.82
RM426.55
List Price:  RM597.19
You save:  RM170.64
₦38,742.47
List Price:  ₦54,241
You save:  ₦15,498.53
kr942.59
List Price:  kr1,319.67
You save:  kr377.07
NZ$151.92
List Price:  NZ$212.70
You save:  NZ$60.77
₱4,946.60
List Price:  ₱6,925.45
You save:  ₱1,978.84
₨16,611.72
List Price:  ₨23,257.07
You save:  ₨6,645.35
S$139.09
List Price:  S$194.74
You save:  S$55.64
฿3,133.37
List Price:  ฿4,386.84
You save:  ฿1,253.47
₺686.33
List Price:  ₺960.89
You save:  ₺274.56
R1,677.23
List Price:  R2,348.19
You save:  R670.96
Already have an account? Log In

Transcript

Hey guys, welcome back. So in this video we're going to discuss the dot formula property for the range object. So what the dot formula property does is that it outputs the formula of the range object that you use it on. However, it can only work with objects having single cells, okay? So with range objects only having single cells, so you could use it with range a one for example, but it cannot work with range objects that are having multiple cells, right? Because you cannot output the formula of multiple cells at the same time.

So you can't use it on range a one to D five, for example. Okay, so the range object that you would use it with, needs to have only just a single cell. Okay, so to have an example here, I've got a Sub procedure that is called range underscore for me that that I've created. And remember guys, we've got a named range called my underscore table that We have created before, okay, so if I write on cell D 14 equals some, my underscore table, and I've actually popped the value of 10 in each cell of the range called Molly underscore table, and this range consists of 60 cells, so the summation should be 600. Okay? And if we go back to the Visual Basic environment, and we would write range, d 14 dot formula, okay, and we put that inside a message box, and we run that we will actually get a message box containing the exact formula that we've written in cell D 14.

So if you run that, you can see here we got the formula in cell D 14. However, if I do range d 14 dot value, I would get actually the value that is in cell D four tene, which is 600. Okay, so you're able to retrieve the format of a cell, or from a cell using the range dot format up property. However, you're also able to write the formula in a cell. So you can write a certain formula in Excel using the range dot formula property. So let's say I'm going to write this same formula here, but using VBA.

So I'm going to delete it from cell D 14. And then I'm going to write range d 14 dot formula equals, and then I actually need to write the exact formula, but between double quotes, so equals sum of my underscore table between double quotes. So if I run that, this is going to write the formula and we're going to get the result which is 600. The same result, you could also use the dot value property as well and that is going to write the formula correctly as well. So if I delete that Go back and use the value property, this as well we write the formula correctly, and everything's just going to be fine. And this formula, of course works and it updates if I just change the value of one of the cells.

You can see here that the formula updates. I'm just going to press Ctrl and z here. Okay, so that's it, guys for the dot formula property. Thank you very much for watching this video and I'll see you on the next one.

Sign Up