Create Custom Commands for Excel - VBA

Submitted by techrev on Thu, 12/29/2016 - 09:49
excel custom commands

Create Custom Commands for Excel - VBA

You can create Custom Commands for Excel to perform some predetermined operations. These commands can be entered directly inside a cell to do the required actions (calculations)

Please note that, these commands are made on VBA, so please refer our previous articles for the basic idea on Macros and VBA

Example Command : ADD

Command to ADD to values from columns A and B and display in C, the command “ADD” will be typed in cells of Column C

Steps :

Create a new excel file and right click on Sheet1 to open VBA editor (right click and select view code)
Then save the file as *.xlsm (macro enabled workbook)

Paste these VBA codes in side the editor

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Rownumber As Long, result As Double
On Error GoTo errH
If Target.Count = 1 And Target(1).Column = 3 Then
If LCase(Target.Value) = ("add") Then
Application.EnableEvents = False
Rownumber = Target.Row
result = Cells(Rownumber, 2).Value + Cells(Rownumber, 1).Value
If result Then
Target.Value = result
Else: Target.Value = ""
End If
End If
End If

errH:
Application.EnableEvents = True
End Sub

Put some numbers in Column A and B for first 5 rows.
Then type “ADD” inside the cell C (of each same row)
You can see that the SUM of A and B will be displayed in C

 

Tags