Black list scam!

Crypto

Creating macros and custom functions in VBA


The content of the article

Almost all office workers are faced with tasks that are associated with processing large amounts of data or routine things when you have to copy and paste a large stream of information by hand. In such minutes it seems that you are wasting hours, and the “machine” would probably have coped with the routine in a second. We are taking a decisive step towards automating the routine.

By the way, we previously wrote about how much time can be saved by putting in the effort and recording a macro.

How to create a macro in VBA / Excel?

The easiest and fastest way is through recording a macro. Go to the “Developer” and open a window for generating queries in VBA.


Didn’t find the “Developer”?

File -> Options -> Customize Ribbon

In the Main Tabs panel, check the Developer box and click OK.


Let’s start recording the macro. For convenience, you can give the macro a name.

We enter the command. In our case, in cell C6, we enter the formula

= C2 + C3 + C4 + C5

We stop recording the macro and check its work.

Change the numbers in cells C2 – C5. Go to “Macros” and click “Run”.

The result is calculated correctly.

What did the macro record? How does it look in code?

Go to “Developer” → “Macros” → select our macro → click “Change”

We see the code.

You can go to this field easier: just use the Alt + F11 combination, which takes us to the VBA code editor.

You can insert absolutely any macro into this field. If you are not an expert in VBA, it will be enough to spy on ready-made macros on the Internet and copy them into your document.

Examples of

1.Using macros, you can copy data from one file to another

Sub CopyFiletoAnotherWorkbook ()
‘Copy the data
Sheets (“Example 1”) .Range (“A1: C5”) .Copy
‘Create a new workbook
Workbooks.Add
‘Paste the data
ActiveSheet.Paste
‘Turn off application alerts
Application.DisplayAlerts = False
‘Save the newly file. Change the name of the directory.
ActiveWorkbook.SaveAs Filename: =“C: EXAMPLE 2.xlsx”
‘Turn application alerts back on
Application.DisplayAlerts = True
End Sub

2. Using macros, you can delete takes. Earlier we wrote about how to do this without using VBA.

Sub DelDups_OneList ()
Dim iListCount As Integer
Dim iCtr As Integer

‘Turn off screen updating to speed up macro.
Application.ScreenUpdating = False

‘Get count of records to search through.
iListCount = Sheets (“Sheet1”) .Range (“A1: A100”) .Rows.Count
Sheets (“Sheet1”) .Range (“A1”).Select
‘Loop until end of records.
Do until ActiveCell = “”
‘Loop through records.
For iCtr = 1 To iListCount
‘Don’t compare against yourself.
‘To specify a different column, change 1 to the column number.

If ActiveCell.Row <> Sheets (“Sheet1”) .Cells (iCtr, 1) .Row Then
‘Do comparison of next record.
If ActiveCell.Value = Sheets (“Sheet1”) .Cells (iCtr, 1) .Value Then
‘If match is true then delete row.
Sheets (“Sheet1”) .Cells (iCtr, 1) .Delete xlShiftUp
‘Increment counter to account for deleted row.
iCtr = iCtr + 1
End If
End If
Next
iCtr
‘Go to next record.
ActiveCell.Offset (1, 0).Select
Loop
Application.ScreenUpdating = True
MsgBox “Done!”
End Sub

The macro finds duplicate elements in the A1: A100 range and removes them. An important feature of the macro is the absence of empty cells in the array. If the list contains blank cells, you must sort the data in ascending order so that any blank cells appear at the end of the list.

3.Using macros, you can compare two lists and remove duplicate information

The following example macro compares one (key) list with another and removes duplicate items in the second list that are in the main list. The first list is on Sheet1 in the range A1: A10. The second list is located on Sheet2 in the range A1: A100. To use the macro, select any sheet and then run the macro.

Sub DelDups_TwoLists ()
Dim iListCount As Integer
Dim iCtr As Integer

‘Turn off screen updating to speed up macro.
Application.ScreenUpdating = False

‘Get count of records to search through (list that will be deleted).
iListCount = Sheets (“Sheet2”) .Range (“A1: A100”) .Rows.Count

‘Loop through the “master” list.
For Each x In Sheets (“Sheet1”) .Range (“A1: A10”)
‘Loop through all records in the second list.
For iCtr = 1 To iListCount
‘Do comparison of next record.
‘To specify a different column, change 1 to the column number.
If x.Value = Sheets (“Sheet2”) .Cells (iCtr, 1) .Value Then
‘If match is true then delete row.
Sheets (“Sheet2”) .Cells (iCtr, 1) .Delete xlShiftUp
‘Increment counter to account for deleted row.
iCtr = iCtr + 1
End If
Next iCtr
Next
Application.ScreenUpdating = True
MsgBox “Done!”
End Sub

The possibilities of VBA are quite extensive. We have given only a few examples, but you can study the topic in more detail on your own by enrolling in the Excel Academy course, watching training videos and our articles.

Alexander Valtsev, CEO of SF Education, spoke about one tricky macro for a giant table in the video.

Author: Fomkina Irina, expert at SF Education

Moris Akline
About author

Speculative operations in the foreign exchange and stock market. Work with futures and indices on CME. Work on FORTS and RTS. Development of portfolios and investment strategies for clients. Knowledge of technical (price charts of various types, indicators and oscillators, trends, channels, support / resistance levels) and fundamental analysis, take note of news. I work according to my trading strategy in compliance with the rules of capital management and risk management. Development of trust management strategies. Technique of order execution, placing and changing protective stop-losses, hedging.
Related posts
Crypto

What is Sustainable Investment? Part 1

Crypto

How I decided to overcome laziness and got into finance: an SF Education graduate on career and change

Crypto

Income and expenditure budget: what is it and how to draw it up?

Crypto

Goals and objectives of financial analysis

Sign up for our Newsletter