By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,710 Members | 1,601 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,710 IT Pros & Developers. It's quick & easy.

VBA Access - Moving parts of a program to DLLs? Error 91.. "Object variable or With block variable not set"

P: n/a
I am trying to move some of our large VBA Access front-end file into
ActiveX DLL files. I created two DLL files so far, one was a module
that contains code to integrate into the QuickBooks accounting
software. Another has general utilities.

I tried referencing the utilities dll, and it shows up in the object
explorer. I instantiated an instance of the class and now it shows up
all okay in the Intellisense. Whenever I try to run a simple function,
though, even a subroutine that just does:

MsgBox "Test"

It gives me the error 91: Object variable or With block variable not
set

Am I missing a step here? We really need these DLLs, or some
alternative, so that we can better use Subversion for our main project.

Jul 21 '06 #1
Share this Question
Share on Google+
9 Replies


P: n/a
"axs221" <ax****@gmail.comwrote in
news:11*********************@i42g2000cwa.googlegro ups.com:
I am trying to move some of our large VBA Access front-end file
into ActiveX DLL files.
Why in the *world* would you do such a thing?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jul 21 '06 #2

P: n/a
Do you have a better idea? We need to split apart our main program,
which is ALL in one database file. It defeats part of the purpose of
using Subversion to have only one file. It takes too long to
synchronize and doesn't allow more than one person to work on the file
without having to later merge bits and pieces together. A separate file
like a DLL library would allow me to work on my Quickbooks integration
portion of the code while the other programmer could work on whatever
he's working on. If there's a better way to do it in VBA then feel free
to enlighten me.

David W. Fenton wrote:
"axs221" <ax****@gmail.comwrote in
news:11*********************@i42g2000cwa.googlegro ups.com:
I am trying to move some of our large VBA Access front-end file
into ActiveX DLL files.

Why in the *world* would you do such a thing?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jul 21 '06 #3

P: n/a
On 21 Jul 2006 14:15:43 -0700, "axs221" <ax****@gmail.comwrote:

First set a reference (code window Tools References) to one of
your DLLs. At that point it would show in Intellisense and Object
Browser.
Then create an object, and call its methods. For the Utilities DLL,
you probably would have a global object:
(in a standard module)Global g_objUtil as Utilities.Utils
(in your startup code)Set g_objUtil = New Utilties.Utils
(in the rest of your code)Msgbox g_objUtil.DoSomething()
(in your exit code)Set g_objUtil = Nothing

Unlike David I don't think modularization in ActiveX DLLs is a bad
idea to support multiple-developer development (MDD). One thing that
might possibly prevent me from doing it is that you rely on VB6 and
ActiveX, which are aging technologies.
I would definitely not encourage MDD using SourceSafe integration.

-Tom.

>I am trying to move some of our large VBA Access front-end file into
ActiveX DLL files. I created two DLL files so far, one was a module
that contains code to integrate into the QuickBooks accounting
software. Another has general utilities.

I tried referencing the utilities dll, and it shows up in the object
explorer. I instantiated an instance of the class and now it shows up
all okay in the Intellisense. Whenever I try to run a simple function,
though, even a subroutine that just does:

MsgBox "Test"

It gives me the error 91: Object variable or With block variable not
set

Am I missing a step here? We really need these DLLs, or some
alternative, so that we can better use Subversion for our main project.
Jul 22 '06 #4

P: n/a
You know, I think I actually left out the constructor call, silly me.
That message would make sense if the object wasn't instantiated... I
was just happy to see the object in the Intellisense.

Thanks, I'll have to give that a try on Monday.

Tom van Stiphout wrote:
On 21 Jul 2006 14:15:43 -0700, "axs221" <ax****@gmail.comwrote:

First set a reference (code window Tools References) to one of
your DLLs. At that point it would show in Intellisense and Object
Browser.
Then create an object, and call its methods. For the Utilities DLL,
you probably would have a global object:
(in a standard module)Global g_objUtil as Utilities.Utils
(in your startup code)Set g_objUtil = New Utilties.Utils
(in the rest of your code)Msgbox g_objUtil.DoSomething()
(in your exit code)Set g_objUtil = Nothing

Unlike David I don't think modularization in ActiveX DLLs is a bad
idea to support multiple-developer development (MDD). One thing that
might possibly prevent me from doing it is that you rely on VB6 and
ActiveX, which are aging technologies.
I would definitely not encourage MDD using SourceSafe integration.

-Tom.

I am trying to move some of our large VBA Access front-end file into
ActiveX DLL files. I created two DLL files so far, one was a module
that contains code to integrate into the QuickBooks accounting
software. Another has general utilities.

I tried referencing the utilities dll, and it shows up in the object
explorer. I instantiated an instance of the class and now it shows up
all okay in the Intellisense. Whenever I try to run a simple function,
though, even a subroutine that just does:

MsgBox "Test"

It gives me the error 91: Object variable or With block variable not
set

Am I missing a step here? We really need these DLLs, or some
alternative, so that we can better use Subversion for our main project.
Jul 22 '06 #5

P: n/a
"axs221" <ax****@gmail.comwrote in
news:11*********************@i3g2000cwc.googlegrou ps.com:
David W. Fenton wrote:
>"axs221" <ax****@gmail.comwrote in
news:11*********************@i42g2000cwa.googlegr oups.com:
I am trying to move some of our large VBA Access front-end file
into ActiveX DLL files.

Why in the *world* would you do such a thing?

Do you have a better idea? We need to split apart our main
program, which is ALL in one database file. It defeats part of the
purpose of using Subversion to have only one file. It takes too
long to synchronize and doesn't allow more than one person to work
on the file without having to later merge bits and pieces
together. A separate file like a DLL library would allow me to
work on my Quickbooks integration portion of the code while the
other programmer could work on whatever he's working on. If
there's a better way to do it in VBA then feel free to enlighten
me.
I know of Access projects using SourceSafe and such products for
this.

I don't see the issue. Don't those products work for you?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jul 22 '06 #6

P: n/a
Bri
axs221 wrote:
Do you have a better idea? We need to split apart our main program,
which is ALL in one database file. It defeats part of the purpose of
using Subversion to have only one file. It takes too long to
synchronize and doesn't allow more than one person to work on the file
without having to later merge bits and pieces together. A separate file
like a DLL library would allow me to work on my Quickbooks integration
portion of the code while the other programmer could work on whatever
he's working on. If there's a better way to do it in VBA then feel free
to enlighten me.
From this response I get the idea that you are using Replication with
this app? And that the UI and data are in the same MDB? If these
assumptions are so, then you should look at splitting the database and
only replicating the data. Replication of the application objects is
very prone to problems. If these assumptions are not so, then we will
move on to Libraries.

You can set up a MDB file with all the code you want to separate from
the main app. Then you can create an MDE from the MDB that you can then
reference in the main app. This would keep all the coding in the same
environment.

--
Bri

Jul 24 '06 #7

P: n/a
We use replication for our data, but our data is in a separate MDB
file. As for libraries, I do want to move some code into other
libraries, but with how my company has our code set up it isn't too
smooth of a transition.

I've made a separate MDB file now for one module in our program, but
now the issue is trying to handle function calls that were in other
sections of the main database of the program, along with any queries or
the few globals that we use for keeping track of certain files and
things. Since the separate MDB can't refer back to the main program, I
created a third library for the calls that both databases need, but
this is kind of a hassle and might get messy. Is there a better way of
handling that?

Shawn

Bri wrote:
axs221 wrote:
Do you have a better idea? We need to split apart our main program,
which is ALL in one database file. It defeats part of the purpose of
using Subversion to have only one file. It takes too long to
synchronize and doesn't allow more than one person to work on the file
without having to later merge bits and pieces together. A separate file
like a DLL library would allow me to work on my Quickbooks integration
portion of the code while the other programmer could work on whatever
he's working on. If there's a better way to do it in VBA then feel free
to enlighten me.

From this response I get the idea that you are using Replication with
this app? And that the UI and data are in the same MDB? If these
assumptions are so, then you should look at splitting the database and
only replicating the data. Replication of the application objects is
very prone to problems. If these assumptions are not so, then we will
move on to Libraries.

You can set up a MDB file with all the code you want to separate from
the main app. Then you can create an MDE from the MDB that you can then
reference in the main app. This would keep all the coding in the same
environment.

--
Bri
Jul 25 '06 #8

P: n/a
Bri
axs221 wrote:
We use replication for our data, but our data is in a separate MDB
file. As for libraries, I do want to move some code into other
libraries, but with how my company has our code set up it isn't too
smooth of a transition.
Good, for a while there I thought you had been using replication to
distribute code changes. That was one of the original uses of
Replication, but it was so prone to problems that anyone in the know
about Replication now strongly recommends against it.
I've made a separate MDB file now for one module in our program, but
now the issue is trying to handle function calls that were in other
sections of the main database of the program, along with any queries or
the few globals that we use for keeping track of certain files and
things. Since the separate MDB can't refer back to the main program, I
created a third library for the calls that both databases need, but
this is kind of a hassle and might get messy. Is there a better way of
handling that?
I'm by no means an expert on Libraries, but I would think that if there
is something in the main program that the library function needs to know
about (variable, object, etc) that if you were to include it in the
parameters of the function call that would take care of things. This is
very similar to moving a function from a Form module, that has all sorts
of Me referrences, to a stand alone module (so it can work with more
than one form). For a simple function, I'll show what I mean with some
air code:

Code in Form:
Function ControlChanged() As Boolean
If Me!MyText.Value<>Me!MyText.OldValue Then
ControlChanged = True
Else
ControlChanged = False
End If
End Function

Code in Module:
Function ControlChanged(frm As Form) as Boolean
If frm!MyText.Value<>frm!MyText.OldValue Then
ControlChanged = True
Else
ControlChanged = False
End If
End Function

Or if you need to be able to refer to any control:
Function ControlChanged(ctr As Control) as Boolean
If ctr.Value<>ctr.OldValue Then
ControlChanged = True
Else
ControlChanged = False
End If

So, if you pass the appropriate Parameters, you should be able to make
the functions in the Library able to work even though they are isolated
from the main app. You mentioned Global Variables as well. If the
function only needs to refer to a few of them you can include them in
the parameters. There are a number of ways you could pass them all at
once if you needed to have the function be able to refer to any/all of
them. You could load them into an array ( GlobalArray(varName, varValue)
) and pass the array as a parameter. You could load them into a
Collection. Or for the ultimate in flexibility (and complexity too) you
could load them into a Class.

Obviously, the code that is the best candidate for a Library is code
that can easily stand alone with the minimum of parameters, but that
doesn't mean you can't deal with anything else. There will be a limit to
what you can practically move to the Library.

Hope that helped.

--
Bri

Jul 25 '06 #9

P: n/a
"axs221" <ax****@gmail.comwrote in
news:11**********************@b28g2000cwb.googlegr oups.com:
I've made a separate MDB file now for one module in our program,
but now the issue is trying to handle function calls that were in
other sections of the main database of the program, along with any
queries or the few globals that we use for keeping track of
certain files and things.
Don't use globals.

Seriously.

There are simply very few circumstances in which a global variable
is a good way to share data. A global constant is a different
animal, and perfectly OK. If you've got lots of those, you could do
what Bri suggested, pass a structure to the code in the library.

I've used libraries plenty of times and this has just never been an
issue. Any code that's in a library oughtn't be dependent on globals
of any kind, it seems to me. That's an obvious architectural
requirement, it seems to me.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jul 25 '06 #10

This discussion thread is closed

Replies have been disabled for this discussion.