 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / April 2008Functions 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  SignatureThank you, JRXPL 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. > 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. [quoted text clipped - 7 lines] > Thank you, > JRXPL SignatureDave Peterson 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) SignatureThank you, JRXPL > Make sure your function is in a General module--not under the ThisWorkbook > module, not behind a worksheet module. [quoted text clipped - 21 lines] > > Thank you, > > JRXPL 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? > Thank you very much- > [quoted text clipped - 41 lines] > > > > Dave Peterson SignatureDave Peterson 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??? SignatureThank you, JRXPL > It worked fine for me. > [quoted text clipped - 46 lines] > > > > > > Dave Peterson 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???) > 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??? [quoted text clipped - 56 lines] > > > > Dave Peterson SignatureDave Peterson Thanks- Did a copy paste. Same result. I did notice that Excel runs a virus scan when I open the file??? SignatureThank you, JRXPL > Try changing the function to: > [quoted text clipped - 69 lines] > > > > > > Dave Peterson I'm out of guesses. Sorry. > Thanks- > [quoted text clipped - 82 lines] > > > > Dave Peterson SignatureDave Peterson Thanks for trying. Much appreciated. SignatureThank you, JRXPL > I'm out of guesses. > [quoted text clipped - 86 lines] > > > > > > Dave Peterson 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 >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??? Thanks for reading the response better than I did! Maybe JRXPL (gesundheit!) will see your message. > Sheets do not have "general modules". > [quoted text clipped - 12 lines] > >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??? SignatureDave Peterson Original was a couple days past so prolly a slim chance. >Thanks for reading the response better than I did! > [quoted text clipped - 16 lines] >> >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??? 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. SignatureThank you, JRXPL > Original was a couple days past so prolly a slim chance. > [quoted text clipped - 18 lines] > >> >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??? 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 Insert>Module. Paste this into that module........... Function CalculateOne(a, b) CalculateOne = a + b End Function File>Save the workbook. Alt + q to return to Excel window. In any cell enter =Calculateone(2,3) which returns 5 Make sure you have Tools>Options>Calculation set to "Automatic" Gord >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. [quoted text clipped - 8 lines] > >Thanks again. Thank you, JRXPL > One more time.................. > [quoted text clipped - 34 lines] > > > >Thanks again.
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.