Printable View
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.
[VBA]Sub ConvertToProper()
Dim cellObject As Range
For Each cellObject In Selection
cellObject.Formula = WorksheetFunction(Proper(cellObject.Formula))
Next
End Sub[/VBA]
You must also define the Proper function, which looks like a user-defined function and not an Excel built-in one.
Isn't proper a Function? It is to set Proper Case.
Sorry I did not know this function. You must have copied your formula wrong or your book is bad. Try this code instead:
[VBA]Sub ConvertToProper()[/VBA]
Dim cellObject As Range
For Each cellObject In Selection
cellObject.Formula = WorksheetFunction.Proper(cellObject.Formula)
Next
End Sub
jungix's code works for me
Check your book, I'll bet that the lineQuote:
Originally Posted by Djblois
[vba]
cellObject.Formula = WorksheetFunction(Proper(cellObject.Formula))
[/vba]
is actually written as
[vba]
cellObject.Formula = WorksheetFunction.Proper(cellObject.Formula)
[/vba]
Thank you for repeating what I just said :rotlaugh:
Djblois you could mark this thread as solved.
Well your first answer was incomprehensible, and the second came closer, so I thought it best to be clear and specific.Quote:
Originally Posted by jungix
If he thinks it is solved he could, but that is for him to determine.Quote:
Originally Posted by jungix
How do I mark it solved????
No I double checked it, it is typed asQuote:
Originally Posted by xld
[vba]
cellObject.Formula = WorksheetFunction(Proper(cellObject.Formula))
[/vba]
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
I know you were correct. Your code worked, so I corrected it in my book.
Thank you,
Daniel
I might add that instead of using the lengthy "WorksheetFunction", you can use:
[vba]Msgbox Application.Proper("abcdef")[/vba]
Or... Msgbox StrConv("abcdef", vbProperCase) ... (as we are working in visual basic in this case and there IS a visual basic function for this)
Dude,Quote:
Originally Posted by Cyberdude
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?
Really? Which book is it? Have you informed the publisher?Quote:
Originally Posted by Djblois
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
Even the Help files get it wrong. Have you come across this teaser?
The following code illustrates how to create a Dictionary object:
[VBA]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"
[/VBA]