Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA 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 August 18th, 2009, 04:18 PM
Registered User
 
Join Date: Apr 2009
Posts: 3
Thanks: 1
Thanked 0 Times in 0 Posts
Cool VBA Code not working in 2007

I have recently installed Office 2007. I have a program used by more than just myself and I'm getting errors such as "Can't find project or library" and the code highlights both "UCase" and "Trim" so far. Are these functions no longer used in 2007? I have to be able to run the code for production purposes and I'm not sure how to work around this problem. This "program" was something I created quite a few years ago under a much earlier version of Excel and I've never had a problem running this particular code over the years until now. Any help would be appreciated. A sample is below:

Code:
 With Range("E5:F7")
        .Merge
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Font.Name = "Arial"
        .Font.Size = 9
        .Formula = _
        "ONE (1) " & Worksheets("Misc Calcs").Range("BF37").Text & " " & UCase  (Worksheets("Cust Info").Range("C18").Text) & " VIBRATING CONVEYOR"
    End With
The code halts at UCase...

Thanks much!
 
Old August 19th, 2009, 01:33 AM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

Hi

Both these functions are Part of VBA's String class

Can you try using Strings.UCase etc

Cheers
Shasur
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)
The Following User Says Thank You to Shasur For This Useful Post:
blue9244 (August 19th, 2009)
 
Old August 19th, 2009, 09:59 AM
Registered User
 
Join Date: Apr 2009
Posts: 3
Thanks: 1
Thanked 0 Times in 0 Posts
Thumbs up Thanks!

That did the trick! I wonder why it's now necessary to add the class when it wasn't required in previous versions of Excel. Any ideas? I had the same problem with Date and had to add DateTime to make it work.

Thanks very much for the suggestion and solution! You rock!
 
Old August 20th, 2009, 03:16 AM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

Hi

Was there any multiple versions of Office installed in your machine? This might be one possible cause of the problem. The other might be version difference between the referenced DLLs



Cheers
Shasur
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)
 
Old August 20th, 2009, 09:11 AM
Registered User
 
Join Date: Apr 2009
Posts: 3
Thanks: 1
Thanked 0 Times in 0 Posts
Default Multiple copies of Excel

Well I did have a trial version installed initially and then I upgraded it to the full version. Perhaps that was the cause. I don't know anything about the DLL versions...

Thanks very much for our help!





Similar Threads
Thread Thread Starter Forum Replies Last Post
Using VBA to Search in Access 2007 culleng Visual Basic 2008 Essentials 1 May 25th, 2009 09:30 PM
Word 2007, undo, VBA FredrikH Word VBA 0 April 16th, 2009 04:20 AM
excel 2007 vba UDF DavidReese BOOK: Access 2007 VBA Programmer's Reference ISBN: 978-0-470-04703-3 1 February 24th, 2009 01:10 AM
Excel 2007 VBA PRogrammers Reference SteveB BOOK: Excel 2007 VBA Programmer's Reference ISBN: 978-0-470-04643-2 18 February 12th, 2009 06:13 AM
TreeView in VBA (Access 2007) linhqhta BOOK: Access 2007 VBA Programmer's Reference ISBN: 978-0-470-04703-3 1 August 6th, 2008 04:14 AM





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