MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

VBA Excel Project to Users

  Asked By: Egidius    Date: Aug 13    Category: MS Office    Views: 1304

I've sent an Excel form with macros to users and when they open it and
enable macros they don't get the form; rather they get:

Compile Error: can't find project or library.

I compile the VBA (Debug:Compile), save the project, save the .xls file, and
when I open it again, Compile Project is highlighted...what am I missing to
port this out to users?



12 Answers Found

Answer #1    Answered By: Abejundio Garcia     Answered On: Aug 13

You may be missing  a referenced Project or Library. (Surprise!)
What you should do is check out Tools/References and see what Projects &
Libraries are checked, and most likely one of those is not present on
your users' machines.
It's a common issue, and one that does need to be corrected for your
form to work correctly.

Answer #2    Answered By: Uma 1985     Answered On: Aug 13

I'm using one measly doodad from atpvbaen: Current Region
and I have to tell each user to go and install this library?...My next
question, then, is how do I build an install wizard so usability is
seamless? Is it possible to copy the routine for
ActiveCell.CurrentRegion.Rows.Count into my module or form  code?

This is, of course, is rational thinking--awaiting a portentous discovery...

Answer #3    Answered By: Angelina Gardner     Answered On: Aug 13

ATPVBAEN - The MS Analysis TookPak for Excel.
Yes, that will have to be on each machine on which you want to use its
features. If that's not easy, and you're only using the one function
(CurrentRegion) you might try writing something to do the job of
CurrentRegion. I'm not sure if the source code for it is available.
MAYBE if you check out the ATPVBAEN.XLA file  from VBA, it's got complete
unlocked sourcecode, but I wouldn't count on it, but it's possible...! I
haven't checked into it.
There are many ways to install templates, I can't go into it right

Answer #4    Answered By: Dalpat Student     Answered On: Aug 13

If you do a Google on ATPVBAEN you find  lots of info. Not sure if it's
helpful info, but one can dream... Also tries "ATPVBAEN source code".

Answer #5    Answered By: Priscilla Lewis     Answered On: Aug 13

One user opened Excel Tools > Add-Ins and checked the two addins, and still
got a failure (Can't find project  or library) when launching the .xls file  I

Would the following work for users  of a forms project I send for general use
(they have various versions of Office, which includes Excel):

Public Sub Auto_Open()
AddIns("Analysis ToolPak").Installed = True
AddIns("Analysis ToolPak - VBA").Installed = True
End Sub

Or is it also necessary for them to do something explicitly in VBA? (i.e.,
set up the Project Reference)...

Answer #6    Answered By: Delbert Cooper     Answered On: Aug 13

Well, it's strange that if they were checked on the user's machine that
it didn't work.

The VBA code you listed may work, you should maybe check Word VBA Help
for more info on installing global template addins, I haven't looked
into it recently. Certainly, the XLA file  must exist on their machine at
a minimum...

Answer #7    Answered By: Myron James     Answered On: Aug 13

If you look at the list of references (in VBA editor Tools >
References)...there's such a huge list. I have about 10 entries for VB. But
what I DID do was check some of those interesting things like MS Dev Env 7.0
and VB Data View Addin Support (which I had installed).

I learned a valuable lesson: someone somewhere will tell you when to enable
a reference, but until then, don't change any of those checkboxes in the
References dialog box.

I'll have to read up on this References topic.

Answer #8    Answered By: Vidisha Pathak     Answered On: Aug 13

The hard part is when someone sends you some code to help you solve your
problem, and it will not compile, then you have to figure out what
reference is missing, because the code works for them! <sigh> I usually
get caught up trying to determine if I need DAO or ADO references.

Answer #9    Answered By: Barney Smith     Answered On: Aug 13

I discovered that it wasn't the analysis tool pak that caused the problem.
By debugging the project  on the user's machine, I find  it fails on:

datInitDate.Value = Format(Date, "mm-dd-yyyy")
datComplDate.Value = Format(Date + 90, "mm-dd-yyyy")

Format class (method?) works great on my machine, but not on user's--this is
apparently a VB 6.0 function, which explains why I couldn't find it in the
VBA Object Browser.

so now I'm back to:

datInitDate.Value = Date$
(displays as 01-06-2005)
datComplDate.Value = DateAdd("d", 90, datInitDate.Value) (displays
as 4/6/2005)

Which is what prompted me to try to find a cure (slashes vs dashes in 2
dates on same form)

I see a class VbDateTimeFormat listed in VBA Object Browser, but this method
provides 4 preconfigured selections (long, short, and the default general)
which don't seem to apply. Or do I wrap both dates in this and that will
"fix" the slash-n-dash issue.

Or is there a way to enable VB commands on users' machines?

Answer #10    Answered By: Bu Nguyen     Answered On: Aug 13

What version of Office are you using? What Service Pack are you at, and
then what service pack level is your user at? I remember in Access that
Date() was not a supported function in the initial release, but then
Service Pack 1 fixed it. Databases that had worked fine in Office 97
using the Date() function stopped functioning in an Access 2000

Answer #11    Answered By: Alonzo Roberts     Answered On: Aug 13

I'm using Office Standard 2003 with it's Sept 2003 svc pack; users  here have
a range of installations, but the problem arose in Office 2002...so that
must mean the most sophisticated version coming from my machine doesn't work
on anyone else's. I think it's the Format() function in VB that breaks. But
then I wonder how it's possible to build a "robust" solution in VB that DOES
work on users' workstations. (VB 6.0 was installed when I came to this
workstation, though I've only been working in VBA (or so I thought).

It's mind-boggling the Date() function(s) is so poorly
documented--obfuscation, laborious, conundrum--come to mind. I'd thought
dates (today's date plus some date 30 or 40 days, or a year later) on forms
to have been around since the advent of Excel. Seems even programming
languages that 'support' two contiguous versions of Office are more
exclusive than the spoken/written word across diverse cultures.

Answer #12    Answered By: Dion Jones     Answered On: Aug 13

I just went digging through Excel XP and Excel uses Format as
FormatDateTime, which it looks as though Excel 2003 also recognizes. I
know I use Format in Access (both versions) all the time to accomplish
the code you are using, but try the FormatDateTime and see if that works
for you.

Didn't find what you were looking for? Find more on VBA Excel Project to Users Or get search suggestion and latest updates.