Wrox Programmer Forums
BOOK: Excel 2007 VBA Programmer's Reference ISBN: 978-0-470-04643-2
This is the forum to discuss the Wrox book Excel 2007 VBA Programmer's Reference by John Green, Stephen Bullen, Rob Bovey, Michael Alexander; ISBN: 9780470046432
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Excel 2007 VBA Programmer's Reference ISBN: 978-0-470-04643-2 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old September 13th, 2012, 09:29 PM
Registered User
Join Date: Sep 2012
Posts: 2
Thanks: 1
Thanked 0 Times in 0 Posts
Default Do all newcomers have these problems or is it just Me?

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.

Old September 16th, 2012, 10:05 AM
Friend of Wrox
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts

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
The Following User Says Thank You to mtranchi For This Useful Post:
mrgrump (September 16th, 2012)
Old September 16th, 2012, 05:02 PM
Registered User
Join Date: Sep 2012
Posts: 2
Thanks: 1
Thanked 0 Times in 0 Posts
Default Do all newcomers have these problems or is it just Me?

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.

Similar Threads
Thread Thread Starter Forum Replies Last Post
c problems saurabh1983in C++ Programming 6 November 29th, 2005 04:23 AM
validate.asp problems and logon.asp problems p2ptolu Classic ASP Databases 0 February 16th, 2005 02:34 PM
Two Problems goraya C# 2 May 21st, 2004 05:54 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.