PDA

View Full Version : Solved: Code from a book



Djblois
08-04-2006, 07:26 AM
When ever I type code from a book it never works. I type it exactly as printed in the book. I found some code that will change my highlighted selection into Proper case but it is giving me a Sub or Function not defined on the Proper.

Sub ConvertToProper()
Dim cellObject As Range
For Each cellObject In Selection
cellObject.Formula = WorksheetFunction(Proper(cellObject.Formula))
Next

End Sub

jungix
08-04-2006, 07:42 AM
You must also define the Proper function, which looks like a user-defined function and not an Excel built-in one.

Djblois
08-04-2006, 07:53 AM
Isn't proper a Function? It is to set Proper Case.

jungix
08-04-2006, 08:02 AM
Sorry I did not know this function. You must have copied your formula wrong or your book is bad. Try this code instead:



Sub ConvertToProper()
Dim cellObject As Range
For Each cellObject In Selection
cellObject.Formula = WorksheetFunction.Proper(cellObject.Formula)
Next

End Sub

lucas
08-04-2006, 09:01 AM
jungix's code works for me

Bob Phillips
08-04-2006, 10:13 AM
When ever I type code from a book it never works. I type it exactly as printed in the book. I found some code that will change my highlighted selection into Proper case but it is giving me a Sub or Function not defined on the Proper.

Sub ConvertToProper()
Dim cellObject As Range
For Each cellObject In Selection
cellObject.Formula = WorksheetFunction(Proper(cellObject.Formula))
Next

End Sub

Check your book, I'll bet that the line



cellObject.Formula = WorksheetFunction(Proper(cellObject.Formula))


is actually written as



cellObject.Formula = WorksheetFunction.Proper(cellObject.Formula)

jungix
08-04-2006, 10:43 AM
Thank you for repeating what I just said :rotlaugh:

Djblois you could mark this thread as solved.

Bob Phillips
08-04-2006, 12:24 PM
Thank you for repeating what I just said :rotlaugh:

Well your first answer was incomprehensible, and the second came closer, so I thought it best to be clear and specific.


Djblois you could mark this thread as solved.

If he thinks it is solved he could, but that is for him to determine.

Djblois
08-04-2006, 12:28 PM
How do I mark it solved????

Djblois
08-04-2006, 12:29 PM
Check your book, I'll bet that the line



cellObject.Formula = WorksheetFunction(Proper(cellObject.Formula))


is actually written as



cellObject.Formula = WorksheetFunction.Proper(cellObject.Formula)


No I double checked it, it is typed as



cellObject.Formula = WorksheetFunction(Proper(cellObject.Formula))

jungix
08-04-2006, 12:39 PM
Go to thread tools, and tick solved.

Your book is wrong then, because the correction I suggested worked on my computer whereas yours gave me an error.

xld, you're right my first message was wrong, but the second said exactly the same as yours

Djblois
08-04-2006, 12:56 PM
I know you were correct. Your code worked, so I corrected it in my book.
Thank you,
Daniel

Cyberdude
08-04-2006, 07:31 PM
I might add that instead of using the lengthy "WorksheetFunction", you can use:
Msgbox Application.Proper("abcdef")

johnske
08-04-2006, 09:13 PM
Or... Msgbox StrConv("abcdef", vbProperCase) ... (as we are working in visual basic in this case and there IS a visual basic function for this)

Bob Phillips
08-05-2006, 07:41 AM
I might add that instead of using the lengthy "WorksheetFunction", you can use:
Msgbox Application.Proper("abcdef")

Dude,

sometimes it is appropriate to use Application, sometimes you have to use WorksheetFunction. I tend to use Application as it is easier to test for success IMO. I once wrote a tome on the subject, sad isn't it?

Bob Phillips
08-05-2006, 07:42 AM
No I double checked it, it is typed as



cellObject.Formula = WorksheetFunction(Proper(cellObject.Formula))


Really? Which book is it? Have you informed the publisher?

geekgirlau
08-07-2006, 01:57 AM
About 100 years ago I started writing macros in WordPerfect for DOS. For anyone who has dabbled in these, you may remember the dreaded ~ character that indicated the end of built-in functions. This was the source of great heartache for new (and not-so-new) developers, in making sure that they were in the right position.

I remember seeing a book published on WP macros that did not have a SINGLE ~ character in any of the code samples. Near the start of the book was a statement along the lines of insert the ~ character where appropriate ... :confused3

mdmackillop
08-07-2006, 01:13 PM
Even the Help files get it wrong. Have you come across this teaser?

The following code illustrates how to create a Dictionary object:
Dim d 'Create a variable
Set d = CreateObject(Scripting.Dictionary)
d.Add "a", "Athens" 'Add some keys and items
d.Add "b", "Belgrade"
d.Add "c", "Cairo"