Month: March 2022

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

Bonus

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

2. Sometimes while using filters dates donot group together. Simply multiply all dates by 1 , using paste special function.

National Minimum Wage is increasing from 1st April 2022

Every year all employers should check payroll records to ensure they donot fall foul of NMW regualtions

National Minimum Wage (NMW) is increasing from £8.91 to £9.50 from 1st April 2022.

More information on NMW as individuals younger than 23 years get lower pay.

Please review your payroll records to ensure that you are not paying any staff less than NMW.

Method to check

  1. Fixed Pay – Annual Pay divided by number of hours worked in a year.

Example an employee works for £24,000 per annum for 35 hours per week.

Annual hours worked 35 x 52 = 1820 hours.

Hourly rate = 24,000 divided by 1820 = £13.19. This is more than NMW

  1. Variable pay – this is simpler just ensure basic hourly rate is £9.50 or more from 1st April 2022.

Gov.uk calculator to check if you are paying NMW