UDF Function

[Steved]

Hello from Steved
I was kindly given the below UDF function from Frank Kabel
I have excel xp office at home but 2000 at work
I would like to get it to function so that I understand
how it works. in B3 i've put =DocProps it has put
in #NAME? What am I doing thats not right please.
ps I've put in VBA could this be an issue.

Function DocProps(prop As String)
application.volatile
On Error GoTo err_value
DocProps = ActiveWorkbook.BuiltinDocumentProperties
(prop)
Exit Function
err_value:
DocProps = CVErr(xlErrValue)
End Function

Now you can use the following formula in one of your
cells:
=DocProps("Last save time")

 

[JE McGimpsey]

You need to include the argument:

=DocProps("Last Save Time")

 

[Steved]

Hello from Steved
I have entered in B3 =DocProps("Last Save Time") but it
is still giving #NAME? in the cell. I have formated the
cell custom dd/mmm is this my problem.

Thankyou

 

[Gord Dibben]

Steve

Where did you put the UDF?

Should go into a General Module in your workbook.

The #NAME will arise if the UDF cannot be found.

Type #NAME in Answer Wizard to get other potential causes.

Gord Dibben Excel MVP

 

[JE McGimpsey]

Formatting has nothing to do with the problem. Is the UDF in the same
workbook that you entered the formula in?

 

[Steved]

Hello Gord from Steved
I put the code in General Module
The Answwer wizard tells me, The Trace Dependents
command found no formulas that refer to the active cell.
Is there some range I need to put in below for it to
function Thankyou.

Function DocProps(prop As String)
Application.Volatile
On Error GoTo err_value
DocProps = ActiveWorkbook.BuiltinDocumentProperties
(prop)
Exit Function
err_value:
DocProps = CVErr(xlErrValue)
End Function

 

[Steved]

Yes in General Module

-----Original Message-----
Formatting has nothing to do with the problem. Is the UDF in the same
workbook that you entered the formula in?





.

 

[Gord Dibben]

Steve

Works for me in Excel 2002.

=DocProps("last save time") returns a number which is the time when formatted.

General module in my workbook.

BTW. There is a line wrap in the posted code. (prop) is to go on same line
as BuiltinDocumentProperties.

Gord

 

[Dave Peterson]

What did you name the module?

Is it the same as the name of the UDF? If yes, try renaming it:

mod_docprops

might help.

 

[Steved]

Thanks Gord I done everything you have done, but as you
have it working in your spreadsheet I will now work
throught the isuue that it is not working for me and why.
Thankyou for your post.

 

[Steved]

Hello Dave from Steved

All I did was right click on view code and past the code
hence you've raised a point is there another way of
inputing a UDF.

Thanks.

 

[Arvi Laanemets]

Hi

Open your workbook and press Alt+F11. The VBA editor window is opened.

In left upper corner there must be visible the Project window. You see there
VBAProject(YourWorkbook), which has several Microsoft Excel Objects, like
Sheet1(Sheet1), Sheet2(Sheet2), Sheet3(Sheet3), and ThisWorkbook. Below
ThisWorkbook must you have there the VBAProject modules, with at least one
module displayed. Your function must be placed in any of them.

When there isn't any module (then you have probably inserted your procedure
into some worksheets module), then you have to add one into VBAProject
(Insert.Module from menu), and then to insert your function into this module
anew.

PS. When you inserted your function correctly, then you can pick it in
Insert Function Wizard (Paste Function cutton from toolbar) from category
'User defined', like any other available function.

 

[Steved]

Hello Arvi from Steved
What I did not see was project window, I fixed the problem
and now it works.

Thankyou.

 

[Bob Phillips]

Steve,

You problem is wrap-around. The lines

DocProps = ActiveWorkbook.BuiltinDocumentProperties
(prop)

should be one line, so add (prop) to the former and delete the latter.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Harald Staff]

All I did was right click on view code and past the code

That is NOT a general module. Open the VB editor (Alt F11 or similar) and
use menu Insert Module. Paste it there.
Function tested & works fine in Excel 2000.

HTH. Best wishes Harald

 

[Steved]

Hello Bob from Steved
Yes as you point out to me I've corrected it, but not
understanding the concepts I put it in view code module
hence it did not work but when I went Alt F11 and put it
in the right module it worked. Ps (trap for the unwary)

Thankyou for your post.