Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Functions in VBA used in spreadsheet cell
I have written an extremely simple function in VBA module for sheet 1 and
when I try to use that function in a cell in sheet 1 I get a *Name? error. Has anyone seen this before? How do I get the function to work? Function CalculateOne (a,b) CalculateOne = 2*a + b EndFunction -- Thank you, JRXPL |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Functions in VBA used in spreadsheet cell
Make sure your function is in a General module--not under the ThisWorkbook
module, not behind a worksheet module. Make sure you allow macros to run when you open your workbook. And if you use ranges in your formula, make sure you're spelling the addresses correctly. If the problem doesn't go away, then make sure you share what you typed into the cell. ps. I'm assuming the typos in the function are just in the post. In the future, it's better to copy directly from your code and paste into the message. JRXPL wrote: I have written an extremely simple function in VBA module for sheet 1 and when I try to use that function in a cell in sheet 1 I get a *Name? error. Has anyone seen this before? How do I get the function to work? Function CalculateOne (a,b) CalculateOne = 2*a + b EndFunction -- Thank you, JRXPL -- Dave Peterson |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Functions in VBA used in spreadsheet cell
Thank you very much-
I checked the Macro Security and set it to low. I typed the following into the General module, no ranges, checked spelling. It still doesn't work when I type the below command into the excel cell. Function CalculateOne(a, b) CalculateOne = a + b End Function =CalculateOne(2,3) -- Thank you, JRXPL "Dave Peterson" wrote: Make sure your function is in a General module--not under the ThisWorkbook module, not behind a worksheet module. Make sure you allow macros to run when you open your workbook. And if you use ranges in your formula, make sure you're spelling the addresses correctly. If the problem doesn't go away, then make sure you share what you typed into the cell. ps. I'm assuming the typos in the function are just in the post. In the future, it's better to copy directly from your code and paste into the message. JRXPL wrote: I have written an extremely simple function in VBA module for sheet 1 and when I try to use that function in a cell in sheet 1 I get a *Name? error. Has anyone seen this before? How do I get the function to work? Function CalculateOne (a,b) CalculateOne = 2*a + b EndFunction -- Thank you, JRXPL -- Dave Peterson |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Functions in VBA used in spreadsheet cell
It worked fine for me.
And you did put it in a General module in the workbook's project that held the worksheet with the cell with the formula? JRXPL wrote: Thank you very much- I checked the Macro Security and set it to low. I typed the following into the General module, no ranges, checked spelling. It still doesn't work when I type the below command into the excel cell. Function CalculateOne(a, b) CalculateOne = a + b End Function =CalculateOne(2,3) -- Thank you, JRXPL "Dave Peterson" wrote: Make sure your function is in a General module--not under the ThisWorkbook module, not behind a worksheet module. Make sure you allow macros to run when you open your workbook. And if you use ranges in your formula, make sure you're spelling the addresses correctly. If the problem doesn't go away, then make sure you share what you typed into the cell. ps. I'm assuming the typos in the function are just in the post. In the future, it's better to copy directly from your code and paste into the message. JRXPL wrote: I have written an extremely simple function in VBA module for sheet 1 and when I try to use that function in a cell in sheet 1 I get a *Name? error. Has anyone seen this before? How do I get the function to work? Function CalculateOne (a,b) CalculateOne = 2*a + b EndFunction -- Thank you, JRXPL -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Functions in VBA used in spreadsheet cell
Thanks for checking it. It is in the general module for the sheet the cell
is in. I even tried typing it in a cell in all sheets. Still not working??? -- Thank you, JRXPL "Dave Peterson" wrote: It worked fine for me. And you did put it in a General module in the workbook's project that held the worksheet with the cell with the formula? JRXPL wrote: Thank you very much- I checked the Macro Security and set it to low. I typed the following into the General module, no ranges, checked spelling. It still doesn't work when I type the below command into the excel cell. Function CalculateOne(a, b) CalculateOne = a + b End Function =CalculateOne(2,3) -- Thank you, JRXPL "Dave Peterson" wrote: Make sure your function is in a General module--not under the ThisWorkbook module, not behind a worksheet module. Make sure you allow macros to run when you open your workbook. And if you use ranges in your formula, make sure you're spelling the addresses correctly. If the problem doesn't go away, then make sure you share what you typed into the cell. ps. I'm assuming the typos in the function are just in the post. In the future, it's better to copy directly from your code and paste into the message. JRXPL wrote: I have written an extremely simple function in VBA module for sheet 1 and when I try to use that function in a cell in sheet 1 I get a *Name? error. Has anyone seen this before? How do I get the function to work? Function CalculateOne (a,b) CalculateOne = 2*a + b EndFunction -- Thank you, JRXPL -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Functions in VBA used in spreadsheet cell
Try changing the function to:
Function CalculateOneA(a, b) CalculateOneA = a + b End Function =CalculateOneA(2,3) Maybe you have something that's conflicting with that name--module name maybe????? (If you did rename the module, change it to something else--Mod_CalculateOne???) JRXPL wrote: Thanks for checking it. It is in the general module for the sheet the cell is in. I even tried typing it in a cell in all sheets. Still not working??? -- Thank you, JRXPL "Dave Peterson" wrote: It worked fine for me. And you did put it in a General module in the workbook's project that held the worksheet with the cell with the formula? JRXPL wrote: Thank you very much- I checked the Macro Security and set it to low. I typed the following into the General module, no ranges, checked spelling. It still doesn't work when I type the below command into the excel cell. Function CalculateOne(a, b) CalculateOne = a + b End Function =CalculateOne(2,3) -- Thank you, JRXPL "Dave Peterson" wrote: Make sure your function is in a General module--not under the ThisWorkbook module, not behind a worksheet module. Make sure you allow macros to run when you open your workbook. And if you use ranges in your formula, make sure you're spelling the addresses correctly. If the problem doesn't go away, then make sure you share what you typed into the cell. ps. I'm assuming the typos in the function are just in the post. In the future, it's better to copy directly from your code and paste into the message. JRXPL wrote: I have written an extremely simple function in VBA module for sheet 1 and when I try to use that function in a cell in sheet 1 I get a *Name? error. Has anyone seen this before? How do I get the function to work? Function CalculateOne (a,b) CalculateOne = 2*a + b EndFunction -- Thank you, JRXPL -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Functions in VBA used in spreadsheet cell
Thanks-
Did a copy paste. Same result. I did notice that Excel runs a virus scan when I open the file??? -- Thank you, JRXPL "Dave Peterson" wrote: Try changing the function to: Function CalculateOneA(a, b) CalculateOneA = a + b End Function =CalculateOneA(2,3) Maybe you have something that's conflicting with that name--module name maybe????? (If you did rename the module, change it to something else--Mod_CalculateOne???) JRXPL wrote: Thanks for checking it. It is in the general module for the sheet the cell is in. I even tried typing it in a cell in all sheets. Still not working??? -- Thank you, JRXPL "Dave Peterson" wrote: It worked fine for me. And you did put it in a General module in the workbook's project that held the worksheet with the cell with the formula? JRXPL wrote: Thank you very much- I checked the Macro Security and set it to low. I typed the following into the General module, no ranges, checked spelling. It still doesn't work when I type the below command into the excel cell. Function CalculateOne(a, b) CalculateOne = a + b End Function =CalculateOne(2,3) -- Thank you, JRXPL "Dave Peterson" wrote: Make sure your function is in a General module--not under the ThisWorkbook module, not behind a worksheet module. Make sure you allow macros to run when you open your workbook. And if you use ranges in your formula, make sure you're spelling the addresses correctly. If the problem doesn't go away, then make sure you share what you typed into the cell. ps. I'm assuming the typos in the function are just in the post. In the future, it's better to copy directly from your code and paste into the message. JRXPL wrote: I have written an extremely simple function in VBA module for sheet 1 and when I try to use that function in a cell in sheet 1 I get a *Name? error. Has anyone seen this before? How do I get the function to work? Function CalculateOne (a,b) CalculateOne = 2*a + b EndFunction -- Thank you, JRXPL -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Functions in VBA used in spreadsheet cell
I'm out of guesses.
Sorry. JRXPL wrote: Thanks- Did a copy paste. Same result. I did notice that Excel runs a virus scan when I open the file??? -- Thank you, JRXPL "Dave Peterson" wrote: Try changing the function to: Function CalculateOneA(a, b) CalculateOneA = a + b End Function =CalculateOneA(2,3) Maybe you have something that's conflicting with that name--module name maybe????? (If you did rename the module, change it to something else--Mod_CalculateOne???) JRXPL wrote: Thanks for checking it. It is in the general module for the sheet the cell is in. I even tried typing it in a cell in all sheets. Still not working??? -- Thank you, JRXPL "Dave Peterson" wrote: It worked fine for me. And you did put it in a General module in the workbook's project that held the worksheet with the cell with the formula? JRXPL wrote: Thank you very much- I checked the Macro Security and set it to low. I typed the following into the General module, no ranges, checked spelling. It still doesn't work when I type the below command into the excel cell. Function CalculateOne(a, b) CalculateOne = a + b End Function =CalculateOne(2,3) -- Thank you, JRXPL "Dave Peterson" wrote: Make sure your function is in a General module--not under the ThisWorkbook module, not behind a worksheet module. Make sure you allow macros to run when you open your workbook. And if you use ranges in your formula, make sure you're spelling the addresses correctly. If the problem doesn't go away, then make sure you share what you typed into the cell. ps. I'm assuming the typos in the function are just in the post. In the future, it's better to copy directly from your code and paste into the message. JRXPL wrote: I have written an extremely simple function in VBA module for sheet 1 and when I try to use that function in a cell in sheet 1 I get a *Name? error. Has anyone seen this before? How do I get the function to work? Function CalculateOne (a,b) CalculateOne = 2*a + b EndFunction -- Thank you, JRXPL -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Functions in VBA used in spreadsheet cell
Thanks for trying. Much appreciated.
-- Thank you, JRXPL "Dave Peterson" wrote: I'm out of guesses. Sorry. JRXPL wrote: Thanks- Did a copy paste. Same result. I did notice that Excel runs a virus scan when I open the file??? -- Thank you, JRXPL "Dave Peterson" wrote: Try changing the function to: Function CalculateOneA(a, b) CalculateOneA = a + b End Function =CalculateOneA(2,3) Maybe you have something that's conflicting with that name--module name maybe????? (If you did rename the module, change it to something else--Mod_CalculateOne???) JRXPL wrote: Thanks for checking it. It is in the general module for the sheet the cell is in. I even tried typing it in a cell in all sheets. Still not working??? -- Thank you, JRXPL "Dave Peterson" wrote: It worked fine for me. And you did put it in a General module in the workbook's project that held the worksheet with the cell with the formula? JRXPL wrote: Thank you very much- I checked the Macro Security and set it to low. I typed the following into the General module, no ranges, checked spelling. It still doesn't work when I type the below command into the excel cell. Function CalculateOne(a, b) CalculateOne = a + b End Function =CalculateOne(2,3) -- Thank you, JRXPL "Dave Peterson" wrote: Make sure your function is in a General module--not under the ThisWorkbook module, not behind a worksheet module. Make sure you allow macros to run when you open your workbook. And if you use ranges in your formula, make sure you're spelling the addresses correctly. If the problem doesn't go away, then make sure you share what you typed into the cell. ps. I'm assuming the typos in the function are just in the post. In the future, it's better to copy directly from your code and paste into the message. JRXPL wrote: I have written an extremely simple function in VBA module for sheet 1 and when I try to use that function in a cell in sheet 1 I get a *Name? error. Has anyone seen this before? How do I get the function to work? Function CalculateOne (a,b) CalculateOne = 2*a + b EndFunction -- Thank you, JRXPL -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Functions in VBA used in spreadsheet cell
Sheets do not have "general modules".
Workbooks have them. Sheets have "sheet modules" which Dave instructed you to stay away from. Make sure your function is in a General module--not under the ThisWorkbook module, not behind a worksheet module. Remove the function code from the sheet module and place it into a general or standard module. Gord Dibben MS Excel MVP On Fri, 18 Apr 2008 06:43:06 -0700, JRXPL wrote: Thanks for checking it. It is in the general module for the sheet the cell is in. I even tried typing it in a cell in all sheets. Still not working??? |
#11
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Functions in VBA used in spreadsheet cell
Thanks for reading the response better than I did!
Maybe JRXPL (gesundheit!) will see your message. Gord Dibben wrote: Sheets do not have "general modules". Workbooks have them. Sheets have "sheet modules" which Dave instructed you to stay away from. Make sure your function is in a General module--not under the ThisWorkbook module, not behind a worksheet module. Remove the function code from the sheet module and place it into a general or standard module. Gord Dibben MS Excel MVP On Fri, 18 Apr 2008 06:43:06 -0700, JRXPL wrote: Thanks for checking it. It is in the general module for the sheet the cell is in. I even tried typing it in a cell in all sheets. Still not working??? -- Dave Peterson |
#12
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Functions in VBA used in spreadsheet cell
Original was a couple days past so prolly a slim chance.
On Sat, 19 Apr 2008 12:02:38 -0500, Dave Peterson wrote: Thanks for reading the response better than I did! Maybe JRXPL (gesundheit!) will see your message. Gord Dibben wrote: Sheets do not have "general modules". Workbooks have them. Sheets have "sheet modules" which Dave instructed you to stay away from. Make sure your function is in a General module--not under the ThisWorkbook module, not behind a worksheet module. Remove the function code from the sheet module and place it into a general or standard module. Gord Dibben MS Excel MVP On Fri, 18 Apr 2008 06:43:06 -0700, JRXPL wrote: Thanks for checking it. It is in the general module for the sheet the cell is in. I even tried typing it in a cell in all sheets. Still not working??? |
#13
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Functions in VBA used in spreadsheet cell
Thanks for the info everyone, reading over the posts, I tried to create a new
Module and inserted the Function code and it works in this manner. I am unable to get it to work in the code area for (General) or the code area for Worksheet when I dblclick on the worksheet or the code area for Workbook when I dblclick on the project. Sorry for the misunderstanding, but is this the way it is suppose to work? I feel like I am missing a basic concept. I have tried this on two different computer systems. Thanks again. -- Thank you, JRXPL "Gord Dibben" wrote: Original was a couple days past so prolly a slim chance. On Sat, 19 Apr 2008 12:02:38 -0500, Dave Peterson wrote: Thanks for reading the response better than I did! Maybe JRXPL (gesundheit!) will see your message. Gord Dibben wrote: Sheets do not have "general modules". Workbooks have them. Sheets have "sheet modules" which Dave instructed you to stay away from. Make sure your function is in a General module--not under the ThisWorkbook module, not behind a worksheet module. Remove the function code from the sheet module and place it into a general or standard module. Gord Dibben MS Excel MVP On Fri, 18 Apr 2008 06:43:06 -0700, JRXPL wrote: Thanks for checking it. It is in the general module for the sheet the cell is in. I even tried typing it in a cell in all sheets. Still not working??? |
#14
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Functions in VBA used in spreadsheet cell
One more time..................
With your workbook open, hit Alt + F11 to open the Visual Basic Editor window. CTRL + r to open the Project Explorer. DO NOT doubleclick on anything!! Right-click on your workbook(project) and InsertModule. Paste this into that module........... Function CalculateOne(a, b) CalculateOne = a + b End Function FileSave the workbook. Alt + q to return to Excel window. In any cell enter =Calculateone(2,3) which returns 5 Make sure you have ToolsOptionsCalculation set to "Automatic" Gord On Sun, 20 Apr 2008 11:54:00 -0700, JRXPL wrote: Thanks for the info everyone, reading over the posts, I tried to create a new Module and inserted the Function code and it works in this manner. I am unable to get it to work in the code area for (General) or the code area for Worksheet when I dblclick on the worksheet or the code area for Workbook when I dblclick on the project. Sorry for the misunderstanding, but is this the way it is suppose to work? I feel like I am missing a basic concept. I have tried this on two different computer systems. Thanks again. |
#15
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Functions in VBA used in spreadsheet cell
Thank you,
JRXPL "Gord Dibben" wrote: One more time.................. With your workbook open, hit Alt + F11 to open the Visual Basic Editor window. CTRL + r to open the Project Explorer. DO NOT doubleclick on anything!! Right-click on your workbook(project) and InsertModule. Paste this into that module........... Function CalculateOne(a, b) CalculateOne = a + b End Function FileSave the workbook. Alt + q to return to Excel window. In any cell enter =Calculateone(2,3) which returns 5 Make sure you have ToolsOptionsCalculation set to "Automatic" Gord On Sun, 20 Apr 2008 11:54:00 -0700, JRXPL wrote: Thanks for the info everyone, reading over the posts, I tried to create a new Module and inserted the Function code and it works in this manner. I am unable to get it to work in the code area for (General) or the code area for Worksheet when I dblclick on the worksheet or the code area for Workbook when I dblclick on the project. Sorry for the misunderstanding, but is this the way it is suppose to work? I feel like I am missing a basic concept. I have tried this on two different computer systems. Thanks again. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel spreadsheet print functions | Excel Worksheet Functions | |||
8 Functions in a spreadsheet program | Excel Discussion (Misc queries) | |||
Spreadsheet functions | Excel Discussion (Misc queries) | |||
Functions not working in Spreadsheet | Excel Worksheet Functions | |||
Spreadsheet functions | Excel Worksheet Functions |