Latest Code VBA changes
- Code Explorer fix for bug introduced on 9feb17
- Code Explorer drag drop fix - try to move or copy a module to another project that is not visible in the Code Explorer window, And the mouse hits the top or bottom of the window, the view will not scroll.
- Fixed hyperlink in tooltip not clickable
- Fix for Office versions 2007 and earlier in the use of tabs for code indentation
- Support for different VBA experience levels applied to DAO
- New: Learn VBA license - a low-priced alternative to the Code VBA and add-on 'Pro' licenses - for VBA users with not much experience.
- Support for different VBA experience levels: Beginner, Elementary, Intermediate and VBA Pro
- Customize the Code VBA toolbar
- Improved working of ToolTips, now also for Fragments and code modules procedures the ability to link to online documentation.
- Fixed bug in Code Explorer
- Fixed problem with Date Picker relying on MSCOMCT2.OCX not available in Office 2013+
- More improvements in ToolTip handling
- Better organization of VBA » Strings
- Improved organization of Strings code fragments folder
- Improvements in Set class Now and Nothing
- Moved Constants menu below Variable menu to have easier access to Class and Collection menu
- Fixed disablement of Variable declaration and insertion
- Fixed bug in Quick menu throwing an error
- Fixed export module in Code Explorer
- Quick Menu now selects last used menu item - previously only the correspoding submenu. This makes use faster
- Refined Select Case uses and removed Switch incorrect code sample
- Fixed some Code Explorer bugs for non-english VBE users
- Toolbar top menu entries now have their access keys adapt to the VBE user language to prevent conflicts with used keys by VBE built-in menu. Language adaptation done for German, Spanish, French, Italian, Portuguese and Dutch. If you have a VBE menu in yet another language, please contact us and we will include your language.
- Tooltip texts shortened when interfering with submenus viewing.
- Added easy insertion of UserForm.Show (Alt-User Interaction » Userform » Show » ...
- Fixed bug related to German VBE built-in menu items being in the German language.
- Fix performance issue with the Project submenu
- Removed empty Sample menu
- Toolbar changes
- Icons on toolbar replaced by menus which better reflects their nature and allows keyboard access for improved speed. For example Alt-CN to open the the Name menu now can be reduced to Alt-N.
- The menus on toolbar reflect the major groups of programming constructs.
- Declare split in two distinct menus, Procedure and Variable gives faster access.
- Tooltips improvements
- Now takes into account screen scaling factor to size correctly
- Can pick up HelpUrl and TooltipText instructions from code fragments and code procedures in the library. The HelpUrl let's you link to online documentation of the code and open using click on the Tooltip or F1.
- Tooltips in menu on click go to corresponding Tutorial or Help page.
- Better support for MS Project VBA Connect and Disconnect events fixes error
- Re-introduced fast declaration of variables and properties.
- Support for integrating HTML Help in your application - partly VBA code, also refers to Help Generator help authoring tools, also created by us.
- Better cursor positioning when opening a new module, taking Option Explicit eo. into account.
- Improved support for VBA Date functions
- Fixed bug in inserted code in one of the msgbox dialog variants.
- Excel » Range » Set » Select Range now returns External address, e.g. "[Book1]Sheet1!G5:H6" instead of only "G5:H6"
- Moved boolean functions in Range and Worksheet (e.g. RangeNameExists) in the VBA Code library to their \Is subfolder
- Improved behaviour for inserting procedures which have the same type used both as class or argument, and as return type.
- Better support and improved documentation for Excel Special Cells
- Improved menu of Excel » Range
- The Range Selection dialog now returns address without $ sign, which has no use in VBA
- Improved support for selecting and looping over cells collection
- Added to User Interaction:
- MS Access: SetWarnings
- Excel: Application.DisplayAlerts
- Excel protection:
- Improved menu for Set Workbook
- Allow functions to be inserted as arguments for Subs, e.g. with Debug.Print
- Space added between existing and new code when inserted on a non-empty line.
- Fix: variable for return arguments for objects no longer get Set code on the variable declaration code
- Refined support for opening a workbook, closing a workbook and Saving a workbook
- Improved support for writing Automation code e.g. programming Excel from Access (add reference to the required Office library first).
- Many Tooltips and Help F1 improvements:
- Tooltips appearing on Excel classes
- Properties and methods for the major Access and Excel classes now have F1 support to their MSDN Office help pages.
- Fixed Insert Variable not working from the menu
- Fixed Quick button on toolbar not working
- Fixed Search in Chrome not working
- Many new MS Access VBA fragments obtained from major on-line resources have been included.
- Hyperlink in tooltip on fragment now also can be activated using F1 or mouse click.
- Connection String Builder starts the standard MS Data Link Properties dialog:
- ADO » Connection » Connection String Builder
- ACE/DAO » Database » Connection » Connection String Builder
- Fixed bug with Publisher unloading Code VBA.
- Code VBA now also works with PowerPoint.
- Access VBA changes
- Name now also incudes fields of queries
- Quick can also be used after selecting Name of child of Table or Query - Field, or Form or Report - Control
- Access » SQL » Comma separated field string for selected table
- DAO/ACE » Table » Field Values Set inserts block of Field setting expressions
- DAO/ACE » Table » Field Values Get inserts block of Field getting expressions
- Getting names of Access objects now also available under the Access » Form » Name, etcetera.
- FindLast method added to class generated from table
- Fixed problem with Select Range in Office 2013 and 2016
- improved fragment Array » Sort
- Added fragment Class » Set = Nothing
- Added fragment Access » Control » ListBox » MultiSelect » Select All and Clear All
- Added fragment Access Form and Report Open: Keep1Open
- Fixed problem with stability for UserForm Builder if no fields were inserted
- Excel VBA and Access VBA menu now automatically adapt to only show classes, properties and methods supported in the version you use. This is especially usful for users with Office versions earlier than 2010.
- Fixed bug with menu timer
- Improvements to the menu, such as clearer captions and additional icons
- More use of lists of access objects for inserting code in Access VBA
- When selecting a procedure in the Code Explorer it now appears at the top of the code pane making it easier to find.
- During debug mode refresh of Code Explorer is stopped temporarily because it has adverse effect on debugging.
- Solved problem in case user chooses preference not to show default values.
- Fixed bug with menu items disabled in Access
- Improvements for Access VBA
- Integrated use of names when inserting statements for opening and closing forms and reports and tables and queries
- Integrated use of names when making Recordsets in DAO
- Property Builder now lets check if Get or Set/Let should be excluded.
- Worked on issues with enablement of Declare
Improvements for Excel VBA
- Integrated use of names when inserting Set statement for worksheets and named ranges
- Better support for the Name object Add method
- Fixed bug in Code Explorer which caused error.
- Fixed bug in Quick menu
- Remove less used buttons from toolbar: Error and Name
Added to Code VBA menu: Declare >
- New Constant Builder dialog, can also be started from the Code VBA toolbar
- Lost during recent changes, but now returned, the Type Builder.
- Fragments for inserting Enums.
- Improved behaviour of the Quick menu. Returns to the last menu selected option, however without directly executing it.
- Fixed problem with stability for UserForm Builder
- Quick Menu has been extended to choose between your latest (max 4) Code VBA actions, instead of just the latest. Now also accessible from the toolbar
New Comment menu added to the menu and Toolbar containing
- Procedure Header fragment - also used in the Procedure Builder. This replaces the old 'Add procedure comments block' in the Declare group - removed there.
- Code Change fragment: easily add a comment marking where in the code you made changes
- Comment lines of code feature (Removed from the Tools section)
- Declaring Procedures, Properties and Variables via the menu now uses builders
- On the builders, double-click on the type both selects the type and does the [Insert] button action.
- On the toolbar buttons that use applications and libraries currently not referenced are hidden, e.g. the Access button if you currently are in Excel.
- More appropriate message when trying to insert code (e.g. If statement) from toolbar with cursor outside procedure
- Declare and Set custom class get nice prefix instead of 'cls'
- Bug fix on Tidy in Code Explorer
- Improved insertion of Error Handels from the Code Explorer
- Toolbar items removed that don't refer to active of referenced application
- Bug fix for Quick button when previous action was on toolbar
- Class Builder no longer prefixes created with cls
- Code VBA toolbar extended with If, Select Case, Do While and For loop insertion code
- Fixed bug toolbar submenus polluted by other code areas
- Fixed bug fragments added from toolbar added to wrong location
- An alternative is given to the Code Builder pane in terms of separate Builder dialogs for those who don't want the pane permanently available because of the spcae it takes up.
- New Procedure Builder Dialog
- New Property Builder Dialog
- New Variable Builder Dialog
- Code VBA toolbar extended with above new Builder Dialogs.
- Code VBA toolbar extended with New Current Variable Insertion
- New feature for Code Explorer: 'Add Error Handler' right-click on procedure
Code Builder improvements
- Better placement of return statement of function
- Better placement of cursor in created procedure - inside body
- If in combo then Enter is no longer interpreted as Insert
- Code VBA toolbar extended with major code access points. This is more convenient for users who prefer to use the mouse for accessing the menu - one click less.
- menu: Error » Trapping » ... for faster switching between error trapping settings - instead of using the VBE Options dialog.
- Code Explorer: Rename module in Access now allows underscore _ as first character.
- Fixed 'Delete module' not working
- Rename method added which optionally replaces all references and calls to the module or procedure in the project
- Implemented right-click to open context menu using Shift-F10 (same as in Windows explorer)
- Previous user preferences now are no longer overwritten when installing new version
- Code Explorer extended with Rename feature for modules and procedures which optionally propagates to all places in the code where the name is used.
- Fixed problem with trial not including Excel VBA and Access VBA.
- Code Builders extended with If/Select, For, Loop (While/Until)
- Bug fix: Name » File / Folder Select not working
- Bug fix: Error when using UserForm Builder
- New Code Builders dockable window for creating sub and function procedures, properties, variables and constants faster.
- Added ctr-shift-E shortcut to access Code Explorer using the keyboard.
- Fix error when dragging a control from the Toolbox on a userform crossing the Code Explorer
- Renamed Code Explorer 'Indent' to 'Tidy Indentation'
- Procedure Builder: in case of object type use set for return value
- Extra fragments for delete file
- Extra fragments for looping files or subfolders in a given folder
- Fix on recent license checking changes
- New tool: Procedure Builder - fastest way to create new procedure with supporting code for error handling, header hourglass and return statement. Implemented as a dockable window similar to Code Explorer.
- Comments blocks like procedure header block can now automatically be aligned left.
- Bug fix: insertion of functions in vb modules in library not working
- Bug: Object reference not set to an instance of an object - in tvCodeExplorer_DragOver
- Bug fix: 'Set Range' submenu in Excel was lost since release 8mar15, included again.
- Bug fix: Specified argument was out of the range of valid values.
- First use of the software now first asks permission before activating trial on the server.
- New preference: Look for new version. This allows to overrule the default behaviour of looking online for a new version
- Code Explorer fix: error occurred when user removed an End Sub statement
- Code Indenter tool added to Code Explorer. When applied from a module or procedure all code is correctly indented making it much more readable.
- Fixed Class Builder bug properties nit getting inserted
Code VBA menu memory issues fixed:
- When add-in disconnected all Images disposed and ExistingImages cleaned.
- Number of GDI objects not increased each time when the menu opened.
Fixed issues with load and unload of the add-in:
- When add-in loaded after unloading CodeExplorer not shown.
- If "Load on Startup" off and add-in loaded from add-in manager then I got message "Microsoft Excel is trying to recover your information...this might take several minutes" when close Excel.
- Added: 'Simple' - customization of the Excel VBA menu to only show the classes you actualy use.
- Code VBA menu speed increase.
- Code Explorer, new: Add and remove Line numbers to procedure
- Code VBA menu fix: allow access to Add line numbers when lines to number are selected
- Fixed bug with handling code containing line numbers
- Insert procedures into Immediate Window
- Now also works with with PowerPoint
- Replaced simple insertion of class by Class Builder: menu » Class » Class Builder
- Custom Data Type Builder: : menu » Declare » Variable »Type Builder
- Fixed bug Code Explorer » Insert on (Declarations)
- F12 (optional arguments for last inserted procedure) now also works after Code Explorer » Insert
- Move procedures to other modules using drag and drop.
- Fix: Code Explorer no longer flickers in Windows 7 / Office 2010
- Added User Interaction » SelectionBox component to use similar to VBA InputBox and MessageBox
- Added code fragments for array from cpearson.com
- Fix: procedure arguments also inserted when from external file
- Fixed: insertion of Current Project procedures ignored procedure arguments
- New menu option 'Insert': insertion of Current Project procedures
- New menu option 'Go to' makes selected procedure the current - alternative to double-click
- Fix: menu option 'Import' not always opening dialog
- Menu tree opens semi-expanded instead of closed.
- Added SelectionForm allows value to be selected using a standard dialog, similar to InputBox. menu: User Interaction » SelectionForm » ...
- Code Explorer now support commenting procedures
- New tool for MS Access: SQL VBA Builder turns SQL into a VBA string.
- Added support for Optional when inserting precedure arguments: Declare » Variable » Optional » ...
- UserForm Builder now allows adding fieldnames from selected cells.
- Fix: If » Is » ... now again lists the built-in functions IsEmpty, etcetera.
- Fix: Code Explorer bug in Excel
- Added UserForm Builder to make custom dialogs easily including input validation. menu: User Interaction » UserForm » Builder...
- MSForms classes methods and properties now available from menu: User Interaction » UserForm.
- Fix: Name » Table » ... insertion fails
- VBA » User and network » Is64Bit
Excel VBA fragments added:
- Range » Is » Blankk
- Worksheet » Is » Blank
- Fix: Workbook menu crash due to removal of mailer class (only of use in Mac)
- Fix: Property variable should be Private
- Moved Range submenu in Range » Set to a more prominent position to reflect it's importance.
- Optional Arguments can now be easily inserted using Name » Optional Arguments, or F12. Note that this only works for the procedure you just inserted, or which currently selected in the menu.
- Refined support for the Workbook.Open ExcelFile and TextFile menus.
- Code fragments added for Worksheet.Add different use cases.
- Code modules can now be inserted in the \Documents\VBA Code\SharedHidden\ folder and then referenced from any fragment. This avoids procedure calls (such as API) that are needed in different code fragment calls to result in duplicated modules.
- Now also supports VBA in MS Project, AutoCAD and Visio. Fixed support for PowerPoint.
- Tooltips now can be activated for the current menu item using F1. If a tooltip appears which has a hyperlink in it pressing F1 again opens the related web page.
- In preferences you can set if tooltips should appear when the mouse hovers over a menu item. Some people consider the tooltip as visually getting in the way.
- The main methods of all classes, Add Delete, Open, and so on, are now promoted one level up in the menu, to make them more visible.
Latest Code VBA changes
- Declare now supports fast insertion of scope: public, private and static.
- Add methods of collections automatically hook to the class containing the collection. Therefore there is no longer need for the intermediate step of declaring the collection - which makes for smaller an clearer code.
Better support for assigning ranges:
- Additional methods added for Cells, Columns and Rows
- Same with For Each loops
- Select ranges and have correct syntax directly.
- Menu for Set and For Each puts most likely parent class on top based on the route followed in the menu - no longer purely based on alphabetic order
- Problem fixed with menu getting stuck after moving in the menu beyond the most right menu item - cursor landed outside of the coding pane.
- Improved code insertion for quite a few methods like Activate and Delete by ignoring/excluding the meaningless return value.
- Fragment addded: VBA » Read File » Read file line by line
- Excel Add events for major classes now are linked to their Parent class collection, giving you more useful code in a single action. For example Worksheet.Add
- Single Items inadvertently missing under Set in last release have returned and now require one menu click less to use.
- Excel and Access classes now also show descriptions when you hover above them.
- Major methods such as Add, Activate, Delete, Select now are placed above Methods group to give them a separate -higher- status and better visibility.
Important new release with many improvements for Excel:
- Reorganisation of the top level menu for Excel VBA with fewer entries based on actual frequency of use.
- The 'Set' menu for all Excel classes now also includes methods and properties.
- Classes lower in the menu hiarchie now no longer have a reduced representation.
- Software is now smaller and faster
- Changed menu Workbook to better show methods related to its life cycle (New/Open .. Save/Close)
- Added under Excel » Worksheet » Set: ActiveSheet.
- Added under Excel » Slicer » Set: ActiveSlicer
- Improved accessibility of WorksheetFunctions
- Added under Name: Select Range. This inserts the address of the selected area in correct A1 syntax.
- More detailed handling for creating new workbook using Workbooks.Add different usages.
- Special handling of Workbooks collection so that no wbks variable is added makes for shorter code.
- Added menu section User Interaction » Application.InputBox for Excel.
- Fixed LBound and UBound inserted code where explicit arguments are not allowed.
- Modules containing public procedures can now be included in the \My Documents\VBA Code\ subfolders and will then be made available from the code vba menu. As an example: VBA Code\Excel\Worksheet now contains modSheetProcedures.bas with functions SheetExists and IsValidSheetName.
- Added GoTo / GoSub language
- Fixed insertion of Names » Labels, quotes no longer added
- Insertion in With block removes varname in front of inserted dot. method or property if same type as With.
- Fixed bug of 'Manage Fragments' opening wrong fragments folder.
- Added fragment 'Give Input and Validate' under menu: VBA » User Interaction » InputBox'.
- Improved fragment 'Give Whole Number'
- Improved algorithm to find best candidate class variable when inserting With statement.
- Insertion now no longer prefixes common methods like ActiveWorksheet by the Application class.
- Grouped methods and properties from Range class with common first noun e.g. Formula and Clear to improve visibility by having fewer menu entries.
- Moved collection methods such as Add and OpenText for classes Workbook and Worksheet to the top to make them more visible.
- Removed Add2 from Worksheets and Sheets collection.
- Moved PivotField and PivotItem to top level of Excel menu.
- Extra versions of InputBox with Title and Defaults arguments optionally included.
- Intelligent insertion of names improved with support for quotes and brackets for use with explicit arguments
- Removed special hidden character in inserted line inadvertently added which prevented compiling
- User Interaction » Excel built-in Dialogs added
- Icons added for MS Access
- Fixed Name » Select File/Folder interpreting hover as click.
- Icons added for MS Access
- Many Office compliant icons added for easier finding if relevant procedure
Tooltips for VBA procedures, filled with:
- Code that will be inserted.
- Descriptive title.
- Hyperlinks to MSDN help pages.
- Fragment process fixed for If » Is » Object Is Nothing
- Solved menu problem in Access 2000
Fixes on the menu:
- Fields on Names » Tables no longer appearing
- In some cases fragments no longer appear
Fragments added to Access:
- Form: Move to the record selected in List or Combo box
- Form: Disable PgUp and PgDown keys using Form_KeyDown event
- Form » 'Is' group added, with NewRecord and Dirty.
- Control » OptionGroup: Specify behavior for each value in option group
- Control » ComboBox: Add item to combo box using OnNotinList event
- Faster recognition of menu items by adding icons
- Minor fix on the menu
- Extended Fragments added to VBA » Automation » Shell: the main windows (Calc, notepad,...) and Office applications (Word, Excel, Outlook)
- Extended Fragments added to VBA » String » Is: 'Is RTF' and 'Is HTML' which check the start of the string.
- Improved performance of Code VBA menu. Now also supports Office 2010 64 bit.
- Improved organization of Form and Report properties by redistributing them over Format, Data and Other same way as in the Access property user interface.
- Fragments added to Access » Access Objects » (determine if) Exists with Name: Query, Table, Linked Table, Form, Report.
Fragments added to VBA » String:
- Get Alphabetic Chars
- Get Numeric Chars
- Remove Illegal Chars
- Added under VBA: User and Network information using the Environ function.
- Added under VBA » Folder: Special Folders. TEMP, PUBLIC, APPDATA, and more using the Environ function.
Fragments added to VBA » Manage File:
- Get File Extension
- Get FilePath
- Make FileName with New Extension
- Fragments added to VBA » String: Wrap Text function for things like labels.
- Access Controls properties now divided over Data, Format and Other similar as in the design mode for improves finding what you need.
- Minor change to trial activation
Class from Table:
- autonumber ID is made available after creation of new record
- empty strings are returned as Null - if needed
- Code Explorer added as replacement for Project Explorer for fast navigation between procedures.
- basErrorHandle now supports 64bit Office.
- Access fragment added: Form » SubForm » Switch View Normal - Datasheet
Improvements to Access VBA menu:
- Menu: Access » SQL has more query string templates.
- CriteriaBuilder support dor different types added wherever there is need to specify a 'where' condition.
- Increased reliability of activation, also with trial versions.
Screen menu removed. The code it contained was already available under Menu: Access » Forms » Set as
Screen.ActiveForm. Same for ActiveControl (under .. Access » Control) and similar others.
- Code fragments with only a single line now also can be inserted in a non-empty line.
- Fixed bug in Add Property
- Menu: VBA » Time and Menu: VBA » Registry added.
- Increased speed of menu
- Support for Public/Private in Property builder
Refined support for MS Access:
- Menu: Access » Data gives refined access to the domain functions DLookup, DAvg etcetera.
- Menu: Access » Variable ... now also can be used after scope modifiers such as 'Public'. Same for Const.