Re: Defining multiple variables in VBA

[MrIfOnly]

This is a carry over from Rick's post #18 in this MrExcel thread (https://www.mrexcel.com/forum/excel-questions/992142-visual-basic-applications-macro-find-value-inside-string-copy-cell-sheet-2.html) (I didn't want to derail the thread):

Rick's post:



Just so I can narrate, answer the first question with 5 and the second question with 9... the expected sum would be 14, not the 59 that is returned.


Sub Test()
Dim S1, S2, S3 As Long
S1 = InputBox("Enter first integer number:")
S2 = InputBox("Enter second integer number:")
S3 = S1 + S2
MsgBox S3
End Sub

I've been using the format: Dim x, y, z as type for years now and have recently been shown the errors of my ways. But I never thought it caused any actual problems in my code until Rick posted the above.

My question is this: I stepped thru the above code with watches on all the variables. It seems that Excel is assigning the non-explicitly declared variables as variants (expected) but then 'guessing' the type when they are assigned values. Do you have any idea what triggers the guess? I tried things like 5.0, 8.9 and the like to see if I can force the guess from String to Long or Double but the variables remained Variant/String types.

Just curious, really. I may have to go back through all of my VBA to see what has actually been happening behind my back while I wasn't looking %D.

Thanks,

CJ


https://www.youtube.com/watch?v=ySENWFIkL7c&lc=UgyqIYcMnsUQxO5CVyx4AaABAg (https://www.youtube.com/watch?v=ySENWFIkL7c&lc=UgyqIYcMnsUQxO5CVyx4AaABAg)


https://www.youtube.com/watch?v=yVgLmj0aojI&lc=UgwWg8x2WxLSxxGsUP14AaABAg.9k3ShckGnhv9k89Lsaig oO (https://www.youtube.com/watch?v=yVgLmj0aojI&lc=UgwWg8x2WxLSxxGsUP14AaABAg.9k3ShckGnhv9k89Lsaig oO)
https://www.youtube.com/watch?v=yVgLmj0aojI&lc=UgxxxIaK1pY8nNvx6JF4AaABAg.9k-vfnj3ivI9k8B2r_uRa2 (https://www.youtube.com/watch?v=yVgLmj0aojI&lc=UgxxxIaK1pY8nNvx6JF4AaABAg.9k-vfnj3ivI9k8B2r_uRa2)
https://www.youtube.com/watch?v=yVgLmj0aojI&lc=UgxKFXBNd6Pwvcp4Bsd4AaABAg (https://www.youtube.com/watch?v=yVgLmj0aojI&lc=UgxKFXBNd6Pwvcp4Bsd4AaABAg)
https://www.youtube.com/watch?v=yVgLmj0aojI&lc=Ugw9X6QS09LuZdZpBHJ4AaABAg (https://www.youtube.com/watch?v=yVgLmj0aojI&lc=Ugw9X6QS09LuZdZpBHJ4AaABAg)

[snb]

What's the point ?
The result of an Inputbox is always a string.


Sub M_snb()
S1 = InputBox("Enter first integer number:")
S2 = InputBox("Enter second integer number:")
S3 = 1 * S1 + S2

MsgBox S3
End Sub

[DocAElstein]

Hi MrIfOnly,
Welcome to ExcelFox :)

I think there are probably many good reasons ( other than the less relevant these days of “using Variant data type does consume more resources” ) to be careful with your Declaring ( Diming ) of variables. Some of which I know about.


Anyway, to answer your specific question. I think this is the answer is fairly straight forward :
The Input Box displays a prompt in a dialog box, waits for the user to input text or click a button, and then returns a String containing the contents of the text box. (If the user clicks Cancel, a zero-length String is returned. )

VBA does not have to guess in this case. It is given a String


_.....................

Alan

This is an example of where using a Variant can catch you out


You probably know that you can refer to many things in VBA through their Item Number. In addition for many things you can also refer to them by their item String Number.

For example , form here:
https://www.excelforum.com/excel-programming-vba-macros/1102805-learing-about-range-objects-2.html#post4183530

As example a Worksheet can be referred to in ways such as these

Worksheets.Item(1)
Worksheets.Item(“Sheet1”)


( As Item is usually the default Property in VBA , then you can do this_..
Worksheets(1)
Worksheets(“Sheet1”)
_.. In my opinion that is also a bit of a bad habit as you are relying on VBA to guess. I think only VBA has some of these defaults, so if you get in the habit you might do it in other languages that do not have it as the default )


The danger is that you can , and people sometimes do , use a number as a String name, like

Worksheets.Item(“128746712”)

That is no problem when hard coding as in the last line

But consider that you are using a variable, and are careless in how you Declare it, that is to say it is a Variant, containing the ____ 128746712
Say a Variant type variable, var, has 128746712 in it , and you do this

Worksheets.Item(var)

Depending on exactly how / where / when you “filled” that variable, var, it may be a Variant holding a String , or a Variant holding a Number.
If it is holding a String, then no problem. If it is holding a number, then you will effectively have

Worksheets.Item(128746712)

You can probably guess what happens here: VBA tries to reference the 128746712 th Worksheet !!!

To be on the safe side, ( “Belt an Braces” ), I always do like this

Worksheets.Item("" & var & "")
or this
Worksheets.Item("" & var)

The above two concatenate a String or Strings ( all be it of zero length ) with a String or a number. The end result is a String

_................


You probably know what Rick is demonstrating: In String building you can use a & or a +
If the variables are strings you get for S1 + S2 like
S1 & S2, __ - the two numbers ( actually string characters ) are physically “stuck” together side by side

If the variables are numbers you get for S1 + S2 like
S1 + S2 __ - the numbers are added mathematically



Alan


P:S. Lööking at snb's response made me think again about an age old problem of trying to Put into a VBA String Variable a text which includes itself a quote or two...
I know how to do it, but often, like now puzzle as to why.
To get for example displayed this_..
____how to get “from” the current cell to the other referenced cell
:.. the string I type in VBA code is
__= "how to get "“from"” the current cell to the other referenced cell"
What I am thinking is that we might be tricking VBA a bit here. - We see a correct syntaxly pair - a starting " and finishing “
So we have two Strings
__= "how to get "
__= ” the current cell to the other referenced cell"
In between we have something that again appears syntax valid to build into a String, but somehow is not. VBA is somehow confused and just talks it as “from” and puts exactly that between the other two Strings...

[DocAElstein]

Hi snb

-----The result of an Inputox .... What's the ... Inputox then :) %D








EDIT a long time later...
Just some Inputox Notes I wanted to dump here, as a quick place to dump them ..

Rick said here: _..
http://www.excelfox.com/forum/showthread.php/1828-How-To-React-To-The-Cancel-Button-in-a-VB-(not-Application)-InputBox
_... about the...How To React To The Cancel Button in a VB (not Application) InputBox _.. that_..
_...I think the below code snippet is self-explanatory (even though the StrPtr function may be new to you)...


Sub TesthIe()
Dim Answer As String
'....
'....
Answer = InputBox("Tell me something")
If StrPtr(Answer) = 0 Then
MsgBox "The user clicked Cancel, so we will exit the subroutine now."
Exit Sub
ElseIf Len(Answer) = 0 Then
MsgBox "The user clicked OK without entering anything in the InputBox!"
Else
MsgBox "The user entered the following..." & vbLf & vbLf & Answer
End If
'....
'....
End Sub

I don't think it is easy
_...
This my take on it:
String variable:
"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.
So at the address of the variable itself there is only a pointer to a memory area.
StrPtr
The StrPtr function “returns the address of start of that actual memory area once used in which the Unicode string is stored”. This address is not identical to the address of the variable that you use this string in Visual Basic: - at the address of the variable itself there is only a pointer to a memory area..
The actual memory address ( for the start of the string ) may change, but it will not have been set the first time if the variable has not yet been used.
API ddl stuff
I expect the Functions like the StrPtr function are “WRapper functions for some API Function”. In English I think that means that that StrPtr function gives the necessary and appropriate arguments for an API Function that can return addresses of variable by running a code hidden somewhere in a File usually with a .dll extension which is usually supplied as standard with windows. The idea being that these files are a sort of directly runtime executable things that many other applications, such as Excel VBA can use in a code run. It is some sort of efficient way to allow the linking to be done to the file and the various functions loaded and used as the code such as the VBA code runs. Hence a name direct link library – The file is some special sort of runnable thing rather than just a collection of function codes. It is somehow directly Integratable into a code, as apposed to an earlier way of doing things known as static linking which had to copy a lot of stuff into a code to be used when the code ran. The word API is just a word used to intimidate. It stands for Application Program Interface. It very loosely means the things available to an Application programmer from these libraries or some similarly available coding stuff. The word Interface is often used in computing to mean the bit someone can interact with which has all the other clever stuff behind it. It is intend to give some unity / consistency / standardisation of the bit people use to do more complicated stuff that might not be directly available or might be require a lot more in depth computer knowledge to get to and use directly. If you like API is the control panel.
These things are often a bit vague as I doubt anyone knows in the meantime what is actually going on. You may actually be using the “Excel API” when you play around with Data Base things which use stuff that takes Objects, Linking and Embedding them ( OLE ) into a VBA code. ( you use OLEDB library stuff )
Anyway the end result of all this is that the actual address, ( as a Long number I expect ) is retuned by the API function and that passed to the wrapper function StrPtr
Very likely the API function or the wrapper function is wired to return 0 if the things somehow does not work, or doesn’t find anything or whatever similar.
Hence the end result of all this is I will have
____________StrPtr(MyVaraibleNotYetUsed)=0





https://msdn.microsoft.com/de-de/library/bb978983.aspx
http://www.excelfox.com/forum/showthread.php/2157-Re-Defining-multiple-variables-in-VBA?p=10192#post10192
http://www.excelfox.com/forum/showthread.php/2157-Re-Defining-multiple-variables-in-VBA#post10153
https://www.excelforum.com/development-testing-forum/1154829-collection-stuff-of-codes-for-other-threads-no-reply-needed-18.html#post4630570

[snb]

Forgot your glasses ?

[DocAElstein]

Forgot your glasses ?The ones with my glühwein in? :) :cheers:

[MrIfOnly]

What's the point ?
The result of an Inputbox is always a string.

The point was curiosity.

A better way to state your second statement: the result of an Inputbox defaults to a string, no? After all, I can define S1 as an Integer and the result of S1 = InputBox("Enter first integer number:") would be an integer.

Thank you, Alan, for the explanation and example of the dangers of Variant type. I'm curious about the resources issue: 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?}

Just for the sake of conversation, this works in C++:


//test stacked variables

#include<iostream>

using namespace std;

int main()
{
long S3, S1, S2;

cout << "Enter first integer number: ";
cin >> S1;
cout << "\nEnter second integer number: ";
cin >> S2;
S3 = S1 + S2;
cout << "\nSum is: " << S3<<endl;

return 0;
}

S1, S2 & S3 are all Long. So this concept in VBA is a bit of different thinking for me.

Regards,

CJ

[snb]

Nur Paulaner:)

[DocAElstein]

Nur Paulaner:)

Mine is a Dunkle Bier or occaisionally a wein with my Father in Law to ease the pain of the aging Mother in Law. .. Lol..
I expect in a few years time, I may get into Paulaner. - Some Kneipers here have an offer of what they call "A Bucket of Paulaner"
:)

[DocAElstein]

Hi CJ

... 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-questions/361246-vbnullstring-2.html#post4411660
https://www.mrexcel.com/forum/excel-questions/361246-vbnullstring-4.html
https://www.mrexcel.com/forum/excel-questions/917689-passing-array-class-byval-byref-2.html#post4413433
https://www.mrexcel.com/forum/excel-questions/917689-passing-array-class-byval-byref-4.html#post4415958
https://www.mrexcel.com/forum/excel-questions/918015-call-byval-byref-strptr-address-inconsistencies-my-end-please-run-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..


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.php?f=30&t=31608&p=244579#p244579
_.. IsArray( ) etc . ... https://www.excelforum.com/excel-new-users-basics/1098177-store-array-created-by-vba-macro-internally-for-use-in-same-vba-macro-by-next-run-or.html#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

[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]

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/viewtopic.php?f=27&t=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/development-testing-forum/1101544-thread-post-appendix-no-reply-needed-please-do-not-delete-thanks-10.html#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-questions/899604-passing-variables-between-subs-get-byref-argument-error-2.html#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-cooler/1174400-would-like-to-know-about-the-forum-experts-gurus-4.html#post4614261
http://www.eileenslounge.com/viewtopic.php?f=27&t=22512&sid=0953c2f407a745eba625ea8e36d9f312
https://www.excelforum.com/the-water-cooler/1174400-would-like-to-know-about-the-forum-experts-gurus-4.html#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.php?f=30&t=31608&p=244579#p24457
https://www.excelforum.com/excel-programming-vba-macros/1325983-null-vs-empty-vs-0-length-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/development-testing-forum/1101544-thread-post-appendix-no-reply-needed-please-do-not-delete-thanks-10.html#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-users-basics/1058406-range-dimensioning-range-and-value-referencing-and-referring-to-arrays-5.html#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-introduce-yourself/1214555-an-old-geezer-coming-over-from-the-access-forum.html

[DocAElstein]

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.php?f=30&t=31608&p=244579#p24457
https://www.excelforum.com/excel-programming-vba-macros/1325983-null-vs-empty-vs-0-length-string.html
https://www.youtube.com/watch?v=jTmVtPHtiTg&t=560s


http://www.excelfox.com/forum/showthread.php/2157-Re-Defining-multiple-variables-in-VBA?p=10192#post10192

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

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

https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg.9xhyRrsUUOM9xoT7VkGX 9w Regarding null - agreed, it's used infrequently in VBA but is much more common in other languages. It's often used (as in this case) to pass "something" to a non-optional parameter. The function being called will be designed with a default behaviour when null is passed to it. In this case you could achieve the same result by passing a reference to the folder object, so these two lines produce the same result:

Sheet1.Cells(r, c + 1).Value = fol.GetDetailsOf(null, c)

Sheet1.Cells(r, c + 1).Value = fol.GetDetailsOf(fol, c)