Username

[Oldjay]

I have the following code that works on some computer but not on others
It gives the error Project or library not found

Public Function UserName()

UserName = Environ("USERNAME")

End Function

Also tried UserName = Environ$("USERNAME")

What do I have to add?

 

[Tom Hutchins]

On the computers where this code does not work there is probably a needed
reference missing (to see what references have been added, select Refernces
from the Tools menu in the Excel Visual Basic Editor). I think the missing
refernce is either 'Visual Basic for Applications' or 'Microsoft Excel 11.0
Object Library' (you may have a different version than 11.0).

Here is another way to retrieve the user's LAN ID using an API call:

Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" _
(ByVal lpBuffer As String, nSize As Long) As Long

Function UserName() As String
Dim Buffer As String * 100
Dim BuffLen As Long
On Error GoTo UNerr
BuffLen = 100
GetUserName Buffer, BuffLen
UserName = Left(Buffer, BuffLen - 1)
Exit Function
UNerr:
UserName = vbNullString
End Function

Sub AAAAA()
MsgBox UserName()
End Sub

Hope this helps,

Hutch

 

[Oldjay]

All computers have the same references and are running Excel 2003 SP3.
I tried the API and got the same error message at

GetUserName Buffer, BuffLen

 

[Tom Hutchins]

Are the computers where you get this error Macs? The ENVIRON function is not
available on the Mac, and the API won't work on a Mac either.

You could also try retrieving Application.UserName, which is the name
entered on the Tools >> Options >> General tab.

Hutch

 

[Peter T]

The API example Tom posted should work fine for you in windows. Try pasting
it into a new module in a new project and try again. Earlier you said "
error Project or library not found", perhaps you have a missing reference
(Tools - references). If following works it would be highly indicative of a
missing ref.

MsgBox VBA.Interaction.Environ$("USERNAME")

If for you Environ("USERNAME") still doesn't work, ie it returns an empty
string but doesn't error, there's probably not much you can do about it. It
doesn't work for me either. However I get 10 returns if I run this -

Sub test()
Dim i As Long
On Error Resume Next
For i = 0 To 100
s = Environ(i)
If Len(s) Then
Debug.Print i, s
s = ""
End If
Next
End Sub

Regards,
Peter T

 

[Oldjay]

I now get an error "Declare statrments not allowed as public membersof object
modules" when i try to run api

 

[Peter T]

When trying code from this ng always place it in a normal module, unless
instructed otherwise or it's self evident from the code, eg it's an event

If for your own reasons the API declaration needs to go in say Thisworkbook,
sheet, userform or any other class module, declare it as Private

Private Declare Function GetUserName Lib etc

See Declare together with Public & Private in help

Regards,
Peter T

 

[Tom Hutchins]

Put the API declaration and function code in a VBA module (Insert >> Module
in the Visual Basic Editor). You can't put that code in the code module for a
userform or a worksheet or in the ThisWorkbook module.

Hutch

 

[timmg]

What does

?Environ(49)

or

?Environ("username")

display when entered directly in the immediate window? (ctrl + G)

tim

 

[Tom Hutchins]

For me,

?Environ(49) displays nothing
?Environ("username") displays my LAN ID

Hutch

 

[Oldjay]

I can open a new workbook paste the code and get Lan ID
When I insert a new module in the existing workbook and paste the code I get
the same error. It's got to be something in this machine as the program works
with Environ("username") on other machines. This a 3.5M estimating program
with many menus and modules and more than 75 Subs.

Is there any other thing that I can look for?

 

[Tom Hutchins]

Clearly, there is no problem with the code. It must be an environmental
(system) problem, Excel has become corrupted, or there is a conflict with
another program.

First, I would double-check the references in the VBE on the computers that
won't run the Environ("username") statement. Maybe, for example, your
workbook was saved with a reference to the Excel 11.0 Object Library but
those machines only have the Excel 10.0 Object Library.

Second, I would check what add-ins, if any, are enabled on those machines.
Some add-ins (like Hyperion's SmartView) can alter or disable normal Excel
functions. Disable any add-ins not found on the machines that work, and see
if Environ("username") works then.

Hutch

 

[Peter T]

I did try earlier to suggest the OP check for any missing references and how
to overcome (even how to get Environ to work with a missing ref). The OP's
stated error message was indeed indicative of a missing ref.

However, Environ("Username") does not work in all systems, it's flaky. It
doesn't work in mine, doesn't fail but merely returns an empty string.

The API is more reliable, subject not declaring it as Public in anything but
a normal module (which is what "Declare" defaults to when not preceded with
"Private").

Regards,
Peter T

 

[Oldjay]

Thanks for all the help. I went ahead with the Application .username even
thou once in a while a user will use another computer and get the wrong name.