Tips And Tricks

[SamT]

Every once in a while we come across a posted solution that is subtle, sophisticated and generally undocumented that deserves more than a Reputation Point. It is also a solution can be incorporated into many VBA Apps.

This thread is a place for Mods to put those jewels of wisdom for everybody to learn from.

[SamT]

Jan Karel Pieterse

MS Excel MVP
VBAX Expert


I prefer not to have code like that in the Thisworkbook module.

Move all code to subs in a a normal module and call those subs from the events in thisworkbook.

Especially code in workbook_open *sometimes* causes trouble because Excel isn't quite done loading yet when that code is executed.
To avoid that problem, I use the OnTime method to start any code after workbook_Open like so:

Private Sub Workbook_Open()      
Application.OnTime Now, "ContinueOpen"  
End Sub

Then in the normal sub I have:

Sub ContinueOpen()      
Call2Sub1      
Call2Sub2  
End Sub

Regards,

Jan Karel Pieterse
Excel MVP
JKP Application Development Services
Founding member of:
www.excelexperts.nl

[Paul_Hossler]

Not sure that the 6K TRANSPOSE() limit is still in later versions


http://social.msdn.microsoft.com/For...ions-on-arrays

Earlier versions of Excel had some limits on on the number of cells you could assign from a VBA array (passing the results from the VBA array to the worksheet). How have these changed in later versions of Excel?

I know that there seems to be a limit in VBA when using worksheetfunction.transpose of 2^16 elements in the array. This is an increase from the previous 5,000 or so

Paul

Copied from Thread: Fastest way to temporarily store results for further analysis

[Jan Karel Pieterse]

I'd add a small routine in a normal module, like so:

Sub SaveAnyway()
    Application.EnableEvents = False
    ThisWorkbook.Save
    Application.EnableEvents = True
End Sub

[SamT]

From Thread:Conditional Compilation Quick Question (Well, at least hopefully)
Stated By: GTO

I am muddling through writing some code that needs to work in at least VB7 with both 32|64 bit, and preferably VB6 and VB7 (and still covering both 32|64). So... I just noticed that if I write a function two ways (#If VB7 #Else...), and I am in VB7 (Excel 2010), then I cannot put a breakpoint in the #Else section.

#If VBA7 Then 
    Public Declare PtrSafe Function GetWindowText Lib "user32" _ 
    Alias "GetWindowTextA" (ByVal hwnd As LongPtr, _ 
    ByVal lpString As String, _ 
    ByVal cch As Long _ 
    ) As Long 
#Else 
    Public Declare Function GetWindowText Lib "user32" _ 
    Alias "GetWindowTextA" (ByVal hwnd As Long, _ 
    ByVal lpString As String, _ 
    ByVal cch As Long _ 
    ) As Long 
#End If 

#If VBA7 Then 
    Private Function EnumChildProc(ByVal lpHwnd As LongPtr, ByVal lpParam As LongPtr) As Long 
        Dim lRet As Long 
'Etc     
End Function
#Else 
    Function EnumChildProc(ByVal lhWnd As Long, ByVal lParam As Long) As Long 
        Dim lRet          As Long 
        Dim sWinClassBuff As String * 255 
        'Etc 
End Function

Solution:

Module Level Constant:

#Const Design32 = False|True

Conditional Code:

#If Design32 Then 
    Function EnumChildProc(ByVal lhWnd As Long, ByVal lParam As Long) As Long 
    #ElseIf VBA7 Then 
        Private Function EnumChildProc(ByVal lpHwnd As LongPtr, ByVal lpParam As LongPtr) As Long 
        #Else 
            Function EnumChildProc(ByVal lhWnd As Long, ByVal lParam As Long) As Long 
            #End If 
        Dim lRet          As Long 
        Dim sWinClassBuff As String * 255 
        Dim sWinTitleBuff As String * 255 
'Etc         
    End Function

[SamT]

Date of Beginning of Month = Date of Start of month

date of Last thirty 30 days

First of year date

This date last month

Option Explicit

Function BOM(dDate As Date) As Date
'Thanks to xld @ http://www.vbaexpress.com/forum/showthread.php?49493
'Beginning Of Month
    BOM = dDate - Day(dDate) + 1

'How it Works:
'   if dDate = 24 Mar, 2014
'   Day(dDate) = 24: dDate - 24 = 0 Mar,2014
'   Add 1 = 1 Mar, 2014
End Function

Function BOY(dDate As Date) As Date
'Beginning Of Year
  Dim sYear As String
  
  sYear = CStr(Year(dDate))
  BOY = CDate("1/1/" & sYear) 'Already international
End Function

Function Last30Days(dDate As String) As Date
'The Date 30 days ago. March 1st = Jan 29; Jan 31 = Jan 1st
  Last30Days = dDate - 30
End Function

Function PreviousMonth(dDate As Date) As Date
'Returns proper date of previous month. Mar 1st = Feb 1st; Mar31 = Feb 28 (29 on leap years)
  PreviousMonth = DateAdd("m", -1, dDate)
End Function

Sub Test()
'Range("D1") holds date to use
Dim TotalThisMonthToDate
Dim StartRow As Long
Dim EndRow As Long
Const XXX As Long = 99 'Random column number

With Sheets("YTD")
  StartRow = .Range("A:A").Find(BOM(.Range("D1")), LookIn:=xlValues).Row
  EndRow = .Range("A:A").Find(.Range("D1").Value, LookIn:=xlValues).Row
  TotalThisMonthToDate = WorksheetFunction.Sum(Range(Cells(StartRow, XXX), Cells(EndRow, XXX)))
End With
End Sub

[Bob Phillips]

Function BOM(dDate As Date) As Date
    BOM = dDate - Day(dDate) + 1
End Function

[SamT]

Sue to lack of activity, I am going to unstick this thread and let it wander off into the wild.