Excel Macro VB Mismatch error

Hi everybody,

I hope this is the right place to post this. I was looking to get some feedback on a mismatch error I'm getting in Visual Basic. I have inherited a custom macro created to run in an Excel spreadsheet. The business that I am working with, would like this updated to work with the newer version of the software. I'm not super proficient in Visual Basic's, more of a newbe.

Anyway, I'm getting a mismatch error I was hoping somebody could look at the picture below and perhaps give me some input on what I should correct?

Attachment 170169

here is the code too

Code:

---

Option Explicit
Declare PtrSafe Function GetUserName Lib "advapi32.dll" _
    Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As LongPtr) As LongPtr
Public Function OpenAuthenticate() As String
   
    Load frmAuthenticate    'Load the authentication form
    frmAuthenticate.Show    'Make the form visible to the user
   
                            'Get the username back from the form
    OpenAuthenticate = frmAuthenticate.strValue
    'MsgBox "Value returned from frmAuthenticate: " & OpenAuthenticate
   
                            'Unload the form from memory
    Unload frmAuthenticate
End Function

Public Function Get_Network_User() As String
                      'Return the network login name of the local user
               
Dim slength As LongPtr  ' length of the string
Dim username As LongPtr
Dim retval As LongPtr  ' return value

                        ' Create room in the buffer to receive the returned string.
username = Space(255)  ' room for 255 characters
slength = 255          ' initialize the size of the string
                        ' slength is now the length of the returned string
                       
                        ' Get the user's name
retval = GetUserName(username, slength)

If retval = 0 Then
    Get_Network_User = "" 'retval=0 if an error occured in GetUserName
Else
    Get_Network_User = Left(username, slength - 1)  ' extract the returned info from the buffer
    '(We subtract one because we don't want the null character at the end of the trimmed string.)
End If

End Function

---

as you do not show the code to call the function, hard to guess

going by the highlighted line, should be something like

Code:

---

somevar = get_network_user

---

your variable must be of type string

but also username should be a string

but on testing the first did not appear to cause a problem, so the problem was username

You may be getting this error if the user name is blank (0 length). Add a breakpoint or Stop to check the username contents.

Change

Code:

---

Dim username As LongPtr

---

to

Code:

---

Dim username As String

---

Quote:

---

Originally Posted by westconn1

as you do not show the code to call the function, hard to guess

going by the highlighted line, should be something like

Code:

---

somevar = get_network_user

---

your variable must be of type string

but also username should be a string

but on testing the first did not appear to cause a problem, so the problem was username

---

sorry, here is the whole thing...

Code:

---

Option Explicit
Declare PtrSafe Function GetUserName Lib "advapi32.dll" _
    Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As LongPtr) As LongPtr
Public Function OpenAuthenticate() As String
   
    Load frmAuthenticate    'Load the authentication form
    frmAuthenticate.Show    'Make the form visible to the user
   
                            'Get the username back from the form
    OpenAuthenticate = frmAuthenticate.strValue
    'MsgBox "Value returned from frmAuthenticate: " & OpenAuthenticate
   
                            'Unload the form from memory
    Unload frmAuthenticate
End Function
Public Function Get_Network_User() As String
                      'Return the network login name of the local user
               
Dim slength As LongPtr  ' length of the string
Dim username As String
Dim retval As LongPtr  ' return value

                        ' Create room in the buffer to receive the returned string.
username = Space(255)  ' room for 255 characters
slength = 255          ' initialize the size of the string
                        ' slength is now the length of the returned string
                       
                        ' Get the user's name
retval = GetUserName(username, slength)

If retval = 0 Then
    Get_Network_User = "" 'retval=0 if an error occured in GetUserName
Else
    Get_Network_User = Left(username, slength - 1)  ' extract the returned info from the buffer
    '(We subtract one because we don't want the null character at the end of the trimmed string.)
End If

End Function

---