Excel VBA Declarations (1 Viewer)

[JMongi]

I'm used to putting "Option Explicit" and "Compare Database" at the top of my VBA.
What is the comparable declarations for Excel VBA.

 

[GPGeorge]

Compare Database isn't applicable for Excel because it's not a Database.
Option Explicit, on the other hand, is a VBA specific declaration which requires that all variables must be Dimmed before you can use them in a Function or Sub.
Inasmuch as VBA is common to all Office applications, albeit with different sets of actions and methods, it has the same requirements in Excel as in Access.

 

[JMongi]

I knew that "Compare Database" wouldn't be applicable. But, there is no counterpart to this declaration in Excel?

I should have specified I was already going to use Option Explicit and was more asking about the the other declaration. Thanks for the response.

 

[CJ_London]

This link tells you about all the options ‘options’

Option Compare statement (VBA)

Office VBA reference topic

learn.microsoft.com

 

[Isaac]

good question. could be the reason it doesn't come up much is b/c most people are satisfied with the default text compare option and no additional option statement needed (in excel).

honestly - no sarcasm - i say this not as an expert in this topic. but it's the impression i get.

option explicit yes yes yes good job always always always

 

[Isaac]

This reminds me Dear Microsoft, can you please make ADO available in Excel without having installed Access - in an easy clear way. Just standard with Excel.

 

[ebs17]

ADO is its own library and of course available without MS Access installed. It should be available with any common version of Windows. If you know your way around and use Late Binding right away, you don't even have to set a reference.

 

[Isaac]

How do you install it by itself? i wanted it to be 'easy' but have found it complicated in the past (at least when communicating the request to IT gods)

Yes, I love using late bound ADO in excel, but in the past it has only worked when I.T. has installed Access for people - (and on my PC as well)

 

[ebs17]

Gods can lie or know nothing.
In VBA, set a reference to Microsoft AxtiveX Data Objects x.x Library, 6.1 should be the highest version. You can use this to check whether it is available via early binding.

 

[Isaac]

Gods can lie or know nothing.
In VBA, set a reference to Microsoft AxtiveX Data Objects x.x Library, 6.1 should be the highest version. You can use this to check whether it is available via early binding.

Ahh - you're just talking about references.

I'm not talking about that, I don't even use references for ADO in Excel. I'm talking about it doesn't actually WORK - i.e., isn't there - without an Access install.

If you can prove that wrong I'll be interested (and happy).

 

[MajP]

Is it a matter of downloading MSADO15.dll? I am guessing, do not know if sufficient.

 

[ebs17]

Since the days of Windows 2000 and WinXP (with the introduction of Office 2000), ADO has been supplied automatically. What software do you have that should be different? Search for msado15.dll (on your computer, then also download and register).

 

[Isaac]

I have Windows 11, and have recently had Windows 10 and XP and I forgot the other one of note, but have never gotten ADO to work in Excel without installing Access - (i.e. if I only have Excel installed).

I did find msado15.dll on my current Windows 11 laptop, inside all 4 of these:

C:\Program Files (x86)\Common Files\System\ado
C:\Windows\WinSxS\wow64_microsoft-windows-m..ents-mdac-ado15-dll_31bf3856ad364e35_10.0.22000.1_none_6cc92a7ea9d08641
C:\Program Files\Common Files\System\ado\msado15.dll
C:\Windows\WinSxS\amd64_microsoft-windows-m..ents-mdac-ado15-dll_31bf3856ad364e35_10.0.22000.1_none_6274802c756fc446

I'll have to uninstall Access, try my ADO code again and see how it goes I'll post back