Writing VBA Excel functions

[SteveGregory]

I am very new to VBA stuff. I have two functions that I am trying to write. I would like them to work for Excel 2007, 2010 and finally 2000 if possible.

1. I would like to open a worksheet and have a function returns the name of the user/machine who last saved the file. My attempt:
Function LastAuthor()
LastAuthor = ThisWorkbook.BuiltinDocumentProperties("Last Author")
End Function

Cell formula: =LastAuthor()

2. I would like to open a worksheet and have a function returns the name of the current user/machine. My attempt:
Function CurrentUser()
CurrentUser = ActiveWorkbook.UserStatus
End Function

Cell formula: =CurrentUser()

Thanks, Steve

 

[IDS]

Those seem to work (in 2010).

I don't have Excel 2000, but this discussion:

http://www.excelforum.com/excel-formulas-and-functions/547082-last-saved-date-in-excel-2000-a.html

seems to suggest that it will work in 2000.

Am I missing something?

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[SteveGregory]

Doug,
I was having a hard time because the functions were working on a Excel 2010 machine and a 2007 machine but not on my Excel 2007 computer. It turns out that my Excel Options/Trust Center/Macro Settings were set to "Enable all macros" and the other machines were set to "Disable all macros with notification".

So when I changed the setting on my computer, it worked! I had to click on the notification to enable the macros.

I am not sure why this makes a difference. Is there something else that I should do to make these 2 functions work for either setting?

 

[IDS]

Steve - that seems very strange, are you sure the setting was not "Disable all macros"?

You can set a directory (and all sub-directories underneath it) as a "trusted location", then you don't have to click anything to get the macros to work (as long as they are saved to the trusted location).

The other thing to watch for is that the file is not saved as an xlsx, which will loose any attached VBA. The macros in any file saved as .xls, .xlsb, or .xlsm should work, as long as macros are enabled.

On other people's machines there is nothing you can do to guarantee that macros will run. If the computer is set to disable all macros, or if the user does not enable them when prompted, then they won't work.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[SteveGregory]

Doug,
I am not sure why the "Enable all macros" doesn't work on my 2007. So I kept the "Disable all macros with notification" setting because it works and added a "trusted location" as you suggested. Perfect! Thanks

I am using the .xls format to keep it compatible with 2000.