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

Indian Provident fund and UK taxation

Withdrawal from Indian Provident fund by a UK tax resident will be taxable in the UK.

Summary

In India, payment from provident fund is exempt income under section 10 of Indian Income Tax Act. Source: Taxation of Non- Residents – Revised (2021)

But in case an individual who is a UK resident withdraws it, he will need to pay UK income tax on it. Source: HMRC Employment Income Manual EIM74510

Main point to remember is value of Provident fund till 6 April 2017 will not be taxable, see EIM74510

Story

Many of us who left our lands and travelled to the UK in search of fame and fortune may be forced to withdraw funds from their Indian provident fund, it is better to know that it is taxed at the highest marginal rate in the UK.

Moral

Blessings of the Tax Gods – every day you will learn something new!

Bonus

As per India UK DTAA Article 19 (2)- Any pension paid by Government of India to any individual shall be taxable only in India.

Government of India means Union, State or Municipal – see Article 3 (1) (k)

Source: UK India DTAA Agreement – Synthesized text

Registering a company as employer with non-UK resident director

Overseas directors and PAYE;
Email address of HMRC Employer helpline

20th January 2019

Today I was registering a company as an employer. Directors are from overseas.

Registration form required their unique tax identifier from their country of origin. When I entered it HMRC system rejected it as it will only take data in format of UK National Insurance number.

This seemed strange as other countries have other formats of tax identifier numbers and do not follow UK national insurance format of AB123456C.

I have put a dummy number AB123456C. There is no place to provide any extra information to HMRC on this PAYE registration form.

Fingers crossed, lets see what happens. I will post the results.

PS – I thank accountingweb website, I got this idea of putting a dummy NI number from there.

30th January 2019

I received an email from HMRC informing NI number provided is not valid. Replied to their informing them about the system issue , detailed above. Lets see…

1st February 2019

We succeeded ! 

Email received from HMRC employer team that application has been processed and reference numbers in post.

Thank you to HMRC once again for accepting common sense.

Email of Employer helpline : employer.helpline@notifications.hmrc.gov.uk

2nd March 2022

Process changed, now we need to write to HMRC if director does not have a UK National Insurance number.

Direct telephone number: VAT Registration helpdesk

Recently we were registering a client for VAT. Usually you get the VAT registration number in couple of days.

This time the application did not go through, but HMRC sent a clear and detailed email with the documents required.

We sent those documents but did not hear back for over a week.

I searched for a telephone number and got to the following webpage:

https://www.gov.uk/government/organisations/hm-revenue-customs/contact/vat-enquiries

Now they even have a web-chat option and the web chat was very helpful and they gave me a direct number for VAT registration helpline as 0300 523 245.

Good luck !

24th February 2022

Telephone number to check VAT registration application status is 0300 322 7870