Not at all clear on use of variables and/or object variables
in [General]
|
From: JMay-Rke on 4 Jul 2008 10:19 I jumped on the Excel (VBA) bandwagon some years ago. I have dozens of books and reference them constantly. In no book or in my visiting any newsgroup have I been able to clearly make the distinction between when to engage a regular variable versus an object variable - particularly as regards the Range object. Can someone make an attempt to CONTRAST these two elements?
From: recrit on 4 Jul 2008 10:43 On Jul 4, 10:19 am, JMay-Rke <JMay...(a)discussions.microsoft.com> wrote: > I jumped on the Excel (VBA) bandwagon some years ago. I have > dozens of books and reference them constantly. In no book or > in my visiting any newsgroup have I been able to clearly make > the distinction between when to engage a regular variable versus > an object variable - particularly as regards the Range object. > > Can someone make an attempt to CONTRAST these two elements? do you mean when to use either of the following? dim x x = Range("A1") |OR| dim rng as Range set rng = Range("A1")
From: JMay on 4 Jul 2008 10:54 Exactly -- Spot-on recrit !! "recrit" wrote: > On Jul 4, 10:19 am, JMay-Rke <JMay...(a)discussions.microsoft.com> > wrote: > > I jumped on the Excel (VBA) bandwagon some years ago. I have > > dozens of books and reference them constantly. In no book or > > in my visiting any newsgroup have I been able to clearly make > > the distinction between when to engage a regular variable versus > > an object variable - particularly as regards the Range object. > > > > Can someone make an attempt to CONTRAST these two elements? > > do you mean when to use either of the following? > > dim x > x = Range("A1") > > |OR| > > dim rng as Range > set rng = Range("A1") >
From: recrit on 4 Jul 2008 11:31 On Jul 4, 10:54 am, JMay <J...(a)discussions.microsoft.com> wrote: > Exactly -- Spot-on recrit !! > > "recrit" wrote: > > On Jul 4, 10:19 am, JMay-Rke <JMay...(a)discussions.microsoft.com> > > wrote: > > > I jumped on the Excel (VBA) bandwagon some years ago. I have > > > dozens of books and reference them constantly. In no book or > > > in my visiting any newsgroup have I been able to clearly make > > > the distinction between when to engage a regular variable versus > > > an object variable - particularly as regards the Range object. > > > > Can someone make an attempt to CONTRAST these two elements? > > > do you mean when to use either of the following? > > > dim x > > x = Range("A1") > > > |OR| > > > dim rng as Range > > set rng = Range("A1") for this decision, I would consider the following: (1) Data type, search Excel Visual Basic Help for data type summary. Using "set range" you create an object reference which only uses 4 bytes of memory. The size of the variable using a non-reference type depends on what you are reading in from the cell.... worst case is that its not consistent so you have to use variant type all the time which uses 16 bytes or more depending if its a number or string. However if you know you read in a boolean it only uses 2bytes and then would be more memory efficient. Range object become versatile when you are grinding data where the type is not all the same, so loop through rows and doing something with value will be more efficient. (2) if you are reading in a range of cells, Range("A1:A5")... you still have the same choice. When pulled to a variable you end up with an variant array which can amplify the memory issue. With the range variable you get a referenced object again that you can still iterate over, either for...each || for r=1 to rng.Rows.Count etc (3) Range object allows access to all the Range properties, so it can be useful for interacting with the sheet. ie changing colors, formats, retrieving sheet object, dependents, etc (4) If you are going to perform a destructive operation on the range that you are reading in, then the reference variable will change according... this can cause some errors and confusion. For instance you are reading in Range("A1:A5") but then you delete rows 2 & 3 ... your range variable will not have the values for rows 2 & 3 any more.... in this case if you wanted those values you would have to start with pulling the values into an array. As you can see, it really depends on your application. My preference is to use the range object if possible since its the most versatile and memory efficient. Hope this helps.
From: Jim Thomlinson on 4 Jul 2008 11:33
A varaible and an object are both things that you store in memory in order to reference them. That is really where the similarity ends. Lets start with a variable. A variable is one of your fundamental data types. It is an integer, long, double, string, boolean, array or... They are a variables of a specific memory size. You store values in the varaibles and youi can read the values back. You can write to a varaible with an equal sign. Objects are much more complex. Without getting too far into object oriented programming objects are tangable things like workbooks, worksheets or ranges. Each different object has it's own properties and methods. Properties describe the object such as the cells value, colour, border, ... Methods are things that the object can do such as sort, copy, calculate, ... When you want to create or modify an object you need to use the set key word. Objects use up a fair bit more memory than a standard variable but then again they can do a lot more stuff. While they do take up a lot more memory they are still very efficient to pass around as you are really only passing a pointer to the object and not the entire object itself. -- HTH... Jim Thomlinson "JMay-Rke" wrote: > I jumped on the Excel (VBA) bandwagon some years ago. I have > dozens of books and reference them constantly. In no book or > in my visiting any newsgroup have I been able to clearly make > the distinction between when to engage a regular variable versus > an object variable - particularly as regards the Range object. > > Can someone make an attempt to CONTRAST these two elements? |