Excel

Open The Visual Basic Editor Programmatically

Ease of Use

Easy

Version tested with

2002 

Submitted by:

Scottie P

Description:

Simple VBA that takes the user directly to a defined sub in the VBE with one click! 

Discussion:

You want to send some VBA to a first time VBA User who does not have the ability to surf the web, but they can send and receive email. Here is a sample VBA procedure for her use, but there is one line in the code that requires her input. I do not have to spend time explaining how to open the VBE and then find the code she needs to find. I can simply tell her to click the GOTO VBE button and provide her input there. The sample file contains VBA that allows the user to go directly to an undefined area in the VBE as well. 

Code:

instructions for use 

			
Option Explicit 
 
 '// This code will take you to the Sub (in the VBE) defined in the .Goto line
 '// in this case:  .Goto "test1"
Sub test1() 
    With Application 
        .Goto "test1" 
    End With 
End Sub 
 
 '// This code will generically open the VBE
Sub test2() 
    Application.VBE.MainWindow.Visible = True 
End Sub 
 '
 '// This code *-test2-* will also work in MS Word

How to use:

  1. Copy the above code.
  2. Open any workbook and press Alt + F11. The VBE (Visual Basic Editor) will open.
  3. From the menus, select Insert > Module; when the module appears, paste the copied code into the code pane on the right side of the VBE.
  4. Close the VBE using the red X at the upper right.
 

Test the code:

  1. From the menus, select Tools > Macro > Macros and double-click "test1". You should now find yourself looking at the VBE, with your cursor blinking in the "test1" procedure.
 

Sample File:

OpenVBE.zip 6.81KB 

Approved by MOS MASTER


This entry has been viewed 249 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express