CODEWITHSUNDEEP
vbaobjectms-access
whatwhatwhat
whatwhatwhat

Reputation: 2256

Variable not defined in VBA script

I'm getting into the good habit of using Option Explicit at the top of all my MS Access VBA scripts. However, for this function it's giving me a Variable not defined error highlighting the line that begins with Set objSysInfo =

Public Function GetUser(Optional whatpart = "username")

    Dim returnthis As String
    If whatpart = "username" Then GetUser = Environ("USERNAME"): Exit Function
    Set objSysInfo = CreateObject("ADSystemInfo")
    Set objUser = GetObject("LDAP://" & objSysInfo.UserName)
    Select Case whatpart
        Case "fullname": returnthis = objUser.FullName
        Case "firstname", "givenname": returnthis = objUser.givenName
        Case "lastname": returnthis = objUser.LastName
        Case Else: returnthis = Environ("USERNAME")
    End Select
    GetUser = returnthis

End Function

Might I be missing a specific reference?

Upvotes: 1

Views: 5248

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71157

Option Explicit forces you to declare all variables.

This means you must declare all variables you're using, otherwise your code won't compile - as you've noticed.

You declared local variable returnthis as a String, using the Dim keyword:

Dim returnthis As String

That's how you declare a local (procedure scope) variable.

You could declare variables at module scope using the Private keyword (Dim works as well, but better keep Dim for locals)

Option Explicit
Private foo As Object

How you scope variables depends on where you're using them - if a variable is only ever used in one single procedure, then it's better to scope it to that procedure and declare it as a local variable.

In this case you have objSysInfo and objUser that aren't declared anywhere.

Dim objSysInfo As Object
Dim objUser As Object

I know we're not on Code Review, but while we're here there are other issues with your code:

  • The return type of the function is implicitly Variant. Append As String to the end of the function's signature.
  • The optional parameter whatpart is implicitly Variant as well, and implicitly passed ByRef, but you only need to read it, not write and return to the caller - so declare it Optional ByVal whatpart As String = "username".
  • Prefer the Environ$ function over Environ - both do the same thing, but Environ returns a Variant, that you're implicitly converting to a String - best use Environ$ and work with a string from the start.

  • Instead of relying on magic, hard-coded strings, use an Enum value and Select Case over the possible legal values:

    Public Enum NamePart
    EnvironmentDefined
    FullName
    FirstName
    LastName
End Enum

The full code could look like this:

Public Enum NamePart
    EnvironmentDefined
    FullName
    FirstName
    LastName
End Enum

Public Function GetUserName(Optional ByVal part As NamePart = EnvironmentDefined) As String

    Dim result As String
    Dim sysInfo As Object
    Dim userInfo As Object

    If Namepart = EnvironmentDefined Then 
        GetUser = Environ$("USERNAME")
        Exit Function
    End If

    Set sysInfo = CreateObject("ADSystemInfo")
    Set userInfo = GetObject("LDAP://" & sysInfo.UserName)
    Select Case part
        Case FullName
            result = userInfo.FullName
        Case FirstName
            result = userInfo.GivenName
        Case LastName
            result = userInfo.LastName
        Case Else
            result = Environ$("USERNAME")
    End Select

    GetUserName = result

End Function

Note, I didn't include a GivenName enum member, since it's redundant; calling code can do this:

Dim value As String
value = GetUserName(FirstName)

Upvotes: 6

Related Questions