Unable to populate a range with a formula

[MichaelDavid]

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

 

[Joel]

SetAC2 is a function and need the parethesis

Range("AC2:AC10").Formula = "=SetAC2()"

 

[MichaelDavid]

Hi Joel:
I added () like you suggested, and still get #Name? populated
thruout the range. What should I try next?
--
May you have a most blessed day!

Sincerely,

Michael Fitzpatrick

 

[MichaelDavid]

By the way, I tried the example on page 18 of "Excel 2007 VBA Programmer's
Reference" (ISBN 978-0-470-04643-2). The example is the creation of a UDF
(User Defined Function) to convert degrees Centigrade to degrees Fahrenheit.
The code is as follows, and I placed it in a module in my Personal Macro
Notebook:

Function Fahrenheit(Centigrade)
Fahrenheit = Centigrade * 9 / 5 + 32
End Function

Column A of the worksheet has the Centigrade values to be converted, and
Column B is to have the resultant Fahrenheit degrees. When I set B2 on the
worksheet to =Fahrenheit(A2) as per the example in the book, B2 displays as
#Name?. Perhaps there is a setting in my Excel setup which prohibits setting
B2 in this way. I tried this example also with Excel 2003 on another computer
with the same results.
--
May you have a most blessed day!

Sincerely,

Michael Fitzpatrick

 

[MichaelDavid]

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

 

[Joel]

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.

 

[Dave Peterson]

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]

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

 

[MichaelDavid]

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

 

[Dave Peterson]

What happened when you tried my suggestion?

Did it work or did it fail?

 

[MichaelDavid]

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

 

[MichaelDavid]

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