Call a Subroutine from a different Module in VBA
Prefix the call with Module2 (ex. Module2.IDLE). I’m assuming since you asked this that you have IDLE defined multiple times in the project, otherwise this shouldn’t be necessary.
vba
Is there a need to set Objects to Nothing
VB uses a so-called “reference counting” garbage collector. Basically, the moment a variable goes out of scope, the reference counter on the referenced object is decremented. When you assign the object reference to another variable, the reference counter is incremented. When the counter reaches zero, the object is ready for garbage collection. The object resources … Read more
Retrieve calendar items (Outlook API, WebDAV) displaying strange behaviour
Possible cause: Sort after setting IncludeRecurrences. Here is my code of a PowerShell module that retrieves Outlook items between two dates. And a little applet to check for changes and send an email including the agenda updates, which comes handy when you don’t have mobile access to the Exchange. Path: Documents\WindowsPowerShell\Modules\Outlook\expcal.ps1 Function Get-OutlookCalendar { <# … Read more
Excel column number from column name
I think you want this? Column Name to Column Number Sub Sample() ColName = “C” Debug.Print Range(ColName & 1).Column End Sub Edit: Also including the reverse of what you want Column Number to Column Name Sub Sample() ColNo = 3 Debug.Print Split(Cells(, ColNo).Address, “$”)(1) End Sub FOLLOW UP Like if i have salary field at … Read more
What are some good patterns for VBA error handling? [closed]
Error Handling in VBA On Error Goto ErrorHandlerLabel Resume (Next | ErrorHandlerLabel) On Error Goto 0 (disables current error handler) Err object The Err object’s properties are normally reset to a zero or a zero-length string in the error handling routine, but it can also be done explicitly with Err.Clear. Errors in the error handling … Read more
Why Use Integer Instead of Long?
Integer variables are stored as 16-bit (2-byte) numbers Office VBA Reference Long (long integer) variables are stored as signed 32-bit (4-byte) numbers Office VBA Reference So, the benefit is in reduced memory space. An Integer takes up half the memory that a Long does. Now, we are talking about 2 bytes, so it’s not going … Read more
How do I test if optional arguments are supplied or not?
Use IsMissing: If IsMissing(arg) Then MsgBox “Parameter arg not passed” End If However, if I remember correctly, this doesn’t work when giving a default for the argument, and in any case it makes using the default argument rather redundant.
Getting Error 3340 Query ‘ ‘ is corrupt while executing queries DoCmd.RunSQL
Summary This is a known bug caused by the Office updates released on November 12, 2019. The bug affects all versions of Access currently supported by Microsoft (from Access 2010 to 365). This bug has been fixed. If you use a C2R (Click-to-Run) version of Office, use “Update now”: Access 2010 C2R: Fixed in Build … Read more
Test if string begins with a string?
There are several ways to do this: InStr You can use the InStr build-in function to test if a String contains a substring. InStr will either return the index of the first match, or 0. So you can test if a String begins with a substring by doing the following: If InStr(1, “Hello World”, “Hello … Read more
Pass arguments to Constructor in VBA
Here’s a little trick I’m using lately and brings good results. I would like to share with those who have to fight often with VBA. 1.- Implement a public initiation subroutine in each of your custom classes. I call it InitiateProperties throughout all my classes. This method has to accept the arguments you would like … Read more