[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 911
  • Last Modified:

#Const directives in VBA

Hey Experts. I'm new to #Const directives and am not sure how they work. I am researching them now because I want to utilize late binding of needed references in an Access application because the application is being used by users with both 32 and 64 bit machines and the folder structures are different so many dlls are missing on some.

I could also not use late binding if I could figure out how to reference a shared folder containing the necessary references -- but for some reason when I try to change the reference location it is ignored/not changed. I know I was able to do this before... don't remember how I got it to work. Do I need to re-register them in their new location?

So I guess what I'm asking is -- which way would be best/easiest. This is not a distributed application and the number of users is only at most a dozen.

The users click on a short-cut to a batch file which copies the front-end to their local machine retaining links to the back-end tables. I know in the past I have also copied the required reference files in the batch file and registered them on the fly. I can't find a copy of that file -- but maybe that's another solution?

Any advise/assistance/help will be much appreciated.

Thanks a lot!

Eileen
0
Eileen Murphy
Asked:
Eileen Murphy
  • 5
  • 3
  • 3
8 Solutions
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Eileen,

 A constant is a why of having a consistent value available.  Normally their used to clairify the meaning of something.

 For example, when calling a routine or API, the call may be for a numeric value. i.e.

  SetModemResponse(x)

where X =
1 = No response
2 = Normal response
3 = verbose responses

and rather then do:

SetModemResponse(2)

 you can do:

Private Const ModemResponseNone As Integer = 1
Private Const ModemResponseNormal As Integer = 2
Private Const ModemResponseVerbose As Integer = 3

and now your call becomes:

SetModemResponse(ModemResponseNormal)

 which is a lot clearer.  The above can also be done better with enumerations, but it gives you the idea.

#Const is a compile directive and will be executed if it's within a #IF, so if you were dealing with a 32 /64 bit app and did this:

  #IF vba7 = True
     #Const debug = "64 bit"
  #Else
     #Const debug = "32 bit"
  #EndIf

You best bet is to use late binding (no references set) and then you don't have to worry about it (unless your making direct API calls).

Jim.
0
 
Eileen MurphyIndependent Application DeveloperAuthor Commented:
Is there a list of #Consts? I ask because I guess I'm not clear about how to actually do the binding... still researching...
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<Is there a list of #Consts? >>

   There are a few built-in constants(VBA7, Win16, Win32, Win64, etc), but not many and they depend on version. Constants are values you typically assign.

  Also please note that I said the #Const explaination backwards; #Const defines a constant for use within #IF/#Else/#EndIf structure, NOT the other way around.

<< I ask because I guess I'm not clear about how to actually do the binding... still researching... >>

 Binding is a seperate issue.

 When you late bind, you declare a object generically:

 Dim objXLS as Object

 Set objXLS = CreateObject(Excel.Application).

 When your early bind, you do it explicitly:

 Dim objXLS as Excel.Application

 Set objXLS = New Excel.Application

 In which case, a reference needs to be set.  When you early bind, VBA knows what your talking about because you set the reference.

  When you dim something as Excel.   VBA goes looking through the reference list for the Excel object lib, and then tries to find the Application object in that.

But with late binding, not so.  Since you've simply said "Object", it just knows some object is going to go in there, but it doesn't know which one yet.

With late binding, everything is looked up at runtime.  With early, it's looked up at design time.


Jim.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
A couple of other notes:
With early binding, you get Intellisense. With Late Binding, you don't.

With Late Binding, there is the potential for a performance hit - however, I suspect with today's systems, that may be down in the mud ... unless there is a LOT of Late Binding in a particular chunk of code.

With Late Binding, References become a non-issue, and in theory you code works in future versions.

So, many of developers use Early Binding during development to get Intellisense, then switch to Late Binding for Deployment ... because it's just a couple of lines of code that need to be changed - in general.

mx
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
@mx,

<<With Late Binding, there is the potential for a performance hit - however, I suspect with today's systems, that may be down in the mud ... unless there is a LOT of Late Binding in a particular chunk of code.>>

 It's about 15%, but I think your right; with today's systems, unless your doing a lot of it, the performance hit is worth it just to get away from the problem with references.

Jim.
0
 
Eileen MurphyIndependent Application DeveloperAuthor Commented:
THANK YOU SO MUCH FOR RESPONDING. I understand it much better... These are the references the application currently uses (attached screen shot) and the problematic ones are the version specific and the one that is not being referenced which caused the initial problem is msado15.dll -- I unchecked it to try to re-reference it from a new shared folder, but it won't let me reference it-- which was the reason for my posting this question in the first place.

Anyway --- which of these references should I early/late bind to??
Screenshot.pdf
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
So, 15% of 100 milliseconds ... yep, could be huge, lol

I don't see any of those References as a real problem, as they are all pretty standard .... and in theory should ... 'adjust' upward to new version. for example 12.0 Access DB Engine to 14.0 (Access 2010).

mx
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
References in VBA to put it bluntly are a royal pain in the ***.  

In latter versions, some of the references get automatically updated and you get no warning that it has done so.

Also, as you have found, unless a reference is in the exact same place, same version, etc, the reference breaks and you have an app that doesn't work.

<<Anyway --- which of these references should I early/late bind to??>>

 Unfortunately, all the ones you show except the last need to be there.  The one your have a problem with (ADO) can be late bound, but you would not want an app to run like that with the performance hit because you'd use it a lot.

  The question is; do you need it?  Uncheck it and try to compile the app.  If you get a clean compile, then it's not needed and can be left un-checked.

 Same for the Office 12.0 Database Engine Object, which is DAO.  If your not using DAO in code, then you may not need it.

 The last reference was for backwards compatibility with some older code; try unchecking it and see if you can live without it.

 All the rest are required and needed and you really don't want to (or can't) late bind to those.

Jim.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
BTW on this:

<<
The users click on a short-cut to a batch file which copies the front-end to their local machine retaining links to the back-end tables. I know in the past I have also copied the required reference files in the batch file and registered them on the fly. I can't find a copy of that file -- but maybe that's another solution?
>>

 the command to register a DLL or type lib is:

C:\Windows\Systrem32\regsvr32.exe   <path to DLL/OCX>


 And with all that said, looking back, what is it exactly your having an issue with?  The ADO reference looks good and should be working fine.

Jim.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Also, you probably do not need the OLE reference, unless you are specifically using automation.  I always uncheck that reference.

mx
0
 
Eileen MurphyIndependent Application DeveloperAuthor Commented:
Thank you for the education... I feel much better about approaching this now!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now