+ Reply to Thread
Results 1 to 7 of 7

Username Code help - Slow processing speed

  1. #1
    Registered User
    Join Date
    12-03-2013
    Location
    Coventry
    MS-Off Ver
    Excel 2007
    Posts
    6

    Username Code help - Slow processing speed

    Good Morining All,

    I have managed to put together a Macro which finds out the Username if a user within Excel.
    I did have this code without the Application.Volatile but it didn't work. The downside of this is that if I have this included it slows down the whole spreadsheet.

    The Code was taken from various different sources so I do not claim to be the originator of the code. Nor am I precious about it. If you guys can think of a way to get the spreadsheet on open to tell me the username that would be brilliant.

    The function I call to find out the name is fGetUsername

    Thanks in advance.

    James Burton.

    Private Type USER_INFO_2
    usri2_name As Long
    usri2_password As Long ' Null, only settable
    usri2_password_age As Long
    usri2_priv As Long
    usri2_home_dir As Long
    usri2_comment As Long
    usri2_flags As Long
    usri2_script_path As Long
    usri2_auth_flags As Long
    usri2_full_name As Long
    usri2_usr_comment As Long
    usri2_parms As Long
    usri2_workstations As Long
    usri2_last_logon As Long
    usri2_last_logoff As Long
    usri2_acct_expires As Long
    usri2_max_storage As Long
    usri2_units_per_week As Long
    usri2_logon_hours As Long
    usri2_bad_pw_count As Long
    usri2_num_logons As Long
    usri2_logon_server As Long
    usri2_country_code As Long
    usri2_code_page As Long
    End Type

    Private Declare Function apiNetGetDCName Lib "Netapi32.dll" Alias "NetGetDCName" (ByVal servername As Long, ByVal DomainName As Long, bufptr As Long) As Long

    Private Declare Function apiNetAPIBufferFree Lib "Netapi32.dll" Alias "NetApiBufferFree" (ByVal buffer As Long) As Long

    Private Declare Function apilstrlenW Lib "kernel32" Alias "lstrlenW" (ByVal lpString As Long) As Long

    Private Declare Function apiNetUserGetInfo Lib "Netapi32.dll" Alias "NetUserGetInfo" (servername As Any, UserName As Any, ByVal level As Long, bufptr As Long) As Long

    Private Declare Sub sapiCopyMem Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As Long)

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

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

    Private Declare Function GetComputerName Lib "kernel32.dll" Alias "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long

    Private strUserID As String

    Private strUserName As String

    Private strComputerName As String

    Private Const MAXCOMMENTSZ = 256
    Private Const NERR_SUCCESS = 0
    Private Const ERROR_MORE_DATA = 234&
    Private Const MAX_CHUNK = 25
    Private Const ERROR_SUCCESS = 0&

    Public Function fGetUserName() As String
    Application.Volatile
    ' Returns the network login name
    Dim lngLen As Long, lngRet As Long
    Dim strUserName As String
    strUserName = String$(254, 0)
    lngLen = 255
    lngRet = apiGetUserName(strUserName, lngLen)
    If lngRet Then
    fGetUserName = Left$(strUserName, lngLen - 1)
    End If
    Application.Calculate

    End Function

    Private Sub Class_Initialize()
    On Error Resume Next
    'Returns the network login name
    Dim strTempUserID As String, strTempComputerName As String

    'Create a buffer
    strTempUserID = String(100, Chr$(0))
    strTempComputerName = String(100, Chr$(0))

    'Get user name
    GetUserName strTempUserID, 100

    'Get computer name
    GetComputerName strTempComputerName, 100

    'Strip the rest of the buffer
    strTempUserID = Left$(strTempUserID, InStr(strTempUserID, Chr$(0)) - 1)
    Let strUserID = LCase(strTempUserID)

    strTempComputerName = Left$(strTempComputerName, InStr(strTempComputerName, Chr$(0)) - 1)
    Let strComputerName = LCase(strTempComputerName)

    Let strUserName = DragUserName(strUserID)

    End Sub

    Public Property Get UserID() As String
    UserID = strUserID
    End Property

    Public Property Get UserName() As String
    UserName = strUserName
    End Property

    Public Function DragUserName(Optional strUserName As String) As String
    On Error GoTo ErrHandler
    Dim pBuf As Long
    Dim dwRec As Long
    Dim pTmp As USER_INFO_2
    Dim abytPDCName() As Byte
    Dim abytUserName() As Byte
    Dim lngRet As Long
    Dim i As Long

    ' Unicode
    abytPDCName = fGetDCName() & vbNullChar
    If strUserName = "" Then strUserName = GetUserName()
    abytUserName = strUserName & vbNullChar

    ' Level 2
    lngRet = apiNetUserGetInfo( _
    abytPDCName(0), _
    abytUserName(0), _
    2, _
    pBuf)
    If (lngRet = ERROR_SUCCESS) Then
    Call sapiCopyMem(pTmp, ByVal pBuf, Len(pTmp))
    DragUserName = fStrFromPtrW(pTmp.usri2_full_name)
    End If

    Call apiNetAPIBufferFree(pBuf)
    ExitHere:
    Exit Function
    ErrHandler:
    DragUserName = vbNullString
    Resume ExitHere
    End Function

    Public Property Get ComputerName() As String
    ComputerName = strComputerName
    End Property

    Private Sub Class_Terminate()
    strUserName = ""
    strComputerName = ""
    End Sub

    Public Function fGetDCName() As String
    Dim pTmp As Long
    Dim lngRet As Long
    Dim abytBuf() As Byte

    lngRet = apiNetGetDCName(0, 0, pTmp)
    If lngRet = NERR_SUCCESS Then
    fGetDCName = fStrFromPtrW(pTmp)
    End If
    Call apiNetAPIBufferFree(pTmp)
    End Function

    Public Function fStrFromPtrW(pBuf As Long) As String
    Dim lngLen As Long
    Dim abytBuf() As Byte

    ' Get the length of the string at the memory location
    lngLen = apilstrlenW(pBuf) * 2
    ' if it's not a ZLS
    If lngLen Then
    ReDim abytBuf(lngLen)
    ' then copy the memory contents
    ' into a temp buffer
    Call sapiCopyMem( _
    abytBuf(0), _
    ByVal pBuf, _
    lngLen)
    ' return the buffer
    fStrFromPtrW = abytBuf
    End If
    End Function

  2. #2
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Username Code help - Slow processing speed

    Can you not use:

    Please Login or Register  to view this content.
    ?

  3. #3
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Username Code help - Slow processing speed

    Please Login or Register  to view this content.
    You must please learn code tags.
    • Please remember to mark threads Solved with Thread Tools link at top of page.
    • Please use code tags when posting code: [code]Place your code here[/code]
    • Please read Forum Rules

  4. #4
    Registered User
    Join Date
    12-03-2013
    Location
    Coventry
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Username Code help - Slow processing speed

    Quote Originally Posted by Izandol View Post
    Please Login or Register  to view this content.
    You must please learn code tags.
    Thanks but that doesn't seem to update my field. I have the value stored on a sheet which is usually hidden. Although when I run the code in the immediate window it displays the username for that user it doesn't recalculate the field. Even when i request excel to select the field and recalc (all within code) it doesn't work. I have to manually f2 and enter the field before it shows correctly.

    Any ideas? I want to move away from application.volatile but that is slowing the workbook down.

    Cheers

  5. #5
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Username Code help - Slow processing speed

    You have to use application.volatile to get it to update I believe, it shouldn't slow the workbook down with just one line in the function.

  6. #6
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,667

    Re: Username Code help - Slow processing speed

    Use the below one in the second line of the code to make the UDF volatile

    Please Login or Register  to view this content.


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  7. #7
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    7,994

    Re: Username Code help - Slow processing speed

    Hi, JBurton1986,

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    Izandol has made a point of that before.

    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Slow VBA Code for simple Loop and add pasted values Any Way to Speed Up
    By John Vieren in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-10-2013, 11:16 AM
  2. Excel processing speed
    By tony.nz in forum Excel General
    Replies: 2
    Last Post: 11-13-2009, 03:42 PM
  3. Increasing Processing Speed
    By boylejob in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-11-2008, 02:19 AM
  4. Processing speed
    By maacmaac in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-07-2008, 02:21 PM
  5. [SOLVED] speed of processing
    By Chas in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-08-2006, 09:20 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1