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
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
Check your book, I'll bet that the lineOriginally 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
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.Thank you for repeating what I just said
If he thinks it is solved he could, but that is for him to determine.
How do I mark it solved????
No I double checked it, it is typed as
[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)
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
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?
Really? Which book is it? Have you informed the publisher?
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 ...
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]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Posting Permissions
Reply With Quote
