Missing References in Excel VB

[brunel]

I am studying Excel VBA Programming from the book "Microsoft Excel VBA Programming for the Absolute Beginner" - the book is good but some of the projects included will not run as there are missing references such as "LCase" and "Format" with the message "Can't find Project or Library" (in the Visual BAsic Editor). Then "references" box is launched indicating that "VB Objects and Procedures" and "Microsoft Multimedia DTCs" are missing. Where do I find these and how do I install them? Any help would be greatly appreciated. If this is not the most appropriate forum could you please direct me to the right one!

 

[BrainB]

Check them via Tools/References in the VB Editor.
May need to install from the MS Office disk. Regards
BrianB
** Let us know if you get something that works !
================================

 

[brunel]

Thanks very much for your response Brian. I have tried this and no go. It appears to be looking for the references in C:\Program Files\Microsoft Visual Studio\Vintdev98 and C:\Program Files\Microsoft Visual Studio\VB98\VB6.OLB which I suspect would be there if I installed Visual Studio which I don't have. If this is the case are these libraries available somewhere? If not what next?

 

[BrainB]

Hi

You are quite right, the message does relate to 'Visual Basic' (or 'Visual Studio'). 'Visual Basic for Applications' really only relates to Microsoft Office. You should therefore be using it via one of its applications such as Word or Excel. It looks like VB/VS has been on your computer at some time. As hinted at before, I don't think VBA is installed in the default Office setup, you have to explicitly select it.

Some things to try :-

1. What version of Microsoft Office are you using ?
2. How are you accessing/opening the Visual Basic (for Applications) Editor ? What Office application are you using ?
3. I am using Office 97. When I open Excel, press Alt+F11 to open the Editor and open menu Tools/References the main ones checked are "Visual Basic For Applications","Microsoft Excel 8.0 Object Library" and "Microsoft Office 8.0 Object Library".
4. How are you using LCase and Format ? Please copy/paste the lines in question to a message.

Regards
BrianB
============================== Regards
BrianB
** Let us know if you get something that works !
================================

 

[brunel]

Thanks once again for your help Brian.

The answers to your questions are:

1. Office 2000 Pro
2. I am using Excel 2000 (9.0.4402 SR-1) and opening the VB Editor either via the toolbar in design mode or Alt+F11.
3. The main ones checked are the same as yours except that they are version 9.0.
4. The code being used is:

userName = LCase(InputBox("What is your first and last name?", "Name&quot)

and

bDay = Format(userBirthday, "dddd&quot
bMonth = Format(userBirthday, "mmmm&quot

userName is declared as a string.
userBirthday as a date.

These are part of the project "Time of your Life" for which the code is included on the CD that comes with the book.


Best wishes,

John

 

[BrainB]

I am at a bit of a loss. The References I gave you were for Excel 97. I also run 2000 where mine match yours and your code behaves as expected.

1. Somehow references to Visual Basic/Studio have appeared on your machine. Have you any idea where they came from ?

2. A likely reason is that you have copied a module from a disk that came with the book. If so it looks like it it is for VB rather than VBA.
(a) Try *typing* the code into the module of a new workbook,and running it.
(b) If (a) works you may be able to copy and paste code from the source disk to a clean module. To make sure it is clean copy/paste into notepad then copy/paste into your module (this is a process to clean corrupted Excel code too).

3. Perhaps the disk contains the references you need. I would not use these because you, presumably, want to learn VBA, not something else.

3. Check out Control Panel "Add or Remove Programs" and remove any that seem suspicious.

Let us know how you get on.

Regards
BrianB
** Let us know if you get something that works !
================================

 

[brunel]

Hi Brian,

Thanks very much for your ideas. I really appreciate your help. I have tried what you suggested with success! Duane Birnbaum, the author of the book replied to me along similar lines.

What I did was copied and pasted the code to a new workbook using the move/copy command in Excel.

Interestingly, it appeared to fix the references in the other projects from the book as well, although some code that I had written based on the book still had the incomplete references. Once again copying to a new workbook solved the problem.

I suspect you are correct and that the "projects" in the book were written and saved in an environment where Visual Studio was also installed - a trap for the unwary.

Cheers,

John.

 

[BillGeake]

John,

There are other possible reasons for this - see Q213524 at MSDN. This describes the fault but the circumstances are different.

I located the DLL that the function (for me, it was Format) needed using the Object Browser and made sure everything was up to date to no avail. I think the error message is wrong in this case.

Whatever the real cause (suspect we will never know) copying the code to a new workbook does seem to be a successful workaround.

Bill.