A few tips on working with VBA in Excel

Good day!

Some time ago I was asked to “help with Excel”, and then the work turned up like this, so over the past couple of months I have learned a lot of useful things, and I want to share with you in a recent article.

It is assumed that you know the basics of Visual Basic. I will not tell you how to create forms or modules, here are just code examples.
')

Visual basic

Options

Firstly, in VB arrays can begin with index 1, which is strange for many, therefore you can write in the beginning of modules:

OPTION BASE 0 

It is also recommended to register:

 OPTION EXPLICIT 

In this case, the interpreter will require all variables to be declared in advance. Variables must be declared because:
- VB will remember their message and will not correct the entire code for the last entered variant;
- sometimes errors occur with the transfer of variables byRef, if they are not declared (that is, you must either declare a variable, or assign byVal in a function / procedure before it).

Another important operator is ON ERROR. Here are the options:

 ON ERROR RESUME NEXT '     ON ERROR GOTO label: ' ,   ,   label: ON EROR GOTO 0 '   . 

Language features

Although VB is quite simple, it is helpful to read the documentation for its syntax. For example, I was surprised to find out that you can prescribe complex usolviya in SELECT'ah (analog switch):

 SELECT CASE parametr CASE 1: ' do something' CASE 3 to 5: ' do something else' CASE 6, 8, 9: ' do something funny' CASE ELSE: ' do do do' END SELECT 

Macro acceleration

Often, macros require a long execution time, which can be significantly reduced. Call Prepare and Ended at the beginning and at the end of each resource-intensive function.

 Public Sub Prepare() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Application.EnableEvents = False ActiveSheet.DisplayPageBreaks = False Application.DisplayStatusBar = False Application.DisplayAlerts = False End Sub Public Sub Ended() Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic Application.EnableEvents = True ActiveSheet.DisplayPageBreaks = True Application.DisplayStatusBar = True Application.DisplayAlerts = True End Sub 

In order:
1. Disable redrawing of objects on the screen so that nothing blinks.
2. Turn off the calculation. Attention, if the macro is interrupted in the middle of work, the calculation will remain in manual mode!
3. Do not handle events.
4. Displaying page borders also helps for some reason.
5. The status bar displays various data, which slows down the work, turn off.
6. This is if needed. Turns off Excel messages. For example, we are doing a Workbook.Close, Excel wants to ask whether to save the changes. When you turn off this option, all answers will be given automatically (the changes will not be saved).

It is important to understand that VBA performs all actions in the same way as the user. Therefore, in order to set the page parameters, it opens and closes the parameters window every time. I set the parameters for 10 sheets, it really is not fast. Therefore, we do this:

 If Sheets("01").PageSetup.PrintArea <> "A1:D5" Then Sheets("01").PageSetup.PrintArea = "A1:D5" If Sheets("02").PageSetup.PrintArea <> "A1:E8" Then Sheets("02").PageSetup.PrintArea = "A1:A1:E8" 

Further, it is often necessary to look at various ranges of cells and do something with them. It is important not to use for loops with brute force indexes, they are slow. You can use the built-in functions of Excel, but the most convenient option is this:

 Dim tCell As Variant For Each tCell In Sheets("01").Range("P16:Q19").SpecialCells(xlCellTypeFormulas) If tCell.Interior.ColorIndex = xlColorIndexNone Then tCell.Locked = True tCell.Interior.Color = RGB(220, 230, 241) End If Next tCell 

This code looks at the specified range, selects “special cells” in it, in this case, all that have formulas (that is, start with the equal sign). For each cell it looks, if it is not painted over, then it must be protected (see below) and painted. This code works very fast.

For any variables to which you are going to assign a book, the sheet, range (cell) must first be declared as Variant.

Naturally, if you need the same type of values ​​in the cells, you need to use autocomplete, just the same as “stretching” the cells by the user.

 Sheets("01").Range("P15").AutoFill Sheets("01").Range("P15:Q15"), xlFillValues 

The second range should include the first, and the second optional parameter indicates the type of autocomplete.

Download books and events

When you open the book every time the procedure is triggered.

 Private Sub workbook_open() Dim sh as Variant Prepare '   For Each sh In ActiveWorkbook.Worksheets With sh.PageSetup If .Orientation <> xlLandscape Then .Orientation = xlLandscape If .LeftMargin <> Application.CentimetersToPoints(0.5) Then .LeftMargin = Application.CentimetersToPoints(0.5) If .RightMargin <> Application.CentimetersToPoints(0.5) Then .RightMargin = Application.CentimetersToPoints(0.5) If .TopMargin <> Application.CentimetersToPoints(1.5) Then .TopMargin = Application.CentimetersToPoints(1.5) If .BottomMargin <> Application.CentimetersToPoints(0.5) Then .BottomMargin = Application.CentimetersToPoints(0.5) If .HeaderMargin <> Application.CentimetersToPoints(0) Then .HeaderMargin = Application.CentimetersToPoints(0) If .FooterMargin <> Application.CentimetersToPoints(0) Then .FooterMargin = Application.CentimetersToPoints(0) End With Next sh Ended End Sub 

In this case, the print settings (margins, orientation) are reset to default. It is possible to perform another initialization. It is important that if macros are disabled, then nothing will be executed. If in Eksele a macro warning warning panel came out at the top and the user clicked "Enable", then at that very moment the Workbook_open () procedure will be executed.

The list of available events can be viewed at the top of the VB editor. For example, I did a check on the Change event, where the cell in which there were changes lies, and if this is the desired range, then an entry was made to the log with the old and new values.

Protection

Firstly, I note right away that MS Office does not execute macros on computers where it did not find the antivirus if the book is encrypted. Collided on computers where the antivirus was, but apparently Windows XP did not know about it.

Another antivirus can strangely interfere with work, cause errors that are not entirely explainable. I showed it to IT people, they said ok, they did something, I don’t know.

So, we need to protect the book so that the input is allowed only in the necessary cells (formulas and headers can not be changed). First, you need to make the corresponding cells "unprotected." To do this, do one of:
- select the range, the format of the cells, uncheck "Block cell";
- display the “Block cell” button in quick access and press it, it is very convenient to look at it in order to understand whether the cell is protected or not;
- and it will come in handy to check the third option - to write a macro, which removes the protection from the desired cells itself.

Next you need to protect the sheet. On the Review tab there is such a button. The window asks for a password and set exceptions (what the user can do). Unfortunately, the list of exceptions is small. The most annoying thing is that you cannot allow collapsing / expanding groups of columns / rows. Therefore, we act like this; we prescribe the following to download the book:

 myPassword = "123" For Each sh In ActiveWorkbook.Worksheets sh.Unprotect (myPassword) sh.EnableOutlining = True sh.Protect Password:=myPassword, _ UserInterfaceOnly:=True, AllowSorting:=True, _ AllowFiltering:=True, AllowFormattingRows:=True, _ AllowFormattingColumns:=True, DrawingObjects:=False Next sh 

The underscore continues the logical line on the next physical line. So here we are:
1. Removed protection.
2. Enabled grouping.
3. Put the protection, with:
- protection only from the user, macros continue to have full access (!), It is extremely important;
- allowed sorting, filtering and formatting rows / columns (height / width);
- DrawingObject in this case removes protection from the notes to the cells, maybe something else.

Here we are faced with a couple of surprises. First, not all macros will even work this way. Known bug, you can not do anything. You cannot insert a string, for example. We have to remove and immediately put the protection. If the “attacker” at this moment presses ctrl + break, the protection will fly off.

Secondly, let's say in no way can you delete rows (AllowDeletingRows) that have protected cells, at least one. Read more here .

The solution (crutch) is to add a button or keyboard shortcut for deletion. At the same time, you can check that the user does not remove what is not needed. Add to Workbook_open:

 Application.OnKey "+{DELETE}", ".DelSelectedRow" 

Now the procedure will be called when you press shift + delete.

 Sub DelSelectedRow() If Selection.Rows.Count = 1 Then If Selection.Parent.Name = "01" And Selection.Cells.Count >= 1000 Then If Selection.row > 13 And Selection.row < 50 Then Selection.Delete End If End If End If End Sub 

I know the code is ugly, sorry. Here I tried to check that a row is highlighted, that is, there are 1 rows, and no less than a thousand cells. To remove the wrong, you will have to select a thousand cells starting from a different column. Next, check the sheet name and line numbers. Instead of 50, the last line was calculated (because their number changes, if we delete and add them).

Conclusion

VBA is a very buggy thing that allows you to move mountains in MS Office. Many businesses use Excel models for years, and if they are done well, then everything works.

He himself is suitable for studying VBA, firstly there is a good reference. For example, to find out all the options that can be resolved in the Protect method, press F1, Protect, input. And voila.

Secondly, you can do the required actions manually by writing a macro, and then viewing its code. The code will be terrible (for example, if you change the page settings, the macro will record the values ​​of all parameters and fields, not just the one you changed), but there are answers. Although, for example, .AutoFit, which is recorded when the cell height changes in content (double click on the border to the left), does not actually work.

I suggest experts to share their experiences, give advice in the comments. Thank you for your attention, good development to you.