VBA issue, Private Function error when trying to get my macro to work

user_13973032 0
Viewed 110

When pressing the macro, it comes up with a Compile error: Wrong number of arguments or invalid property assignment. I finally got it to slightly work for 64bit, but now am running into this issue.

Private Function getUser() As String
    Dim strUser As String

    'Create a buffer
    strUser = String(100, Chr(0))
    'Get the username
    getUser strUser, 100
    'strip the rest of the buffer
    strUser = Left(strUser, InStr(strUser, Chr(0)) - 1)
    getUser = strUser
End Function

Here's the other one with getUser in it.

On Error Resume Next

Set MyOL = GetObject(, "Outlook.Application")
If MyOL Is Nothing Then
    Set MyOL = CreateObject("Outlook.Application")
End If
strUser = getUser
Application.DisplayAlerts = False
ThisWorkbook.SaveAs "C:\Documents and Settings\" & strUser & "\Local Settings\Temp\" & " " & staffname & " - OT Survey for " & ActiveSheet.name & ".xlsm"
Application.DisplayAlerts = True
vba excel
1 Answers

Your code is caused by getUser strUser, 100.
It's recursively calling itself (i.e. it's within the function called getuser), and is trying to pass two arguments to the function while the function header doesn't ask for them Private Function getUser() As String.
Basically everything that @JohnColeman commented.

How to fix that I've no idea.

The code does resemble what I've posted below, which returns the system user name.
Put the first line at the top of the module and remove PtrSafe if not on 64 bit.

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

Public Function getUser() As String
    On Error Resume Next

    Dim NBuffer As String
    Dim Buffsize As Long
    Dim Wok As Long

    Buffsize = 256
    NBuffer = Space$(Buffsize)

    Wok = api_GetUserName(NBuffer, Buffsize)
    getUser = Trim$(NBuffer)

    On Error GoTo 0
End Function

Saying all that - your SaveAs,where you use the user name, is looking for the temp folder - would just ENVIRON("Temp") work there?

ThisWorkbook.SaveAs Environ("Temp") & "\ " & staffname & " - OT Survey for " & ActiveSheet.Name & ".xlsm"
user_46773050
Related