Consulting

Results 1 to 2 of 2

Thread: Excel 2011 VBA Help system

  1. #1
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,778

    Excel 2011 VBA Help system

    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.

  2. #2
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,778
    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.
    [VBA]Private Sub Workbook_Open()
    With ThisWorkbook.VBProject.VBE.CommandBars
    .FindControl(Id:=522).Execute
    End With
    End Sub[/VBA]

    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.
    [VBA]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[/VBA]
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •