Home ] Up ] MS Access Page ] SQL Server ] Tech Links ]

Help with the Visual Basic Editor

Scroll Wheel / Mouse wheel not working
VBA Code help and archive
VBA Array character limit
VBE no longer displays Downdown List
Weird Code in the immediate window

VBE no longer displays Downdown List
If the Visual Basic Editor (VBE) is no longer showing / displaying the drop down list of all the 
related properties and methods then the following.
Firstly Make sure you have got the tickbox 'Auto List Members' checked in the Options dialog box in the VBE.
If that doesn't fix it try pressing 'Ctrl + j'.

Mouse scroll wheel in the Visual Basic Editor not working?  You need "Freewheel"!

What is FreeWheel? FreeWheel provides mouse wheel support for applications that do not directly support the IntelliPoint mouse wheel (which as you have discovered includes the VBE (visual basic editor).

FreeWheel caters for the possibility of conflicts with applications that directly support the wheel by allowing you to specify a list of applications. FreeWheel can either include or exclude applications that appear in this list.

FreeWheel is free, so there are no irritating nag screens, no timeouts, and there is no guilt associated with failing to register (that is because you don't ever have to register the product.  It can also be installed without needing admin rights. Ideal for installing on corporate computer that are tightly locked down. See: http://www.geocities.com/SiliconValley/2060/freewheel.html

Microsoft also provide a solution, but it is more complex and you need admin rights, not always suitable: http://support.microsoft.com/kb/837910

Lots of useful VBA stuff here (not just Excel VBA):

I was about to debug some code, so I open the vbe and the immediate window 
and these the following appeared in the 'Immediate' window: Why does this happen?
[auto_open] <
[SetupFunctionIDs] <
[SetupFunctionIDs] >
[PickPlatform] <
[PickPlatform] >
[VerifyOpen] <
[VerifyOpen] > 1
[RegisterFunctionIDs] <
[RegisterFunctionIDs] >
[auto_open] >
This is a bug left over by a careless MS programmer. This happens when the analysis toolpack addins are loaded. you can open the ATP add-in (password is Wildebeest!!) and
get rid of the offending Debug.Print statements that were not removed by MS developers if you care to.

VBA Array character limit

there is a 911 char limit - and the 1823 limit, see below.
For more general purposes including text, you have to process long text cells one by one.
You may receive a "Run-time error 1004" error message when you programmatically set a large array string to a range in Excel 2003
This issue may occur if one or more of the cells in an array (range of cells) contain a character string that is set to contain more than 911 characters.
To work around this issue, edit the script so that no cells in the array contain a character string that holds more than 911 characters.

Data may be truncated when you transfer array data to cells in an Excel worksheet
Note: In Microsoft Office Excel 2003 and in later versions of Excel, you may receive the following error message when you run the VBA macro in
the Visual Basic Editor: "Run-time error '1004': Application-defined or object-defined error".
If you run the VBA macro from your Excel worksheet, you may receive the following error message:
Microsoft Visual Basic: 400
CAUSE: This problem may occur when one of the following conditions is true:
 In Excel 2007, the VBA array is longer than 8,203 characters in length.
 In Excel 2003 and in earlier versions of Excel, the VBA array is longer than 1,823 characters in length.
To work around this problem, populate each cell in your worksheet one at a time from the array, instead of populating the whole range at one time.
For Description of the limitations for working with arrays in Excel see http://support.microsoft.com/kb/166342/

Also see: http://www.dailydoseofexcel.com/archives/2006/12/04/writing-to-a-range-using-vba/

Home ] Up ] MS Access Page ] SQL Server ] Tech Links ]