Get Function to run as a macro

[randolphoralph]

I am not sure how to get the following code to run under a macro. When I go into new macro and select Run Code and enter the Function GetUserName(). I try to run the code and receive a message stating that the function can not be found. I know that it is not found since it under a Private Declared Function but I am not sure how to change the code to get it to run as a macro. Please help.

Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
   "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Function GetUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
    strUserName = String$(254, 0)
    lngLen = 255
    lngX = apiGetUserName(strUserName, lngLen)
    If (lngX > 0) Then
        fOSUserName = Left$(strUserName, lngLen - 1)
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel19, "Random_Provider_List", "E:\Documents and Settings\" & fOSUserName & "\Desktop\Random_Provider_List.xls", , "Random_Provider_List"
    Else
        fOSUserName = vbNullString
    
    End If
End Function

[llkhoutx]

Use the RunCode action in a macro, specifyin the function.

If you can write the subject code, you should avoid macros unless absolutely necessary and graduate to VBA.

VBA is instantly viewable, were macros have to be drilled through, which you'll find is a significant dowside of macros.

[randolphoralph]

Use the RunCode action in a macro, specifyin the function.

If you can write the subject code, you should avoid macros unless absolutely necessary and graduate to VBA.

VBA is instantly viewable, were macros have to be drilled through, which you'll find is a significant dowside of macros.

When I use the RunCode action in the macro and try to run the macro it says the function can not be found since it is a Private Function...I have also tried it as Public function and it still will not work.

[RuralGuy]

The function needs to be in a standard module and not in a class module of a form. Is it? You might also create another Public Function in that standard module that invokes this function and returns the result.

[randolphoralph]

The function needs to be in a standard module and not in a class module of a form. Is it? You hight also create another Public Function in that standard module that invokes this function and returns the result.

I am not sure how to tell if it is a standard or class module...I created the module under the module tab (Tables, Queries, Forums, Reports, Pages, Macros, Modules) How would I create another Public Function in that standard module that will invoke the function and return the result...I am new and still learning so thank you for your patience.

[RuralGuy]

This is the Function:
Function GetUserName() As String
and the default is Public but it couldn't hurt to make sure.
Public Function GetUserName() As String
...it should work! I don't use macros so I'm not real familiar with how or if they work.

[randolphoralph]

This is the Function:
Function GetUserName() As String
and the default is Public but it couldn't hurt to make sure.
Public Function GetUserName() As String
...it should work! I don't use macros so I'm not real familiar with how or if they work.

Thank you. I tried to change it to Public Function GetUserName() As String and it still would not work. I am trying to get this function to run on a button click and have tried to get the function to run directly without the use of a macro and received a error message stating that the Function GetUserName() could not be found. I am at a loss and ready to just give up.

[RuralGuy]

How about switching to code for this event? A Function needs some place to put the results. Does a macro allow for that? It is different than just running a Sub that has no return value. Anyway, if you want to switch to code there are pleanty of us that can help you write it.

[RuralGuy]

BTW have you eliminated corruption as a possible problem by importing your db into a new, empty db and see if the problem follows the import?

[ChrisO]

I will assume that the module name is also GetUserName and that should be fixed first..

[RuralGuy]

Thanks Chris! I completely overlooked that possibility. D'oh!

[randolphoralph]

Wow...Never would have thought of that. As soon as I changed the Module Name it worked like a charm. That is good to know. I am very impressed it is the little things that get me each and every time.