Worksheet VBA function - not recognized

[G Lykos]

Greetings! Am trying first experience of creating a VBA function that is
called via a cell formula in a worksheet, unsuccessfully so far.

Steps have been to paste code into a workbook module such as:

Function CellType(c)
' Returns the cell type of the upper left
' cell in a range
Application.Volatile
Set c = c.Range("A1")
Select Case True
Case IsEmpty(c): CellType = "Blank"
Case Application.IsText(c): CellType = "Text"
Case Application.IsLogical(c): CellType = "Logical"
Case Application.IsErr(c): CellType = "Error"
Case IsDate(c): CellType = "Date"
Case InStr(1, c.Text, ":") <> 0: CellType = "Time"
Case IsNumeric(c): CellType = "Value"
End Select
End Function

I then enter a formula in a cell in a worksheet as =celltype(a1). The
function name does not get capitalized, suggesting that it wasn't located,
and the result is #NAME?, suggesting same thing. Something's not plugged in
somewhere, but I have no idea what. Help!

Thanks,
George

 

[Guest]

There is nothing wrong with the function so it probably has to do with where
you pasted it.

If you place the function in a module and reference it on a worksheet in the
same workbook then you can just type =celltype(A1). If you place the function
in a workbook other than the one where you are going to use it then you must
add the book name to the reference eg =PERSONAL.XLS!celltype(E18)

Hope this helps
Rowan

 

[Biff]

Hi!

You're function does work (tried it) but you probably put it in the wrong
place.

Put it in a general module

ALT + F11 to open the VBE
CTRL + R to open the Project Explorer

Find your workbook/project name

Right click the name and selct Insert>Module

Paste the code in the window that opens.

Biff

 

[G Lykos]

Thanks, Rowan. Added a module in the workbook, pasted the code there, and
it works - I'm launched! BTW, the capitalization in the formula didn't
change, so that behavior is apparently different than standard functions -
looks like you don't get an indication of the validity of a custom function
name while entering a formula, only by seeing results or lack of them
(Office 97). Sound right?

 

[Guest]

Hi George

You are right about the capitalization. User defined functions do not have
all of the features of inbuilt Excel functions eg helpfiles etc

Regards
Rowan