Archive for the ‘Visual Basic Editor’ Category.
Mathew asks:
Where do you find the field names for Outlook, i.e. FirstName. Yours are practically about the only ones I can get to work. Strangely, I can’t get Business Phone (s) or faxes to work.
The answer: Use the object browser. Here’s how. First, make sure you have a reference set to the Outlook Object Library.
If you’re looking for something inside Excel or VBA, those references are set automatically, so you can skip that last step. Next, choose Object Browser from View menu (or hit F2).
From the Project/Libraries drop down, choose Outlook
This will show only the classes from that library. In the classes list, choose ContactItem. Then browse the Members list to see what’s available.
The box just below the Project/Libraries drop down is a search box. When you have an inkling of what you want, but don’t quite know the proper terms, you can use search to narrow things down.
There are some days that I feel like I’m using Excel for the first time. Yesterday was one of them. The menu and toolbars were gone from the Visual Basic Editor. View > Toolbars didn’t work. I couldn’t right click on anything to Customize the toolbars. From the Immediate Window, I determined that the Height, Visible, and Enabled properties were all what they should be. I could not figure out where the darn toolbars went.
I closed the VBE, but did not close Excel. I reopened the VBE (Alt+F11) and all the toolbars were back. Strange.
I tried to use EnableCancelKey to allow myself to stop the music and still make the appropriate API calls to reset the midi out. It didn't work so well for me. Here's another way.
I created two global variables. One to determine whether the procedure was already running and one to determine if the user wants to stop the procedure.
Public gbUserCancel As Boolean
Public gbAppStarted As Boolean
These are declared in a standard module outside of any procedures so that they're accessible from anywhere in the project.
Next, I modified the click event of the button that's used to start the procedure.
Private Sub CommandButton1_Click()
If gbAppStarted Then
gbUserCancel = True
gbAppStarted = False
Else
gbAppStarted = True
PlayWorksheetNotes
End If
End Sub
If the procedure is running (gbApppStarted is True), then it assumed the user wants to stop the procedure and gbUserCancel is set to True. If the procedure is not running, it's called so that it starts running. Finally, I modified the PlayWorksheetNotes procedure to check the gbUserCancel variable.
Sub PlayWorksheetNotes()
Dim r As Long
On Error GoTo ErrHandler
gbUserCancel = False
For r = 2 To Application.CountA(Range("A:A"))
If gbUserCancel Then Err.Raise 9999
Cells(r, 2).Select
Call PlayMIDI(Cells(r, 1), Cells(r, 2), Cells(r, 3))
DoEvents
Next r
ProcExit:
On Error Resume Next
midiOutReset hMidiOut
'Stop
Exit Sub
ErrHandler:
If Err.Number <> 9999 Then
MsgBox Err.Description
End If
'Stop
Resume ProcExit
End Sub
If the button is clicked while the notes are playing, gbUserCancel is set to True. Inside the loop, that variable is check. Once True, an error is raised and the error handler is called. Any error that's not the one I made up (9999) is shown in a message box. Then the procedure resumes at ProcExit which resets the midi out for the future.
The EnableCancelKey property of the Application object determines what will happen when a user interrupts your code. Users can interrupt code by pressing Esc or Cntl+Break. EnableCancelKey has three possible settings:
- xlDisable - Prevents the user from interrupting. I've never used this and can't think of why I ever would.
- xlInterrupt - Normal operation. The debugger is shown and the code is in debug mode at whichever line it happened to be when it was interrupted.
- xlErrorHandler - Raises error number 18 and reacts just like any other error. If you have error handling set up, it's called.
J-Walk posted about Musical Excel a while back. There was an issue with the midi out API not resetting properly and people could not run the code twice. Mpemba fixed it in the comments with a reset API call, but if the user was to interrupt the code, that API function would never get called.
I downloaded the dueling banjos file from J-Walk, and modified the code. This part was added:
Private Declare Function midiOutReset Lib "winmm.dll" _
(ByVal hMidiOut As Long) As Long
and this part was modified:
Sub PlayWorksheetNotes()
Dim r As Long
On Error GoTo ErrHandler
Application.EnableCancelKey = xlErrorHandler
For r = 2 To Application.CountA(Range("A:A"))
Cells(r, 2).Select
Call PlayMIDI(Cells(r, 1), Cells(r, 2), Cells(r, 3))
DoEvents
Next r
ProcExit:
On Error Resume Next
midiOutReset hMidiOut
Stop
Exit Sub
ErrHandler:
If Err.Number <> 18 Then
MsgBox Err.Description
Else
Debug.Print Err.Description
End If
Stop
Resume ProcExit
End Sub
Setting EnableCancelKey allows the code to exit gracefully and ensures that the midiOutReset call is always made. This resets the midi out and should prevent the problem of not being able to run the code twice. It shouldn't really matter whether you have an On Error statement before or after the EnableCancelKey statement.
Actually, this code would exit gracefully except that I have all those 'Stop' commands in there. Oddly, when I set EnableCancelKey to xlInterrupt, it works every time I press Cntl+Break (that is, the code stops and the debugger opens). When I set EnableCancelKey to xlErrorHandler, pressing Cntl+Break works about one out of every thousand times. That estimate is based on me pressing it about 100 times during code execution and the code executes all the way through nine out of ten times.
If you have the time, download dueling-banjos.xls, modified the code as I have and see if you can interrupt normally.
fzz sez:
Lipstick on a warthog.
That a thing CAN be done doesn't make it a good idea. VBE is a handy debugging environment, but it's not a particularly good editor. Myself, if I need to do thorough editing of VBA code, I write it to text files, use a more capable editor, then read it back into the .xls/.xla file. Even for ad hoc tweaks, it seems easier to copy text from VBE (leaving it selected), paste into a more capable editor, make the changes there, copy the result, paste into VBE replacing the selected (and now modified) text. This gets me regular expressions as well as multiline pattern support. And I could automate it using AutoIt if I weren't so lazy.
Any comment that starts like that deserves my full attention. I've never used any editor except the one that ships with Office (the VBE). If using an external editor is better, then I don't want to be left out in the cold.
If you edit VBA in something other than the built-in editor, leave a comment with which editor you use. If you could also list a few of its good and bad points, that would be peachy.
Thanks.
I'm a full time Excel developer.
That means I spend quite some time writing code in the Visual Basic Editor (VBE).
I don't maintain a real code library, but I do copy lots of code from previous projects into new ones.
Whereas the VBE is quite a nice application to write code in, it does lack some functionality I needed a couple of weeks ago: the ability to do a search and replace operation on multiple lines of code in one go.
For example, many routines in my projects contain an error handling mechanism of some sort. These may contain something like this:
Resume
Else
Resume Next
End If
Now what if I want that to look like this:
Case vbRetry
Resume
Case vbIgnore
Resume Next
Case vbAbort
Resume TidyUp
End Select
I wanted a tool that would let me replace a number of consecutive lines of VBA code with another set of consecutive lines.
Well, here is my first go at it:
Excel VBE Multiline Search And Replace
And here is a screenshot:
Tell me what you think of it!
Regards,
Jan Karel Pieterse
www.jkp-ads.com