Re: Defining multiple variables in VBA

[MrIfOnly]

Hi Alan,

Thanks again for a well-thought out response. I, too, am learning. Just when I think I have something in VBA (or life, for that matter) figured out, someone comes along and shows me a better way. That's what I love about forums like this: we get to learn from each other.

Regards,

CJ

[MrIfOnly]

Hi Alan,

Thanks again for a well-thought out response. I, too, am learning. Just when I think I have something in VBA (or life, for that matter) figured out, someone comes along and shows me a better way. That's what I love about forums like this: we get to learn from each other.

Regards,

CJ

[DocAElstein]

Hi CJ,
Your welcome. VBA seems to me totally overwhelming in its complexity. I think getting access to the Experts and VBA Legends ( which I am miles and miles away from ever having the time to become being ) is essential .
Even with it , I too find when I think I have it, I notice another or better way.
Alan

[DocAElstein]

Hi CJ,
Your welcome. VBA seems to me totally overwhelming in its complexity. I think getting access to the Experts and VBA Legends ( which I am miles and miles away from ever having the time to become being ) is essential .
Even with it , I too find when I think I have it, I notice another or better way.
Alan

[DocAElstein]

Just some note to be going on with regarding the Variant.
Variants holding other variables are not necessarily the same as those variables. Some examples that I have come across:
Four things:

_1)
A String in a Variant has a fixed length . This has caught me out on occasions:
http://www.eileenslounge.com/viewtop...=22512#p176056


_2)
We also see that some things "work" if housed in a Variant. An example I know about here is the passing of an Array() ByValue only "working" if that Array() is housed in a Variant.
https://www.excelforum.com/developme...ml#post4381420
This could suggest the thing is being held in a restricted way... It is suggested that the complex way ....that.... "...internal storage and ensuring that the types match so that the offsets are correct...."...
https://www.mrexcel.com/forum/excel-...ml#post4336751 is responsible for preventing this to "work" usually. Once again a reduced size, or size restriction could allow this to work.


_3)
The variable for an Object ( or for anything for that matter ) actually hold or is somehow referring to a "Pigeon Hole" which "points to" and includes information necessary to get at the actual data. ( I believe this is responsible for the vague idea of a Pointer in computing, which some Experts have tried to fool me into believing that they understand.. lol.. ). We can use a Variant in place of an Object, for Object declaration ( Diming ).
The point I want to bring out here is that the variant used to "Hold" an Object is possibly big, but must not be a s massive as the Object itself

_4)
I am not quite sure of the relevance yet here

[DocAElstein]

Just some note to be going on with regarding the Variant.
Variants holding other variables are not necessarily the same as those variables. Some examples that I have come across:
Four things:

_1)
A String in a Variant has a fixed length . This has caught me out on occasions:
http://www.eileenslounge.com/viewtop...=22512#p176056


_2)
We also see that some things "work" if housed in a Variant. An example I know about here is the passing of an Array() ByValue only "working" if that Array() is housed in a Variant.
https://www.excelforum.com/developme...ml#post4381420
This could suggest the thing is being held in a restricted way... It is suggested that the complex way ....that.... "...internal storage and ensuring that the types match so that the offsets are correct...."...
https://www.mrexcel.com/forum/excel-...ml#post4336751 is responsible for preventing this to "work" usually. Once again a reduced size, or size restriction could allow this to work.


_3)
The variable for an Object ( or for anything for that matter ) actually hold or is somehow referring to a "Pigeon Hole" which "points to" and includes information necessary to get at the actual data. ( I believe this is responsible for the vague idea of a Pointer in computing, which some Experts have tried to fool me into believing that they understand.. lol.. ). We can use a Variant in place of an Object, for Object declaration ( Diming ).
The point I want to bring out here is that the variant used to "Hold" an Object is possibly big, but must not be a s massive as the Object itself

_4)
I am not quite sure of the relevance yet here

[DocAElstein]

Variant and variable Types, VarType

For a Variant variable , you “determine how the data in a Variant is treated”. It is not necessarily the case that the variable will then behave exactly as the native ( “normal” ) Variable which it “holds”. Indeed you can run into problems:
https://www.excelforum.com/the-water...ml#post4614261
http://www.eileenslounge.com/viewtop...25ea8e36d9f312
https://www.excelforum.com/the-water...ml#post4613906


Whilst “normal” variables have a single Type / “Type Number, VarType”, the Variant has 3:

Type Number, VarType 0 vbEmpty __ Uninitialized (default)

Type Number, VarType 1 vbNull __ This is somewhat obscure and is defined as “Contains no valid data”. Some time you can get this if you do attempt to do something that does not or should not “work” or return valid information. No one seems to have a clear definition of this, and in most Blog to get it would be done to demo by Let Var = Null , but you can find some code lines that will also return a Null, and this can only be held in a Variant. Edit: here an example on how to get it https://eileenslounge.com/viewtopic....=244579#p24457
https://www.excelforum.com/excel-pro...th-string.html

Type Number, VarType 12 __ (Variant (used only for arrays of Variants)) . This is rather interesting and is probably not only responsible for the problems noted above, but also for some useful things that you can only do with a Variant, such as passing an Array ByValue
https://www.excelforum.com/developme...ml#post4381420

Sub VariableTypeNumbersTypeName2() ' http://www.snb-vba.eu/VBA_Arrays_en.html       http://www.excelfox.com/forum/showthread.php/2157-Re-Defining-multiple-variables-in-VBA?p=10174#post10174        https://www.excelforum.com/the-water-cooler/1174400-would-like-to-know-about-the-forum-experts-gurus-4.html#post4613906
Rem 1 Worksheets info                               Pointer: '_- 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 pigeon 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                               '
Dim WsEV As Worksheet '           '_-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
 Set WsEV = ThisWorkbook.Worksheets("EmptiesVariables")      '_- 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 instance

Rem 2 VarType Constants    https://msdn.microsoft.com/en-us/library/office/gg251422.aspx
Dim VrTpe 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. )       https://www.mrexcel.com/forum/excel-questions/803662-byte-backward-loop-4.html

'2a) Variant Variables: You can determine how the data in a Variant is treated..
Dim Var As Variant: VrTpe = VarType(Var) ' 0                       '  http://www.excelfox.com/forum/showthread.php/2157-Re-Defining-multiple-variables-in-VBA/page2#post10174
Dim VarDef: Let VrTpe = VarType(VarDef) ' 0      vbEmpty  Uninitialized (default)
 Let Range("A25:A26").Value2 = "Some text"                         '  .Value2 gives you the underlying value of the cell (could be empty, string, error, number (double) or boolean)                         '  .Value gives you the same as .Value2 except if the cell was formatted as currency or date it gives you a VBA currency (which may truncate decimal places) or VBA date.
 Let Var = Range("A25:A26").Text                                   '  "Some text" given to Variant    attempts to coerce to a String and will fail if the underlying Variant is not coercable to a String type
 Let Range("A26").Value = "Some other text"                        '  .Value gives you the same as .Value2 except if the cell was formatted as currency or date it gives you a VBA currency (which may truncate decimal places) or VBA date.
 Let Var = Range("A25:A26").Text ' The data is invalid, a Null is given to Variant
 Let VrTpe = VarType(Var)                   ' 1  Contains no valid data
Dim arrVar(1 To 1) As Variant
 Let VrTpe = VarType(arrVar())              ' 12 (Variant (used only for arrays of Variants))

'2b) Normal Variables
Dim Intr As Integer: VrTpe = VarType(Intr)  ' 2
Dim Lng As Long: VrTpe = VarType(Lng)       ' 3  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. )       https://www.mrexcel.com/forum/excel-questions/803662-byte-backward-loop-4.html
Dim Sngle As Single: VrTpe = VarType(Sngle) ' 4
Dim Dble As Double: VrTpe = VarType(Dble)   ' 5


Dim Str As String: VrTpe = VarType(Str)     ' 8  "Pointer" to a "Blue Print" (or Form, Questionnaire 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 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. Instructions will tell how to do this. Theoretically a special value vbNullString is set to aid in quick checks.. But..http://www.mrexcel.com/forum/excel-questions/361246-vbnullstring-2.html#post44116
Dim Obj As Object: VrTpe = VarType(Obj)     ' 9   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.)
 'Set                                      ('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 instance


Dim Bite As Byte: VrTpe = VarType(Bite)     '17

'2c) Array Variables       http://www.snb-vba.eu/VBA_Arrays_en.html#L_4.3
' The Vartype number of an array is 8192. Vartype is the sum of the Vartype of an array (8192) and the Vartype of its content type.
Dim a_bt(1 To 1) As Byte
 Let VrTpe = VarType(a_bt())  ' 8209 = 8192 (array) + 17 (Byte)
Dim a_var() As Variant
 Let VrTpe = VarType(a_var)   ' 8204 = 8192 (array) + 12 (Variant (used only for arrays of Variants))
'2c(ii) Range    ...."........._.... well if you consider the spreadsheet as a “pseudo” Array , as I often do , ( especially when I use “.Cells” in Worksheet Function Grid arguments such as .Index ) , then I guess you can say “array [A1:K20]”        https://www.excelforum.com/the-water-cooler/1174400-would-like-to-know-about-the-forum-experts-gurus-4.html#post4613906
Dim Rnga_sq As Range: Set Rnga_sq = Range("A1:F5")
 Let VrTpe = VarType(Rnga_sq) ' 8204 = 8192 (array) + 12 (Variant (used only for arrays of Variants)) !!!!  VarType  interprets a Range as an array. ???????
End Sub

Lasr row Lr   https://www.excelforum.com/hello-introduce-yourself/1214555-an-old-geezer-coming-over-from-the-access-forum.html
$ as in Left$ etc..  'VBA Strings collection Left function returns a Variant- initially tries to coerces the first parameter into Variant, Left$ does not, that's why Left$ is preferable over Left, it's theoretically slightly more efficient, as it avoids the overhead/inefficieny associated with the Variant. It allows a Null to be returned if a Null is given. https://www.excelforum.com/excel-new...ml#post4084816 .. it is all to do with ya .."Null propagation".. maties ;) '_-.. http://allenbrowne.com/casu-12.html Null is a special "I do not know, / answer unknown" - handy to hav... propogetion wonks - math things like = 1+2+Null returns you null. Or string manipulation stuff like, left(Null returns you Null. Count things like Cnt (x,y,Null) will return 2 - there are two known things there..Hmm - bit iffy although you could argue that Null has not been entered yet.. may never

Lasr row Lr https://www.excelforum.com/hello-int...ess-forum.html

[DocAElstein]

http://www.excelfox.com/forum/showth...0192#post10192

https://powerspreadsheets.com/vba-data-types/

https://powerspreadsheets.com/vba-data-types/

[DocAElstein]

Variant and variable Types, VarType

For a Variant variable , you “determine how the data in a Variant is treated”. It is not necessarily the case that the variable will then behave exactly as the native ( “normal” ) Variable which it “holds”. Indeed you can run into problems:
https://www.excelforum.com/the-water...ml#post4614261
http://www.eileenslounge.com/viewtop...25ea8e36d9f312
https://www.excelforum.com/the-water...ml#post4613906


Whilst “normal” variables have a single Type / “Type Number, VarType”, the Variant has 3:

Type Number, VarType 0 vbEmpty __ Uninitialized (default)

Type Number, VarType 1 vbNull __ This is somewhat obscure and is defined as “Contains no valid data”. Some time you can get this if you do attempt to do something that does not or should not “work” or return valid information. No one seems to have a clear definition of this, and in most Blog to get it would be done to demo by Let Var = Null , but you can find some code lines that will also return a Null, and this can only be held in a Variant. Edit: here an example on how to get it https://eileenslounge.com/viewtopic....=244579#p24457
https://www.excelforum.com/excel-pro...th-string.html

Type Number, VarType 12 __ (Variant (used only for arrays of Variants)) . This is rather interesting and is probably not only responsible for the problems noted above, but also for some useful things that you can only do with a Variant, such as passing an Array ByValue
https://www.excelforum.com/developme...ml#post4381420

Sub VariableTypeNumbersTypeName2() ' http://www.snb-vba.eu/VBA_Arrays_en.html       http://www.excelfox.com/forum/showthread.php/2157-Re-Defining-multiple-variables-in-VBA?p=10174#post10174        https://www.excelforum.com/the-water-cooler/1174400-would-like-to-know-about-the-forum-experts-gurus-4.html#post4613906
Rem 1 Worksheets info                               Pointer: '_- 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 pigeon 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                               '
Dim WsEV As Worksheet '           '_-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
 Set WsEV = ThisWorkbook.Worksheets("EmptiesVariables")      '_- 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 instance

Rem 2 VarType Constants    https://msdn.microsoft.com/en-us/library/office/gg251422.aspx
Dim VrTpe 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. )       https://www.mrexcel.com/forum/excel-questions/803662-byte-backward-loop-4.html

'2a) Variant Variables: You can determine how the data in a Variant is treated..
Dim Var As Variant: VrTpe = VarType(Var) ' 0                       '  http://www.excelfox.com/forum/showthread.php/2157-Re-Defining-multiple-variables-in-VBA/page2#post10174
Dim VarDef: Let VrTpe = VarType(VarDef) ' 0      vbEmpty  Uninitialized (default)
 Let Range("A25:A26").Value2 = "Some text"                         '  .Value2 gives you the underlying value of the cell (could be empty, string, error, number (double) or boolean)                         '  .Value gives you the same as .Value2 except if the cell was formatted as currency or date it gives you a VBA currency (which may truncate decimal places) or VBA date.
 Let Var = Range("A25:A26").Text                                   '  "Some text" given to Variant    attempts to coerce to a String and will fail if the underlying Variant is not coercable to a String type
 Let Range("A26").Value = "Some other text"                        '  .Value gives you the same as .Value2 except if the cell was formatted as currency or date it gives you a VBA currency (which may truncate decimal places) or VBA date.
 Let Var = Range("A25:A26").Text ' The data is invalid, a Null is given to Variant
 Let VrTpe = VarType(Var)                   ' 1  Contains no valid data
Dim arrVar(1 To 1) As Variant
 Let VrTpe = VarType(arrVar())              ' 12 (Variant (used only for arrays of Variants))

'2b) Normal Variables
Dim Intr As Integer: VrTpe = VarType(Intr)  ' 2
Dim Lng As Long: VrTpe = VarType(Lng)       ' 3  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. )       https://www.mrexcel.com/forum/excel-questions/803662-byte-backward-loop-4.html
Dim Sngle As Single: VrTpe = VarType(Sngle) ' 4
Dim Dble As Double: VrTpe = VarType(Dble)   ' 5


Dim Str As String: VrTpe = VarType(Str)     ' 8  "Pointer" to a "Blue Print" (or Form, Questionnaire 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 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. Instructions will tell how to do this. Theoretically a special value vbNullString is set to aid in quick checks.. But..http://www.mrexcel.com/forum/excel-questions/361246-vbnullstring-2.html#post44116
Dim Obj As Object: VrTpe = VarType(Obj)     ' 9   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.)
 'Set                                      ('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 instance


Dim Bite As Byte: VrTpe = VarType(Bite)     '17

'2c) Array Variables       http://www.snb-vba.eu/VBA_Arrays_en.html#L_4.3
' The Vartype number of an array is 8192. Vartype is the sum of the Vartype of an array (8192) and the Vartype of its content type.
Dim a_bt(1 To 1) As Byte
 Let VrTpe = VarType(a_bt())  ' 8209 = 8192 (array) + 17 (Byte)
Dim a_var() As Variant
 Let VrTpe = VarType(a_var)   ' 8204 = 8192 (array) + 12 (Variant (used only for arrays of Variants))
'2c(ii) Range    ...."........._.... well if you consider the spreadsheet as a “pseudo” Array , as I often do , ( especially when I use “.Cells” in Worksheet Function Grid arguments such as .Index ) , then I guess you can say “array [A1:K20]”        https://www.excelforum.com/the-water-cooler/1174400-would-like-to-know-about-the-forum-experts-gurus-4.html#post4613906
Dim Rnga_sq As Range: Set Rnga_sq = Range("A1:F5")
 Let VrTpe = VarType(Rnga_sq) ' 8204 = 8192 (array) + 12 (Variant (used only for arrays of Variants)) !!!!  VarType  interprets a Range as an array. ???????
End Sub

Lasr row Lr   https://www.excelforum.com/hello-introduce-yourself/1214555-an-old-geezer-coming-over-from-the-access-forum.html
$ as in Left$ etc..  'VBA Strings collection Left function returns a Variant- initially tries to coerces the first parameter into Variant, Left$ does not, that's why Left$ is preferable over Left, it's theoretically slightly more efficient, as it avoids the overhead/inefficieny associated with the Variant. It allows a Null to be returned if a Null is given. https://www.excelforum.com/excel-new...ml#post4084816 .. it is all to do with ya .."Null propagation".. maties ;) '_-.. http://allenbrowne.com/casu-12.html Null is a special "I do not know, / answer unknown" - handy to hav... propogetion wonks - math things like = 1+2+Null returns you null. Or string manipulation stuff like, left(Null returns you Null. Count things like Cnt (x,y,Null) will return 2 - there are two known things there..Hmm - bit iffy although you could argue that Null has not been entered yet.. may never

Lasr row Lr https://www.excelforum.com/hello-int...ess-forum.html

[DocAElstein]

http://www.excelfox.com/forum/showth...0192#post10192

https://powerspreadsheets.com/vba-data-types/

https://powerspreadsheets.com/vba-data-types/