I am not new to programming, just new to VBA and I am having difficulties that I never expected. I thought that I could jazz up my Excel spreadsheets as an intellectual exercise in my retirement

It's been 2 months and I am still in limbo.

After buying about 3 books on Excel and VBA I Finally bought a copy of Excel 2007 VBA Programmer's Reference by Green et al and I managed see a glimmer of light at the end of the tunnel. Terms like Objects, collections, properties, and events I understand now.

I still have questions specific to "The Book" but wonder whether I am trying to run before I walk (to date I am on Page 27- not half way through chapter1.)

Q1: the UDF example on page 18:

Function Fahrenheit(Centigrade)

Fahrenheit = Centigrade * 9 / 5 + 32

End Function

will not perform for me as the text says. I have created a column of values from A1to A10, named that Range as "centigrade" (actually the whole of Column A). I have Named the Range B as "Fahrenheit".

Manually Inserted ="Fahrenheit"(A2) in cell B2

Which results in #REF? when "Fahrenheit" has been defined as a Name and #NAME? without defined name set up.

I loaded the Analysis ToolPak -VBA ADD in on the advice of one those gurus on Google (Pearson-I Think). Still Nothing worked.

Well it did for about 5 hours yesterday before I gave up.

Now that I've joined this forum and asked the questions it suddenly answered my prayers and runs just as expected.

Which raises the issue:

Why not work for 5 hours of experimentation. and immediate success
when I delete the name "Fahrenheit" from the Name Manager a day later.

I understand what a UDF is and does and how to create and access it .

My original question was to be "why it no work ?" but now has to be "why work now and not then ?"

Q2: Refers to the code on page 26

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Rows.Interior.ColorIndex = xlColorIndexNone
Target.EntireColumn.ColorIndex = 36
Target.EntireRow.ColorIndex = 36
End Sub


At this point in time when I click a cell or select a group of cells ......

.....results in a message "Runtime error '438': Object does not support this property or method.

I haven't spent much time on this one. I expect the answer will be in the other 1000 and seventy or so pages of the book.

If anybody can enlighten me then I'd be truly grateful.

mrgrump

fahrenheit is not the name of the cell, it's the name of the function, so it should be =fahrenheit(a2) with no quotation marks

Thank you so much.
I finally got there on my own but did not understand why. I discovered that I didn't need to go through naming the ranges on the excel sheet as well. It's a part of the learning curve.