Environ("username") - compile error Excel 2010

[MerkDog]

Hi,

The following code used to work fine for me in Excel 2003:

MsgBox Environ("username")

In 2010, I get a compile error: "cannot find project or library". I get the same error on trying to use the Chr function, which makes me think it must be something obvious...but I can't figure it out.

Thanks
MerkDog

Excel 2010, Windows XP

 

[VoG]

Check for missing references http://www.cpearson.com/excel/MissingReferences.aspx

 

[MerkDog]

Thank you very much Peter, all sorted now.

All the essential references from Chip Pearson's page were present, but there was a missing reference to Microsoft Calendar Control 11.0.

Restoring this reference (copying the file from a colleague - maybe not the ideal solution) fixed the problem.

Thanks again,
MerkDog

 

[Jaafar Tribak]

How is that line of code causing that "missing reference" error ? !!

 

[RoryA]

It's the other way round. The missing reference causes the problem when the compiler tries to work out where the functions (like Chr) are located. If you specifically use VBA.Chr then the reference is not a problem (Until you try and refer to the Calendar control of course)

 

[Greg Truby]

How is that line of code causing that "missing reference" error ? !!

It isn't. That's what drives you nuts when you're missing a reference. The compiler seemingly picks some line of code completely at random and tells you this is where the error is, when in fact there is nothing wrong with the line of code cited by the compiler. Unless you know to look for a busted reference you can spend hours pouring over perfectly good code (voice of experience here).

The Calendar Control is one of those that can frequently be a culprit since (AFAIK) it only gets distributed with Access unless you make an effort to deploy it manually/seperately.

 

[Jaafar Tribak]

Thanks for the explanation guys . Now that you mention this I recall my code erroring out in one of my workbooks unless I fully qualified native VBA Methods . I never bothered to explore that problem and I am sure I would never have expected the culprit to be a missing reference.

 

[Greg Truby]

...I recall my code erroring out in one of my workbooks unless I fully qualified native VBA Methods...

Here's one that will drive you to

In Excel 2007 create a workbook with any userform and code this:

Sub foo()
    UserForm1.Show VBA.FormShowConstants.vbModal
End Sub

Now - try to run that code in Excel 2010. It will not compile with the fully qualified constant reference even though the Object Browser in 2010 will list these constants as existing in the VBA library.

The fix is easy enough - simply remove the full reference and just code vbModal. But I have yet to comprehend this.

 

[Jaafar Tribak]

Here's one that will drive you to

In Excel 2007 create a workbook with any userform and code this:

Sub foo()
    UserForm1.Show VBA.FormShowConstants.vbModal
End Sub

Now - try to run that code in Excel 2010. It will not compile with the fully qualified constant reference even though the Object Browser in 2010 will list these constants as existing in the VBA library.

The fix is easy enough - simply remove the full reference and just code vbModal. But I have yet to comprehend this.

Thanks Greg. I cannot try the code as I don't have Excel 2010 but I wouldn't be surprised for I heard excel 2010 is buggy.

 

[RoryA]

Greg,
In 2010 you can of course just use:

Sub foo()
    UserForm1.Show VBA.[__MIDL___MIDL_itf_m_0001_0016_0001].vbModal
End Sub