Skip to content

Instantly share code, notes, and snippets.

danwagnerco / clear_cell_contents_on_click.vb
Created Apr 14, 2017
Placed in the Worksheet you'd like this mode to work in, you can clear cell contents by simply clicking the cell, which allows for instant editing
View clear_cell_contents_on_click.vb
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Target.FormulaR1C1 = ""
End Sub
View dmikester1_mod.vb
'don't do this anymore:
DataBlock.SpecialCells(xlCellTypeVisible).Copy Destination:=Target.Cells(1, 1)
'instead try this:
Dim rngDestination As Range 'this should be way up at the top with all the other declarations
' ... all the other code
Set rngDestination = Target.Cells(1, 1)
rngDestination.PasteSpecial xlPasteType:=xlPasteValuesAndNumberFormats
danwagnerco / CombineManyWorkbooksIntoOneWorksheet.vb
Last active May 16, 2019
This VBA macro combines data from many workbooks in a single folder into a single worksheet
View CombineManyWorkbooksIntoOneWorksheet.vb
Option Explicit
Public Sub CombineManyWorkbooksIntoOneWorksheet()
Dim strDirContainingFiles As String, strFile As String, _
strFilePath As String
Dim wbkDst As Workbook, wbkSrc As Workbook
Dim wksDst As Worksheet, wksSrc As Worksheet
Dim lngIdx As Long, lngSrcLastRow As Long, _
lngSrcLastCol As Long, lngDstLastRow As Long, _
lngDstLastCol As Long, lngDstFirstFileRow As Long
danwagnerco / HighlightCorrespondingCells.vb
Created Oct 15, 2016
This macro will highlight cells in column G based on the selection in column A
View HighlightCorrespondingCells.vb
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim wksLookups As Worksheet
Dim lngFirstRow As Long, lngLastRow As Long
'Get the Worksheet so we can confidently identify
'the Range that we'll be highlighting (if need be)
Set wksLookups = Target.Parent
danwagnerco / exampleEarlyBindingWithScriptingDictionary.vb
Created Oct 2, 2016
an example of early-binding in VBA (with the Scripting.Dictionary object, a personal favorite)
View exampleEarlyBindingWithScriptingDictionary.vb
Dim dicCoolDictionary As Scripting.Dictionary
Set dicCoolDictionary = New Scripting.Dictionary
' then start assembling the dictionary
View exampleEarlyBinding.vb
Dim variable as SomeObjectType
Set variable = New SomeObjectType
danwagnerco / exampleLateBinding.vb
Created Oct 2, 2016
an example of late-binding in VBA
View exampleLateBinding.vb
Dim variable as Object
Set variable = CreateObject("SomeObjectType")
danwagnerco / TestCreateGUID.vb
Last active Oct 2, 2016
This tiny subroutine walks through all of the options available in the CreateGUID function
View TestCreateGUID.vb
Option Explicit
Public Sub TestCreateGUID()
'Dim t As Scriptlet.TypeLib <~ error, user type not defined
'Dim t As Scriptlet.IGenScriptletTLib <~ at least compiles
'Debug.Print t.Name <~ error, object or with block variable not set
' i.e. doesn't understand t as a variable
'Debug.Print t.GUID <~ same
'Debug.Print t.AnyOtherListedMethodOnThisObject
danwagnerco / CreateGUID.vb
Last active Apr 18, 2019
This function returns a GUID (globally unique identifier) primarily for use in VBA scripts
View CreateGUID.vb
Option Explicit
Public Function CreateGUID(Optional IncludeHyphens As Boolean = True, _
Optional IncludeBraces As Boolean = False) _
As String
Dim obj As Object
Dim strGUID As String
'Late-bind obj as a TypeLib -- a rare time when late-binding
'is actually a must!
danwagnerco / combine_sheets_with_different_headers.vb
Created Jul 8, 2016
This script combines many sheets into a single sheet even when the columns on each sheet are different (or are in different order)
View combine_sheets_with_different_headers.vb
Option Explicit
Public Sub CombineSheetsWithDifferentHeaders()
Dim wksDst As Worksheet, wksSrc As Worksheet
Dim lngIdx As Long, lngLastSrcColNum As Long, _
lngFinalHeadersCounter As Long, lngFinalHeadersSize As Long, _
lngLastSrcRowNum As Long, lngLastDstRowNum As Long
Dim strColHeader As String
Dim varColHeader As Variant
Dim rngDst As Range, rngSrc As Range