Some useful VBAs

Below are some useful VBAs I have recently used in by spreadsheets. I am publishing them as it may help others struggling with VBA code as I often do 🙂

 
Sub HideRows()
    ‘This command hides all the rows from 2 to 100.
    ‘I have not hidden row 1 as it has my headings.
Rows(“2:100”).EntireRow.Hidden = True
‘This command unhides the active row
Rows(ActiveCell.Row).EntireRow.Hidden = False
End Sub

 

Sub UnhideRows()
    ‘This command Unhides all the rows
Rows(“2:100”).EntireRow.Hidden = False
End Sub

 

How to assign short keys?

By assigning keyboard shortcut keys you can make is easier to run these macros.
Developer > Macros >Options> Shortcut Key

 

Below is a sample macro I have created for a daily task.

Sub BankFlash()

   ‘Define the variable for sheet name
Dim sname As Single

    ‘Assigning the variable with the value of Active Sheet’s number
sname = ActiveSheet.Name

    ‘ Copying Active Sheet and then telling it to paste just after the active sheet
ActiveSheet.Copy After:=ActiveSheet

    ‘ Now the Active sheet is the new sheet and we are increasing the sheet name by 4 if its  a Monday.   

If Weekday(Now) = 2 Then
ActiveSheet.Name = sname + 4
Else

‘ On days other than Monday, we increase the sheet name by 1   
ActiveSheet.Name = sname + 1
End If

‘Active sheet is the new sheet now.
‘Clears contents from specific cells in the new sheet
Range(“B7:d12”).ClearContents
Range(“B17:d24”).ClearContents

‘Clears contents from specific cells in the new sheet if today is Monday
If Weekday(Now) = 2 Then
Range(“H19:H23”).ClearContents
Else
End If

‘Links the opening balance to the previous sheet             

Range(“B4”) = “=” & Str(sname) & “!B29”
Range(“C4”) = “=” & Str(sname) & “!C29”
Range(“D4”) = “=” & Str(sname) & “!D29”

‘Last Step , this leaves the control on the box from where i wish to start inputting data
Range(“B31”).Select

End Sub

 

 

Custom functions

The below macro creates a FUNCTION for excel to add numbers by colours. I still donot know how it works fully but I find it useful sometimes.

Function SumByColor(CellColor As Range, SumRange As Range)
Application.Volatile
Dim ICol As Integer
Dim TCell As Range
ICol = CellColor.Interior.ColorIndex
For Each TCell In SumRange
If ICol = TCell.Interior.ColorIndex Then
SumByColor = SumByColor + TCell.Value
End If
Next TCell
End Function

 

Lastly, how to Unprotect an Excel Spreadsheet if you have lost your password.