Worksheet VBA function - not recognized

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 Insert>Module

Paste the code in the window that opens.

Biff

"G Lykos" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> > >
> > >
> > >
>
>
>

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 Insert>Module

Paste the code in the window that opens.

Biff

"G Lykos" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> > >
> > >
> > >
>
>
>