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 (http://www.vbaexpress.com/forum/member.php?4898-Jan-Karel-Pieterse)
http://www.vbaexpress.com/forum/images/statusicon/user-online.png
MS Excel MVP
VBAX Expert (http://www.vbaexpress.com/forum/faq.php?faq=new_faq_item#faq_faq_user_ranks)
http://www.vbaexpress.com/forum/images/reputation/reputation_pos.png

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 (http://www.jkp-ads.com)
Founding member of:
www.excelexperts.nl (http://www.excelexperts.nl)

[Paul_Hossler]

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


http://social.msdn.microsoft.com/Forums/en-US/c45d37f1-a5f1-4cf4-938d-69d294d8e447/limitations-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 (http://www.vbaexpress.com/forum/showthread.php?47629-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) (http://www.vbaexpress.com/forum/showthread.php?48603-Conditional-Compilation-Quick-Question-%28Well-at-least-hopefully%29)
Stated By: GTO (http://www.vbaexpress.com/forum/member.php?17945-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.