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

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


"G Lykos" wrote:

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

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 InsertModule

Paste the code in the window that opens.

Biff

"G Lykos" wrote in message
...
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

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?


"Rowan" wrote in message
...
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


"G Lykos" wrote:

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

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

"G Lykos" wrote:

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?


"Rowan" wrote in message
...
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


"G Lykos" wrote:

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