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

 

[Greg Truby]

Just for anyone that might, someday come across this thread in the future, for the record, I probably would not actually do

    #If VBA7 Then
        UserForm1.Show VBA.[__MIDL___MIDL_itf_m_0001_0016_0001].vbModal
    #Else
        UserForm1.Show VBA.FormShowConstants.vbModal
    #End If

Simply because I would be worried about Microsoft later catching this snafu and fixing it in the next release of Excel and the VBAx compiling constants tend to be "x or better" type constants, i.e. in Excel 2010, VBA6 is TRUE. So if they would fix it, you'd then have a third case to worry about. What I've done is to "throw caution to the wind and live life on the edge" and simply remove the full qualification for code I need to upgrade for 2010 (and I quit using the full qualification when writing new code).

 

[Jaafar Tribak]

Just for anyone that might, someday come across this thread in the future, for the record, I probably would not actually do

    #If VBA7 Then
        UserForm1.Show VBA.[__MIDL___MIDL_itf_m_0001_0016_0001].vbModal
    #Else
        UserForm1.Show VBA.FormShowConstants.vbModal
    #End If

Simply because I would be worried about Microsoft later catching this snafu and fixing it in the next release of Excel and the VBAx compiling constants tend to be "x or better" type constants, i.e. in Excel 2010, VBA6 is TRUE. So if they would fix it, you'd then have a third case to worry about. What I've done is to "throw caution to the wind and live life on the edge" and simply remove the full qualification for code I need to upgrade for 2010.

How about using the handy CallByName Method and handle a runtime error instead - It always work.

 

[Greg Truby]

How about using the handy CallByName Method and handle a runtime error instead - It always work.

Frankly, although I knew about the existence of the CallByName method, I have never used it. You wanna throw together an example of what that might look like?

 

[Jaafar Tribak]

Here is an example that shows how to display the form in excel 2007

CallByName UserForm1, "Show", VbMethod, vbModal

If you were to pass a wrong Member AND/OR wrong Argument as follows it would raise a Runtime error that can be safely handled :

CallByName UserForm1, "BlaBlaBla", VbMethod, "bla bla bla"

The above line is the equivalent of :

UserForm1.BlaBlaBla "bla bla bla"

Which would rise a Compile error.


Looking more carefully,I am not sure if in our particular scenario where the argument is a hidden member ,the form can be displayed in excel 2010 using the CallByName Method.

Anyway, see if running this code line works : (I didn't try this as I don't have Excel 2010)

CallByName UserForm1, "Show", VbMethod, [__MIDL___MIDL_itf_m_0001_0016_0001].vbModal

Another trick that I tend to use in order to avoid version related compile errors is by placing each version-code in a seperate module thus avoiding compiling constants.

 

[Greg Truby]

Jafaar,

As I thought, you can't get that to compile in the "other" version, i.e.

Sub foofoo()
 
    CallByName UserForm1, "Show", VbMethod, VBA.FormShowConstants.vbModal
 
End Sub

Won't compile in XL2010. But

Sub fofofo()
 
    CallByName UserForm1, "Show", VbMethod, VBA.[__MIDL___MIDL_itf_m_0001_0016_0001].vbModal
 
End Sub

compiles, no problem.