Useful functions and scripts for everyday Excel&VBA.
Message with OK/Cancel buttons before running a VBA script
Sub TheFunction()
Dim response As Integer
' Display message box with OK/Cancel buttons
response = MsgBox("Now the function will run. Ok to start?", vbOKCancel)
' Check the response
If response = vbCancel Then Exit Sub
...MAIN FUNCTION CODE...
End Sub
Record the time of a function run
Sub TheFunction()
Dim startTime As Double
Dim finishTime As Double
' Save start time
startTime = Timer
...MAIN FUNCTION CODE...
' Save finish time
finishTime = Timer
' Display start time and finish time
MsgBox "Done!" & vbCrLf & "Started: " & Format(startTime / 86400, "hh:mm") & vbCrLf & _
"Finished: " & Format(finishTime / 86400, "hh:mm")
End Sub
Fill down formula from a cell
Sub TheFunction()
Dim ws As Worksheet
Dim lastRow As Long
Set ws = ThisWorkbook.Sheets("Sheet1")
' Find the last filled row in column A
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' Set formula in B1
ws.Range("B1").Formula = "=A1*100"
' Fill down the formula to the last row in column B
ws.Range("B1:B" & lastRow).FillDown
End Sub
Excel formula for current Sheet name
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
Get column letter in VBA
Function GetColumnLetter(i As Integer, j As Integer) As String
GetColumnLetter = Split(Cells(i, j).Address, "$")(1)
End Function