Hi CJ
Originally Posted by
MrIfOnly
... does VBA use a larger chunk of memory for the variant, then revert to the lesser amount of memory after the variable is assigned a value and VBA "guesses" the type? For example,
Dim n {n is variant and uses x amount of memory}
n= 3 {VBA says that n is type variant/integer: does the memory used go down?}
Good question, very interesting question, .. but I am afraid I do not know the answer.
Sounds like you know generally more about than computing then me. I have just been learning VBA and Excel for a couple of years now. That is about the limit of my knowledge. 
I do not understand the code snippet you Gave
_..___
I have picked up a couple of things after many many hard hours of asking and googling, for example: ...,_..
_.. A Long variable always uses the same amount of space, regardless of the number. Makes sense I suppose, like pseudo 000.1234 and 145.4567 need the same amount of memory
_.. A String is very efficient apparently as the variable gives just the address of where info is about the string, like where it starts from in memory, and its length.
I did some experiments and found that as I changed a String’s size, the memory location of where the string actually was held changed to accommodate the different length. So it is using just the memory it needs and adjusting each time.
https://www.mrexcel.com/forum/excel-...ml#post4411660
https://www.mrexcel.com/forum/excel-...lstring-4.html
https://www.mrexcel.com/forum/excel-...ml#post4413433
https://www.mrexcel.com/forum/excel-...ml#post4415958
https://www.mrexcel.com/forum/excel-...n-code-me.html
This is the info I often include in codes I give ( or rather inflict on OPs I help ).
I confess, I do not fully understanding what I am talking about, Lol... – go easy on me - I am not a computer professional, Lol.. 
Most Porofessionals i ask do not seem to know much better.. lol..
Code:
Sub DimAlan()
Dim Ws As Worksheet ' EP Dim: For the Variable type declared, there should be some Blue Print or master Instruction list. If not The code will error at the start as it need to examine and therefore prepare for usage of the variable in the code run. The variable itself will go to some “Pigeon hole” that has a copy of the instructions or in some way has information on how to use the original instructions such that it can distinguish between different variables of the same type. When the code meets the variable it will look in the Pigeon Hole Pointer for instructions as to what to do in various situations. Knowing of the type allows in addition to get easily at the Methods and Properties through the applying of a period ( .Dot) ( intellisense ). Note this is a look up type list and may not be a guarantee that every offered thing is available – most will be typically.
Set Ws = ThisWorkbook.Worksheets.Item(1) ' EP Set: Setting to a Class will involve the use of an extra New at this code line. I will then have an Object referred to as an instance of a Class. At this point I include information on my Pointer Pigeon hole for a distinct distinguishable usage of an Object of the Class. For the case of something such as a Workbook this instancing has already been done, and in addition some values are filled in specific memory locations which are also held as part of the information in the Pigeon Hole Pointer. Wed will have a different Pointer for each instance. In most excel versions we already have a few instances of Worksheets. Such instances Objects can be further used., - For this a Dim to the class will be necessary, but the New must be omitted at Set. I can assign as many variables that I wish to the same existing instance
'_-Dim: Prepares "Pointer" to a "Blue Print" (or Form, Questionaire not yet filled in, a template etc.)"Pigeon Hole" in Memory, sufficient in construction to house a piece of Paper with code text giving the relevant information for the particular Variable Type. VBA is sent to it when it passes it. In a Routine it may be given a particular “Value”, or (“Values” for Objects). There instructions say then how to do that and handle(store) that(those). At Dim the created Paper is like a Blue Print that has some empty spaces not yet filled in. A String is a a bit tricky. The Blue Print code line Paper in the Pigeon Hole will allow to note the string Length and an Initial start memory Location. This Location well have to change frequently as strings of different length are assigned. Instructiions will tell how to do this. Theoretically a specilal value vbNullString is set to aid in quich checks.. But..http://www.mrexcel.com/forum/excel-questions/361246-vbnullstring-2.html#post44116
'_-So A variable in VBA is like the Link to the part of a URL string reducing size site where a few things about the actual Final site is informed about. This area in that site, like a pigion Hole to which the variable refers, ( the "pigeon hole" location address, and all its contents would be defined as the "Pointer". Amongst other things it has a link, a "Pointing part", pointing to actually where all the stuff is
'EP Dim: For Object variables: Address location to a "pointer". That has all the actual memory locations (addresses) of the various property values , and it holds all the instructions what / how to change them , should that be wanted later. That helps to explain what occurs when passing an Object to a Call ed Function or Sub Routine By Val ue. In such an occurrence, VBA actually passes a copy of the pointer. So that has the effect of when you change things like properties on the local variable , then the changes are reflected in changes in the original object. (The copy pointer instructs how to change those values, at the actual address held in that pointer). That would normally be the sort of thing you would expect from passing by Ref erence. But as that copy pointer "dies" after the called routine ends, then any changes to the Addresses of the Object Properties in the local variable will not be reflected in the original pointer. So you cannot actually change the pointer.)
'EP Set: Fill or partially Fill: Setting to a Class will involve the use of an extra New at this code line. I will then have an Object referred to as an instance of a Class. At this point I include information on my Pointer Pigeon hole for a distinct distinguishable usage of an Object of the Class. For the case of something such as a Workbook this instancing has already been done, and in addition some values are filled in specific memory locations which are also held as part of the information in the Pigeon Hole Pointer. We will have a different Pointer for each instance. In most excel versions we already have a few instances of Worksheets. Such instances Objects can be further used., - For this a Dim to the class will be necessary, but the New must be omitted at Set. I can assign as many variables that I wish to the same existing http://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it-4.html#post4387191instance
Dim str As String ' ' Prepares "Pointer" to a "Blue Print" (or Form, Questionaire not yet filled in, a template etc.)"Pigeon Hole" in Memory, sufficient in construction to house a piece of Paper with code text giving the relevant information for the particular Variable Type. VBA is sent to it when it passes it. In a Routine it may be given a particular “Value”, or (“Values” for Objects). There instructions say then how to do that and handle(store) that(those). At Dim the created Paper is like a Blue Print that has some empty spaces not yet filled in. A String is a a bit tricky. The Blue Print code line Paper in the Pigeon Hole will allow to note the string Length and an Initial start memory Location. This Location well have to change frequently as strings of different length are assigned. Instructiions will tell how to do this. Theoretically a specilal value vbNullString is set to aid in quich checks.. But..http://www.mrexcel.com/forum/excel-questions/361246-vbnullstring-2.html#post44116
Dim Lenf As Long ' ' Long is very simple to handle, - final memory "size" type is known (123.456 and 000.001 have same "size" computer memory ) , and so a Address suggestion can be given for the next line when the variable is filled in. '( Long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647) If you need some sort of validation the value should only be within the range of a Byte/Integer otherwise there's no point using anything but Long.--upon/after 32-bit, Integers (Short) need converted internally anyways, so a Long is actually faster. )
End Sub
So I have a very rough idea of some variables.
But your question is interesting, as I do not have a good 'ExPlanation for a Variant yet.
If I ever get one, and can answer your question I will reply again
_...
I think the extra memory needed ( if indeed that is the case ) is less relevant as memory becomes cheaper and easier and computers quicker...
But there are both dangers ,
_ as I indicated,
and
_ also very useful uses of a Variant, - Often it makes an initialisation of a variable easy to check for by choosing Variant initially, then checking later if it is
_.. Empty ( not yet used )
_.. Null ( used ( attempted ) but not filled with correct data ) https://eileenslounge.com/viewtopic....244579#p244579
_.. IsArray( ) etc . ... https://www.excelforum.com/excel-new...ml#post4157615
Alan
P.s. I think it is still correct what me and snb said, - The input box always returns ( gives, presents , or chucks out ) a String.
But, I think, however, VBA is very tolerant at like putting a String which “looks” like a number into a number variable ( or accepting it in such a variable ) and taking it then as a number,.
That can be achieved either
_ as you demonstrated ,
or
_ when putting a String which “looks” like a number into a function’s argument which takes as argument a number. I think one says that VBA will “co coerce” the variable into the correct type.
I do not know for sure, but I think in other languages it is not always as tolerant and you have to be careful to include bits to convert the type appropriately like CStr( ) CDbl( ) etc...
You probably know that better than me. I know just VBA and very very old languages from about 25 years ago.
In fact I favour handling all my numbers in String variables, as I have found that I have less format changing problems caused by Date format differences and . and , different conventions for the decimal separator and thousand separator.
http://www.eileenslounge.com/viewtopic.php?f=27&t=22850
Re: Defining multiple variables in VBA
.
Reply With Quote
Bookmarks