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:
Fahrenheit = Centigrade * 9 / 5 + 32
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
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.