Excel VBA Macro for Upper Case Text Formatting

I find myself updating some documentation for the menu system I enhanced yesterday. While updating a spreadsheet, I spied the following vba macro. I thought you might find it handy for formatting selected cells.

Place the following in your “ThisWorkbook” module inside the vba editor.

Option Explicit

Sub Upper_Case()

‘ Upper_Case Macro
‘ Change Value of Selection to upper case

‘ Keyboard Shortcut: Ctrl+t

Dim myCell As Range
Set myCell = Application.ActiveCell
With myCell
.Value = UCase(.Text)
End With
End Sub

Then assign a shortcut to run the routine. (I use Ctrl+Shift+T) Then as you work in excel, you can select a cell that you want to format as upper case text and press your shortcut to reformat it.

…now back to work!

VBA Quick Tip

I am writing a quick select case this am and remembered something I thought I’d share with you. Don’t worry about closing quotes around strings when it is the last character on the line. The VBA editor will do it for you when you hit the enter button. So my shortcut was to copy the beginning of the plotstyle name and Case statement to the clipboard and for each new line I simply paste, add the end of the style name and hit “Enter” key. The VBIDE adds the close quote for me.

Here is what I paste from the clipboard – Case “Black-
I added the word: XBold and hit return. The result is shown below:

Case “Black-XBold”

Legacy VBA Error – For next loop may really mean If end if.

I was updating some legacy VBA code today and had to add some additional logic to a function. When I click the run button, I was faced with this dialog box
A quick check of the code indicates that there IS a closing “NEXT” statement for the “For Each…” loop. When this happens to you, check any “If….” logic constructs within the “For … Next” loop. Chances are you’ll find a missing “End if” statement.

ACA 2009 VBA Macro – Room Zoom Available

R2009Find_Room_Zoom2.dvb is a VBA Macro that displays a modeless userform on the screen with a text box and a button labeled “Find”. When you enter a room or space number in the text box and click the “Find” button, it will cycle through all the available space/room objects in the current drawing and any attached xref files and zoom your display to center on that space or room with the corresponding number. This macro originated in Autodesk Architectural Desktop (ADT) R3.3 and is now updated for Autodesk AutoCAD Architectural (ACA) Release 2009.

Take a look at the code within the macro to see how to:

  • Use the acfocus control (allows you to keep the form visible on the screen while you interact with entities within your file.
  • Find the schedule property and get the associated space or room object.

You’ll find more helpful utilities in past posts here and at AUGI.com, R U a Member?

VSTA for Revit Architecture Install – (Think VBA’s replacement!)

I installed the recently received Autodesk Revit Architecture 2009 application on my laptop the other day. In browsing the DVD, I noticed a folder titled VSTA. If you want to install the Visual Studio for Applications for use with Revit, pick the bottom installer option for tools and utilities as shown below.

I didn’t see any mention of this in the documentation, readme, or the installer for the basic product. I did see reference and tutorials in the SDK install, but one has to know to look for these. I found a cryptic mention of this in the New Features Workshop. If you look in the Revit Architecture Help file, you’ll find mention of “Creating Macros with Revit VSTA”…grab Gregory Arkin’s pdf version using the link below.

Install Steps:

  1. Install the Revit Product First
  2. Go back to first page of installer and then install the VSTA tools.

The install is much improved in this release…much faster too!

Now lets see how it runs…..

Note: The product install forces an install of Design Review 2009. This may impact those of you running the BIM Smart Library by Reed Construction Data. If you create a deployment, you can potentially modify the installation.

Links: Looks like other sites have more info on this.
Check out

Use VBA to detach unloaded External References (XREFs)

Often when archiving or sending files to consultants, it is desireable to do some house cleaning prior to transmitting them. A common practice in cases like these would be to detach any unnecessary XRefs. And what is more unnecessary than a previously unloaded xref?

Public Sub DetachUnloadedXrefs()
Dim o_Blk As AcadBlock
For Each o_Blk In ThisDrawing.Blocks
If o_Blk.IsXRef And o_Blk.Count = 0 Then
ThisDrawing.Utility.Prompt o_Blk.Name & _
” is an unloaded xref and has been detached.” & vbCrLf
End If
End Sub

Hope you find that useful.

Updated for 2008: Routine to Copy Layer Keys Between Layer Key Styles

Need to migrate your layer keys in AutoCAD Architecture 2008? I updated the routine to work in 2008. Get it here.