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
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…
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
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
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
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