Public Variable - not defined?

[Guest]

I apologize if this got double-posted. I think I need to go home and take a
nap rather than continue to foul this computer up anymore today with my
presence.

--

I need "findvacdb" to be the same across my entire project so I defined it
in "This Workbook" as Public:

Option Explicit
Public findvacdb As String

Private Sub Workbook_Open()
..
..
findvacdb=S:\Path\to\mdb

When I try to use it in a form, I get the error "Variable not defined".

I'm tired and I just had a big lunch. Maybe it's the hamburger killing brain
cells. But I can't figure this out.

If you need more code, let me know.

Thanks!

 

[Guest]

Do you use the stand alone line of code

End

That line of code kills everything including clearing all global variables.

 

[Guest]

No. But, I do have a few "Exit Sub" in error-handlers. Would these have the
same effect?

I don't know if it matters, and didn't think about mentioning it before. The
code in Module 1 fills a ComboBox on a form (employee numbers). Selecting an
item in the ComboBox executes the next set of code - which is where I'm told
the variable is not defined.

The variable is the path to my database which may differ depending upon the
user.

 

[Dave Peterson]

Try putting this line in a general module.

Public findvacdb As String

(delete then line within the ThisWorkbook module)

or

refer to that variable by its fully qualified name:
msgbox ThisWorkbook.findvacdb

 

[Guest]

That sounds logical. I'll give it a shot.

I thought "ThisWorkbook" would be a general module. In layman's terms: click
Insert|Module, right? Then, at the top of it put my Public declaration?

Seems the easiest way to go.

Thanks!
--
Adios,
Clay Harryman

Try putting this line in a general module.

Public findvacdb As String

(delete then line within the ThisWorkbook module)

or

refer to that variable by its fully qualified name:
msgbox ThisWorkbook.findvacdb

 

[Guest]

A couple of things...
1. When in doubt try being more specific in your referencing
msgbox Module1.findvacdb
2. All users ultimately have the same path to the file. They may have
different drive letters but you can use the full URL. For example your S
drive is mapped something like \\Myserver\Something

In windows explorer you will see the full path besid ethe drive letter or in
Tools -> map directory

 

[Chip Pearson]

I thought "ThisWorkbook" would be a general module. In layman's terms:

click
Insert|Module, right? Then, at the top of it put my Public declaration?

Nope, ThisWorkbook is a class module not a regular module. As such, you must
include its name to access any variable declared in the module. E.g.,

ThisWorkbook.SomeVariable = 123

If you omit the "ThisWorkbook", VBA won't find SomeVariable.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

That sounds logical. I'll give it a shot.

I thought "ThisWorkbook" would be a general module. In layman's terms:
click
Insert|Module, right? Then, at the top of it put my Public declaration?

Seems the easiest way to go.

Thanks!

 

[Guest]

I sincerely appreciate the help. I owe y'all a happy hour or something like
that.

When I got my degree in CS, only linear programming courses were required.
OOP was an elective, which I skipped thinking I wouldn't really need it. Then
I went into Networking and Support instead of programming.

Well, now that I'm (much) older, and I hate lugging PCs and laser printers,
I wanted to get back into programming. This project is my first foray back
into the field. While much of it is still very intuitive, I've come to the
conclusion that I need to return to school to learn about object-oriented
programming. It's not going to stop me from working in the field. It will
only enhance what I have to offer.

Again, thank you so much for all the help you've provided over the last two
months. I would not have been able to complete this project without you.

 

[Guest]

That's going to happen once we decide exactly where all this will reside. For
development purposes I'm using the drive letter.