dcsimg
Results 1 to 5 of 5

Thread: Excel Macro VB Mismatch error

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Feb 2015
    Location
    Ma
    Posts
    17

    Question 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?

    Name:  Capture.jpg
Views: 112
Size:  32.2 KB

    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
    Last edited by Siddharth Rout; Aug 2nd, 2019 at 06:55 AM. Reason: Inserted Code Tags

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,444

    Re: Excel Macro VB Mismatch error

    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
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3
    Frenzied Member jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    1,427

    Re: Excel Macro VB Mismatch error

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

  4. #4
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,998

    Re: Excel Macro VB Mismatch error

    Change

    Code:
    Dim username As LongPtr
    to

    Code:
    Dim username As String
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    MyGear:
    OMEN by HP - 15-ce073tx with Win10+Office 2013. || Mac Book Pro (10.6.8) with Office 2011

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Feb 2015
    Location
    Ma
    Posts
    17

    Re: Excel Macro VB Mismatch error

    Quote Originally Posted by westconn1 View Post
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width