What datatype is Public MyVariable

[SachAccess]

Hi,

I come from Excel VBA background. Usually when I declare any public variable, I write in below manner.
'Public MyVariable as Long' (long is just as an example).
I have not used ''Public MyVariable'. May be am not aware of it. Seeing this first time in MS Access DB.
What datatype is this. Is it a norm, why declaring datatype is skipped here,.
Can anyone please help me in this.
Thanks.

 

[gemma-the-husky]

if you don't give it a type, the type assigned is variant (that's the actual data type name). It works more like an object.

Variant data type | Microsoft Docs

 

[arnelgp]

If you define a Public variable in Form/Report code, then it is not public at all since it only Exists as long as the form/report is open.
when you declare it Inside a Module, it is public and can be changed in any VBA code (form/report or module or macro).

 

[SachAccess]

if you don't give it a type, the type assigned is variant (that's the actual data type name). It works more like an object.

Variant data type | Microsoft Docs

Got it. Instead of writing Dim ABC as variant just DIM ABC. Thanks.

 

[SachAccess]

If you define a Public variable in Form/Report code, then it is not public at all since it only Exists as long as the form/report is open.
when you declare it Inside a Module, it is public and can be changed in any VBA code (form/report or module or macro).

Thanks a lot for the help!

 

[Galaxiom]

If you define a Public variable in Form/Report code, then it is not public at all since it only Exists as long as the form/report is open.

Of course it is Public since it can be referred to from anywhere in the project. Similarly a variable in a Standard Module can be declared Private and its scope will be limited to inside the module.

I prefer to always be explicit but IIRC, a Dim statement in an Object Module will default to Private while a Dim in a Standard module will default to Public. Global can be used in a Standard Module and it means the same as Public.

 

[gemma-the-husky]

Got it. Instead of writing Dim ABC as variant just DIM ABC. Thanks.

In general variants are to be avoided. It's much better to type your variables. You can get hard to trace errors with variants.

 

[SachAccess]

In general variants are to be avoided. It's much better to type your variables. You can get hard to trace errors with variants.

I agree with you. Thanks.

 

[Cotswold]

Personally I do not use public / global variables. If I need a variable to exist for the time the program is in use and to be accessed from any part of the application, then I store those variables on an always-open form. Some are written on start-up and others can be written from various sections of the program as and when necessary. I saw an advantage from the early days of Access in that less memory is in use. Additionally, when some of my forms are in use, I will also store transient variables on a form in a set so that any sub forms may use them. Being able to stick variables onto forms is unique to Access and a very useful feature. (Well I think it is unique but I'm not at all sure. It is just that I haven't come across it in any other software I have used) Another not often considered place to store variables, is of course in a Table.

Which is the better place is probably only a matter of the developer's opinion.

 

[Galaxiom]

I'm not a fan of Public variables on any object, Best practice is to declare them Private and present them as Property of the form where arguments presented to them can be validated, throwing an error at the call rather than inside their parent.

Reading or writing any kind of external variable from a procedure is a very poor practice. Everything a procedure requires should be passed to it through parameters.

 

[MajP]

Being able to stick variables onto forms is unique to Access and a very useful feature. (Well I think it is unique but I'm not at all sure. It is just that I haven't come across it in any other software I have used)

Actually the opposite is probably true. All the Office applications that support vba (Excel, Powerpoint, Outlook, Word) can either support User Forms or their GUI (worksheet, document, slide, etc) supports vba and scripting. I cannot think of any software application that has forms and a scripting language that does not support code and variable declaration.

 

[Cotswold]

Actually the opposite is probably true. All the Office applications that support vba (Excel, Powerpoint, Outlook, Word) can either support User Forms or their GUI (worksheet, document, slide, etc) supports vba and scripting. I cannot think of any software application that has forms and a scripting language that does not support code and variable declaration.

I was thinking of procedural languages actually. However, I would never think of any of those Office applications as being a development environment to create a business system, with the grudging acceptance of Excel maybe being an exception in some instances.

 

[The_Doc_Man]

Got it. Instead of writing Dim ABC as variant just DIM ABC. Thanks.

My comment is narrowly directed towards a DIM without a datatype. You can declare DIM X and get a perfectly usable X variable that is of Variant data type. But the down side of Variant data types is that you can SILENTLY change the data type of X without realizing it, because a Variant is totally amorphous. When you fail to use Option Explicit then it is also possible to silently create a Variant.

When working with a Variant, you lose the implied protection of knowing if you changed the data type stored in that variable. Oh, of course the VBA expression analyzer will convert numerics for you such as DOUBLE to LONG INTEGER or such like that - but you lose notification of trying to make an object into a scalar variable, or vice versa. And that is a dangerous situation. You could kill a recordset or an Excel Application Object without realizing it. With explicit type declaration, the VBA compiler will flag that, and if you disguise it, the run-time expression code will detect abuses, usually with Data Type Mismatch errors. For Variant data types, you are on your own.

Now, a side note... Yes, technically a PUBLIC variable in a class module is, indeed visible across module boundaries. However, we see questions a few times a year on error 91, Object not set, or other similar errors relating to "can't find it" - because the VBA programmer forgot that that class module is not, by default, permanent within a session. It has an implied lifetime unless you take pains to preserve it. With a general module, you don't need to take pains.

 

[MajP]

I was thinking of procedural languages actually

Now I am even more confused. What is an example of a development environment that does not? Anything I think of that I can build in Visual Studio regardless of language is going to have forms and form level code.

 

[SachAccess]

Thanks a lot @The_Doc_Man for the help. I use Option Explicit in my Excel VBA code by habit.
Am aware that forces me to declare every object however I must confess that I am not aware of exact impact of not declaring.
Just use as a safe practice.

 

[The_Doc_Man]

Thanks a lot @The_Doc_Man for the help. I use Option Explicit in my Excel VBA code by habit.
Am aware that forces me to declare every object however I must confess that I am not aware of exact impact of not declaring.
Just use as a safe practice.

By not declaring, you run the risk that a spelling error or typo will create a "phantom" variable that doesn't contain ANYTHING (initialized to 0 or "" or NOTHING) but you reference it and get a funky answer.

<<<no OPTION EXPLICIT>>>

DIM Antidisestablishmentarianism
...

Antidisestablishmentarianism = TRUE

...

IF Antidisestablishmetarianism THEN ...
    Application.Quit
ELSE
    ...do something else
END IF

What will you do when you get to the IF-block? This is an example of big-variable hypnosis. For long variable names you see what you wanted to see, not always what was actually there.

 

[MajP]

Am aware that forces me to declare every object however I must confess that I am not aware of exact impact of not declaring.

Lots of bad things can happen, and it is really hard to debug.

Imagine you have a variable called "PersonName" and you did a good job of declaring it.
dim PersonName as string

Now you want to set it to "John"
PersonName = "John"
is how you should do it since it is a literal, but assume you forgot your quotes (easy to do I know)
PersonName = John
If you do not require variable declaration it will see John above as a variable. And since John does not equal anything it will just be the default empty string. You will not get any errors, because it thinks that is what you wanted to happen. It will be near impossible to catch this error since the code will run fine. The only way to find that error is to see your mistake.

Another one is the spelling mistake or typo
intInput = 10
Output = inInputt + 20

Since there is a spelling/typo mistake in the second line. It will think that "inInputt" is a new variable and will have no idea you meant intInputt. The above result of Output is 20. Nearly impossible to debug unless you can see the spelling error.

 

[SachAccess]

@MajP and @The_Doc_Man thanks a lot for helping me understand so many things with your detailed posts.
Have a nice weekend.

 

[gemma-the-husky]

Lots of bad things can happen, and it is really hard to debug.

Imagine you have a variable called "PersonName" and you did a good job of declaring it.
dim PersonName as string

Now you want to set it to "John"
PersonName = "John"
is how you should do it since it is a literal, but assume you forgot your quotes (easy to do I know)
PersonName = John
If you do not require variable declaration it will see John above as a variable. And since John does not equal anything it will just be the default empty string. You will not get any errors, because it thinks that is what you wanted to happen. It will be near impossible to catch this error since the code will run fine. The only way to find that error is to see your mistake.

Another one is the spelling mistake or typo
intInput = 10
Output = inInputt + 20

Since there is a spelling/typo mistake in the second line. It will think that "inInputt" is a new variable and will have no idea you meant intInputt. The above result of Output is 20. Nearly impossible to debug unless you can see the spelling error.

Great examples. We all make typos like these, except that we detect them with option explicit. I didn't consciously appreciate that variables used on the rhs of an assignment would be created by the compiler.

 

[Galaxiom]

One of the great features of the Visual Studio based editor used for SQL Server Management Studio is that strings are a different colour (red by default).

And there is no Option Inexplicit in T-SQL.

VBA is a permissive language designed for the inexperienced.