Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old August 23rd 05, 04:18 AM
G Lykos
 
Posts: n/a
Default Worksheet VBA function - not recognized

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



  #2   Report Post  
Old August 23rd 05, 05:06 AM
Rowan
 
Posts: n/a
Default

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



  #3   Report Post  
Old August 23rd 05, 05:15 AM
Biff
 
Posts: n/a
Default

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




  #4   Report Post  
Old August 23rd 05, 05:29 AM
G Lykos
 
Posts: n/a
Default

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





  #5   Report Post  
Old August 23rd 05, 05:50 AM
Rowan
 
Posts: n/a
Default

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








Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
changing font style in a complex worksheet function gvm Excel Worksheet Functions 6 August 3rd 05 01:29 AM
How do I use the TABLE worksheet function? Jo Excel Worksheet Functions 2 July 2nd 05 12:41 PM
Automatically up date time in a cell Mark Excel Discussion (Misc queries) 5 May 12th 05 12:26 AM
SUMIF function referring to values on different Worksheet TeeBee0831 Excel Worksheet Functions 10 May 3rd 05 10:28 PM
The Help on the Excel Edate worksheet function contains an error:. Guido Excel Worksheet Functions 1 January 20th 05 01:42 PM


All times are GMT +1. The time now is 05:51 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017