Unable to populate a range with a formula
in [Programming]
|
Prev: Launching PowerPoint from an Excel application
Next: MSCOMCT2.OCX registration error "ClassFactory cannot supply requested class"
From: MichaelDavid on 7 Oct 2008 19:02 Hi Dave: PROBLEM SOLVED! The code for a UDF should be placed in a standard code module, not in one of the Sheet modules, not in the ThisWorkbook module, and not in the Personal Workbook Module. I had the code in my Personal Workbook Module. What I did: In the VBA editor, I went to the Insert menu and chose Module. A new code module was inserted into the project. Then I added the following code: Sub GetConv2() Range("F2:F12").Formula = "=Fahrenheit(E2)" Exit Sub End Sub Function Fahrenheit(Centigrade) Fahrenheit = Centigrade * 9 / 5 + 32 End Function The Worksheet had the following in columns E and F: E F Centigrade Fahrenheit 0 10 20 30 40 50 60 70 80 90 100 I then executed Sub GetConv2(). The result is copied below: E F Centigrade Fahrenheit 0 32 10 50 20 68 30 86 40 104 50 122 60 140 70 158 80 176 90 194 100 212 I really did not think we would win this one. To think that my mistake is that I placed the code for the function in the Personal Workbook rather than in a Standard Code Module. Microsoft should be scolded for their unhelpful error messages. Something simple like: "Don't use the Personal Workbook for User Defined Functions" would have save me about 12 hours of research, trial, and error. Who would have guessed that it was all a matter of which module you put your code into? -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Dave Peterson" wrote: > What happened when you tried my suggestion? > > Did it work or did it fail? > > MichaelDavid wrote: > > > > Greetings! I spoke to quickly. Range("A2:A10").Formula = SetAC2 only sets the > > value of the function, and does not propagate the function call thruout the > > range. It is beginning to look like calls to functions which set the function > > to a value can not be used in a worksheet. I was hoping that something like: > > Range("AC2:AC10").Formula = "=SetAC2" would work, but it just gives #Name? > > thruout the range. I am hoping that someone in this group will show me the > > correct way to propagate a function call thruout a range. > > -- > > May you have a most blessed day! > > > > Sincerely, > > > > Michael Fitzpatrick > > > > "MichaelDavid" wrote: > > > > > Hi Dave: > > > Thru extensive trial and error, I got this to work. Here is the solution: > > > > > > Sub TestSetAC2() > > > > > > Range("A2:A10").Formula = SetAC2 > > > Exit Sub > > > End Sub > > > > > > Function SetAC2() > > > > > > SetAC2 = 5 > > > End Function > > > > > > In other words, we need to use: > > > Range("A2:A10").Formula = SetAC2 > > > and not: > > > Range("AC2:AC10").Formula = "=SetAC2" > > > > > > But why? Does anyone on this group know? (Perh. it has something to do with > > > whether one uses .Formula or .FormulaR1C1.) > > > -- > > > May you have a most blessed day! > > > > > > Sincerely, > > > > > > Michael Fitzpatrick > > > > > > > > > "Dave Peterson" wrote: > > > > > > > If that setac2 function is in the the same workbook with TestSetAc2, but not the > > > > blank workbook you just opened, then you'll want to specify where to find that > > > > function: > > > > > > > > Sub TestSetAC2() > > > > activesheet.Range("AC2:AC10").Formula _ > > > > = "='" & thisworkbook.name & "'!SetAC2()" > > > > End Sub > > > > > > > > MichaelDavid wrote: > > > > > > > > > > Greetings! This one has me stumped. I am trying to populate a range with a > > > > > formula. The code is: > > > > > > > > > > Sub TestSetAC2() > > > > > Range("AC2:AC10").Formula = "=SetAC2" > > > > > Exit Sub > > > > > End Sub > > > > > > > > > > Function SetAC2() > > > > > SetAC2 = 5 > > > > > End Function > > > > > > > > > > First I opened a blank worksheet. When I execute the procedure, I get #Name? > > > > > thruout the range. The tool tip just to the left of #Name? says that the > > > > > formula contains unrecognized text. Any help will be greatly appreciated. > > > > > -- > > > > > May you have a most blessed day! > > > > > > > > > > Sincerely, > > > > > > > > > > Michael Fitzpatrick > > > > > > > > -- > > > > > > > > Dave Peterson > > > > > > -- > > Dave Peterson >
From: MichaelDavid on 7 Oct 2008 20:28
We both came up with the solution at about the same time (12:47 PM vs 12:55 PM PST), but I didn't get a chance to read your posting until just now (5:25 PM PST). As I mentioned in the post I wrote when I finally solved the problem: "PROBLEM SOLVED! The code for a UDF should be placed in a standard code module, not in one of the Sheet modules, not in the ThisWorkbook module, and not in the Personal Workbook Module. I had the code in my Personal Workbook Module. What I did: In the VBA editor, I went to the Insert menu and chose Module. A new code module was inserted into the project. Then I added the following code: Sub GetConv2() Range("F2:F12").Formula = "=Fahrenheit(E2)" Exit Sub End Sub Function Fahrenheit(Centigrade) Fahrenheit = Centigrade * 9 / 5 + 32 End Function The Worksheet had the following in columns E and F: E F Centigrade Fahrenheit 0 10 20 30 40 50 60 70 80 90 100 I then executed Sub GetConv2(). The result is copied below: E F Centigrade Fahrenheit 0 32 10 50 20 68 30 86 40 104 50 122 60 140 70 158 80 176 90 194 100 212 I really did not think we would win this one. To think that my mistake is that I placed the code for the function in the Personal Workbook rather than in a Standard Code Module. Microsoft should be scolded for their unhelpful error messages. Something simple like: "Don't use the Personal Workbook for User Defined Functions" would have save me about 12 hours of research, trial, and error. Who would have guessed that it was all a matter of which module you put your code into? Thank you very much for your post. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Joel" wrote: > The parethesis is definetly needed. If you are still gettting #NAME then is > can't find the function. With Excel 2003 this is usually becvause the > function has been put into the wrong type Page in the VPA Project or is in > the wrong workbook (like personal.xls). Make sure you put the Function into > a MODULE sheet. > > "MichaelDavid" wrote: > > > Another possibility: Perhaps, with certain Excel setups, one has to register > > a new User Defined Function with Excel. > > -- > > May you have a most blessed day! > > > > Sincerely, > > > > Michael Fitzpatrick > > > > > > "MichaelDavid" wrote: > > > > > Greetings! This one has me stumped. I am trying to populate a range with a > > > formula. The code is: > > > > > > Sub TestSetAC2() > > > Range("AC2:AC10").Formula = "=SetAC2" > > > Exit Sub > > > End Sub > > > > > > Function SetAC2() > > > SetAC2 = 5 > > > End Function > > > > > > First I opened a blank worksheet. When I execute the procedure, I get #Name? > > > thruout the range. The tool tip just to the left of #Name? says that the > > > formula contains unrecognized text. Any help will be greatly appreciated. > > > -- > > > May you have a most blessed day! > > > > > > Sincerely, > > > > > > Michael Fitzpatrick |