Code Snippets
Most Microsoft products that use VBA, have a feature where you can store your VBA code in text files and have the code automatically pasted into your code module, with a very simple and easy to use built-in system called:- “File Insert Option”
I describe the system on my Nifty Tips Webpage. Here is a link to the actual tip about inserting code directly into your code module:-
Insert VBA Code Snippets
https://www.niftyaccess.com/nifty-tips/#VBACode
Over the years I have used this system and built up a comprehensive list of useful VBA code Snippets.
I am planning transfer copies of the code from my PC to this webpage, thereby making it available for anyone to use.
Some of the code is not mine, it’s stuff I found useful. I try and acknowledge the originator of the code where I can.
For some of it; I was never able to identify the originator, the original author. If you happen to be the original author, then let me know and I will give you the necessary attribution.
I use the File Insert Option to insert directly into my modules I find it very convenient. I keep a zipped copy of my folders and files both on my PC and in my Google drive folder. However I thought I could do with exposing some of the most useful code I’ve used for years on the web so here it is!.
Convert USA Dates to UK Dates
This particular piece of code is very useful for people that use the British date format.
The code originated from “Alvechurch Data Ltd”…
I’ve never been able to track down the original developer, but I have always left the attribution in the code, wherever I use it.
I would appreciate it if you you would honor originator the same attribution when you use the code yourself…
fSQLDate()
Private Function fSQLDate(ByVal varLocalDate As Variant) As String
'/// CREDITS --- Got this from::: Alvechurch Data Ltd
'Phone +44 121 445 1300 or email sales@alvechurchdata co.uk
'http://www.alvechurchdata.co.uk/accsql.htm
fSQLDate = "#" & Format(varLocalDate, "mm/dd/yyyy") & "#"
End Function 'fSQLDate
fSQLDateAndTime()
Private Function fSQLDateAndTime(ByVal varLocalDate As Variant) As String
'/// CREDITS --- Got this from::: Alvechurch Data Ltd
'Phone +44 121 445 1300 or email sales@alvechurchdata co.uk
'http://www.alvechurchdata.co.uk/accsql.htm
'ATH made a modification so the code now handles Time
'Format("13:13:13", "hh:nn a/p")
'See:- http://www.informit.com/articles/article.aspx?p=1143872&seqNum=7
fSQLDateAndTime = "#" & Format(varLocalDate, "mm/dd/yyyy hh:nn:ss") & "#"
' fSQLDateAndTime = Format(varLocalDate, "mm/dd/yyyy hh:nn:ss")
End Function 'fSQLDateAndTime
Links HERE:-
VBA Access - How to make Access Wait
This particular piece of code uses the Timer function, which if you read up on it, is not recommended. If you do use it, make sure you know what it’s doing because it can cause unanticipated and unintended problems..
Public Function fPause(NumberOfSeconds As Variant)
'From:-
'ghudson
' VBA Access - How to make Access wait.
'https://www.access-programmers.co.uk/forums/showthread.php?t=167470
On Error GoTo Err_fPause
Dim PauseTime As Variant, start As Variant
PauseTime = NumberOfSeconds
start = Timer
Do While Timer < start + PauseTime
DoEvents
Loop
Exit_fPause:
Exit Function
Err_fPause:
MsgBox Err.Number & " - " & Err.Description, vbCritical, "fPause()"
Resume Exit_fPause
End Function 'fPause
Links HERE:-
Basic Function Template fXXXXX()
I use this basic function template all the time. It has everything to get me up and running straight away just as soon as I’ve downloaded this template into my code window.
First off it declares a constant. Normally this constant would be in in a constants module. If you’ve got this constant in your basConstants module, then you don’t need these lines of code, just delete them. Dim conAppName As String conAppName = “(Replace this Local Variable with a Global One) ” The next few lines collect some basic information about your function which is used and returned in the error message if you’re something goes wrong with your function and it happens to create an error. The error message will include the name of the module where the code is this could be in a form or a module and the name of the function or subroutine that caused the error as stored in these variables. Dim strSubName As String Dim strModuleName As String strSubName = “fXXXXX” strModuleName = “Form – ” & Me.Name ‘strModuleName = “Module – basModuleName”.
Private Function fXXXXX() As String
Dim conAppName As String
conAppName = "(Replace this Local Variable with a Global One) "
Dim strSubName As String
Dim strModuleName As String
strSubName = "fXXXXX"
strModuleName = "Form - " & Me.Name
'strModuleName = "Module - basModuleName"
On Error GoTo Error_Handler
'Place Code HERE !!!
'Place Code HERE !!!
'Place Code HERE !!!
Exit_ErrorHandler:
'adoCon.Close
'Set adoCon = Nothing
'Set adoCmd = Nothing
Exit Function
Error_Handler: 'Version - 1a
Dim strErrFrom As String
Dim strErrInfo As String
strErrFrom = "Error From:-" & vbCrLf & strModuleName & vbCrLf & "Subroutine >>>>>>> " & strSubName
strErrInfo = "" & vbCrLf & "Error Number >>>>> " & Err.Number & vbCrLf & "Error Descscription:-" & vbCrLf & Err.Description
Select Case Err.Number
Case 0.123 'When Required, Replace Place Holder (0.123) with an Error Number
MsgBox "Error produced by Place Holder please check your code!" & vbCrLf & vbCrLf & strErrFrom & strErrInfo, , conAppName
Case Else
MsgBox "Case Else Error" & vbCrLf & vbCrLf & strErrFrom & strErrInfo, , conAppName
End Select
Resume Exit_ErrorHandler
End Function 'fXXXXX
