PDA

View Full Version : Excel 2011 VBA Help system



mikerickson
11-06-2010, 11:32 AM
I've installed Excel 2011. Hurah it has VBA. The Ribbon is not as objectionable as I feared. But ... the VBEditor Help system is not making me happy. For example, I can't seem to find help on the Dir function.

But, I still have 2004 installed. Is there some way that I can invoke 2004 VB Help from 2011?

Also, is there anyway that I can disable the feature that when I'm entering a function (worksheet or VBA) a window pops up showing me the expected arguments?

Thanks.

mikerickson
11-28-2010, 03:45 PM
Well, I've come up with a work-around. Actualy two.

Issue ONE:

When one checks the Require Variable Declarations button in 2011 VBEditor Preferences (automatic Option Explicit) is does not persist. One has to check it again the next time Excel is opened.

The work-around is this routine that invokes that dialog screen when the Personal Macro Workbook is opened. The effect on the user is similar to the Enable Macros screen one sees when opening a workbook (but with less purpose).
Put this in the Personal Macro Workbook's ThisWorkbook code module.
Private Sub Workbook_Open()
With ThisWorkbook.VBProject.VBE.CommandBars
.FindControl(Id:=522).Execute
End With
End Sub

Issue TWO:

VBA Help is apparently non-existant for 2011, on board or on line.

The workaround is to create a workbook in 2004 that has a macro which opens the VBEditor and invokes the Help menu, giving you access to 2004's on-board Help. Then an AppleScript can open that version of Excel, open the workbook and trigger the macro. Not slick, but its the quickest way to get to VBA help from 2011.
Create a workbook in 2004, with this code in a normal module.
Sub InvokeVBEditorHelp()
Dim openVBEditorControl As Object, VBEditorHelpControl As Object

Set openVBEditorControl = Application.CommandBars.FindControl(Id:=1695)

Set VBEditorHelpControl = ThisWorkbook.VBProject.VBE.CommandBars.FindControl(Id:=984)

openVBEditorControl.Execute
VBEditorHelpControl.Execute
End Sub
Then (after adjusting file paths) this AppleScript will open that file in 2004 and 2004 VBA Help will appear.

tell application "Macintosh HD:Applications:MSOld:Microsoft Office 2004:Microsoft Excel"
try
activate
if name of workbook "Excel2004VBHelp.xls" = "Excel2004VBHelp.xls" then
set HelpBook to workbook "Excel2004VBHelp.xls"
else
open "Macintosh HD:Applications:MSOld:Excel2004VBHelp.xls"
set HelpBook to active workbook
end if
tell HelpBook
run VB macro "InvokeVBEditorHelp"
end tell
on error
display dialog "Applescript error"
end try
end tell