Greetings to all.

In this post I will tell you what VBA is and how to work with it in Microsoft Excel 2007/2010 (for older versions, only the interface changes - the code is likely to be the same) to automate a different routine.


')


VBA (Visual Basic for Applications) is a simplified version of Visual Basic built into many Microsoft Office line products. It allows you to write programs directly in the file of a specific document. You do not need to install various IDEs - everything, including the debugger, is already in Excel.

Even with Visual Studio Tools for Office, you can write macros in C # and also embed them. Thank you, FireStorm .

I’ll say right away - it is also possible to write in other languages ​​(C ++ / Delphi / PHP), but you need to learn how to read, change and write office files - you will not be able to embed them in documents. And Microsoft interfaces work through COM . So that you understand all the horror, here is Hello World using COM.

Therefore, alas, we will learn Visual Basic.

A little bit of preparation and task setting

So let's go. Open Excel.

First, let's add the Developer panel to the Ribbon. It contains buttons, text fields and other elements for constructing forms.



Appeared tab.



Now let's think about what example we will study VBA. Recently, I needed to make a beautifully priced list that looked like a table. We go to Google, dial the "price list" and download any that is designed like this (do not consider advertising, please):



That is, it is required that there be at least two groups according to which goods can be combined (in our case it will be Type and Manufacturer - in that order). In order for the algorithm proposed by me to work correctly, sort the goods so that the products from one group stand in a row (first by Type , then by Manufacturer ).

The result we want to achieve looks like this:



Of course, if you look at the price only on a computer, you can add filters and it will be much more convenient to look for the right product. However, we want to learn how to code and the task is quite suitable, is not it?

Code

First you need to create a button, when clicked, our program will be called. The buttons are located in the “Developer” panel and appear by the “Insert” button. You need a button form component. Clicked, put on any place in the sheet. Further, if the macro assignment window does not appear, you must right-click and select the “Assign Macro” item. Let's call it FormatPrice. It is important that before the macro name there is nothing - otherwise it will be created in a separate module, and not in the namespace of the book. In this case, you will not be available quick access to the selected sheet. Press the "New" button.



And here we are in the VB development environment. You can also call it from the context menu using the Source / View code command.



Here is a window with a procedure stub. You can deploy it. The code should look something like this:

Sub FormatPrice()

End Sub

Let's write Hello World:

Sub FormatPrice()
MsgBox "Hello World!"
End Sub

And run either by clicking on the button (after removing the selection from it), or by pressing F5 directly from the editor.

Here, perhaps, should be distracted by a small educational program about the syntax of VB. Who knows - you can safely skip this section to the end. The main difference between Visual Basic and Pascal / C / Java is that the commands are not separated ; , and a line break or a colon ( :) , if you really want to write several commands in one line. To understand the basic rules of syntax, I will give an abstract code.

Syntax examples

' .
' VBA
Sub foo(a As String , b As String )
' Exit Sub ' " "
MsgBox a + ";" + b
End Sub

' . Integer
Function LengthSqr(x As Integer , y As Integer ) As Integer
' Exit Function
LengthSqr = x * x + y * y
End Function

Sub FormatPrice()
Dim s1 As String , s2 As String
s1 = "str1"
s2 = "str2"
If s1 <> s2 Then
foo "123" , "456" '
End If

Dim res As sTRING ' VB . ,
Dim i As Integer
'
For i = 1 To 10
res = res + CStr(i) ' String
If i = 5 Then Exit For
Next i

Dim x As Double
x = Val( "1.234" ) '
x = x + 10
MsgBox x

On Error Resume Next ' -
x = 5 / 0
MsgBox x

On Error GoTo Err ' Err
x = 5 / 0
MsgBox "OK!"
GoTo ne

Err:
MsgBox "Err!"

ne:
On Error GoTo 0 '

' ,
Do While True
Exit Do

Loop 'While True
Do 'Until False
Exit Do
Loop Until False
' , , .
' Val Integer
Select Case LengthSqr(Len( "abc" ), Val( "4" ))
Case 24
MsgBox "0"
Case 25
MsgBox "1"
Case 26
MsgBox "2"
End Select

' .
' ReDim (Preserve) - . google
Dim arr(1 to 10, 5 to 6) As Integer
arr(1, 6) = 8

Dim coll As New Collection
Dim coll2 As Collection
coll.Add "item" , "key"
Set coll2 = coll ' Set
MsgBox coll2( "key" )
Set coll2 = New Collection
MsgBox coll2.Count
End Sub

Rake-1. When copying code from IDE (in English Excel), the entire text is converted to 1252 Latin-1 . Therefore, if you want to save Russian comments, you must save the crocodiles as Latin-1 , and then open them in 1251.

Rake-2. Because VB allows you to use undeclared variables, I always at the beginning of the code (before all procedures) put the line Option Explicit . This directive forbids the interpreter to set variables on its own.

Rake-3. Global variables can only be declared before the first function / procedure. Local - anywhere in the procedure / function.

Some more additional features that may come in handy: InPos , Mid , Trim , LBound , UBound . Also, answers to all questions regarding the operation of functions / their parameters can be obtained in MSDN.

I hope that this will be enough for you not to be afraid of the code and write some homework on computer science yourself. In the course of the post, I will unobtrusively acquaint you with new designs.

Code a lot and under Excel

In this part we will begin to code something that can work with our sheets in Excel. To begin with, we will create a separate sheet with the name result (the sheet with the data is called data ). Now, probably, you need to clear this sheet of what is on it. We also “select” a data sheet in order not to write a long call to an array with sheets each time.

Sub FormatPrice()
Sheets( "result" ).Cells.Clear
Sheets( "data" ).Activate
End Sub

Work with cell ranges

All work in Excel VBA is done with a range of cells. They are created by the Range function and return an object of type Range . He has everything you need to work with data and / or design. By the way, the Cells sheet property is also Range .

Examples of working with Range

Sheets( "result" ).Activate
Dim r As Range
Set r = Range( "A1" )
r.Value = "123"
Set r = Range( "A3,A5" )
r.Font.Color = vbRed
r.Value = "456"
Set r = Range( "A6:A7" )
r.Value = "=A1+A3"

Now let's understand the algorithm of our code. So, each line of the data sheet, starting from the second, has some data that we are not interested in ( ID , name and price ) and there are two nested groups to which it belongs ( type and manufacturer ). Moreover, these lines are sorted. While we forget about the passes before the start of the new group, it will be easier. I suggest this algorithm:

1. Considered groups from the next line.
2. Run across all groups in order of priority (larger ones first)
   
   1. If the current group does not match, we call the procedure AddGroup (i, name) , where i is the group number (from the current number to the maximum), name is its name. Several calls are needed to create not only our header, but smaller ones as well.
3. After drawing all the necessary headers, we make another line and fill it with data.

To simplify the work, I recommend defining the following abbreviations:

Function GetCol(Col As Integer ) As String
GetCol = Chr(Asc( "A" ) + Col)
End Function

Function GetCellS(Sheet As String , Col As Integer , Row As Integer ) As Range
Set GetCellS = Sheets(Sheet).Range(GetCol(Col) + CStr(Row))
End Function

Function GetCell(Col As Integer , Row As Integer ) As Range
Set GetCell = Range(GetCol(Col) + CStr(Row))
End Function

Next, we define the global variable “current line”: Dim CurRow As Integer . At the beginning of the procedure, it should be made equal to one. We also need the variable “current line in data ”, an array with the group names of the current previous lines. Then you can write the cycle “until the first cell in the row is non-empty”.

Global variables

Option Explicit '
Dim CurRow As Integer
Const GroupsCount As Integer = 2
Const DataCount As Integer = 3

FormatPrice

Sub FormatPrice()
Dim I As Integer ' data
CurRow = 1
Dim Groups(1 To GroupsCount) As String
Dim PrGroups(1 To GroupsCount) As String

Sheets( "data" ).Activate
I = 2
Do While True
If GetCell(0, I).Value = "" Then Exit Do
' ...
I = I + 1
Loop
End Sub

Now you need to fill in the Groups array:

In place of the dot

Dim I2 As Integer
For I2 = 1 To GroupsCount
Groups(I2) = GetCell(I2, I)
Next I2
' ...
For I2 = 1 To GroupsCount ' VB
PrGroups(I2) = Groups(I2)
Next I2
I = I + 1

And create headlines:

On the dot in the previous piece

For I2 = 1 To GroupsCount
If Groups(I2) <> PrGroups(I2) Then
Dim I3 As Integer
For I3 = I2 To GroupsCount
AddHeader I3, Groups(I3)
Next I3
Exit For
End If
Next I2

Let's not forget about the AddHeader procedure:

Before Format Price

Sub AddHeader(Ty As Integer , Name As String )
GetCellS( "result" , 1, CurRow).Value = Name
CurRow = CurRow + 1
End Sub

Now you need to transfer any information to result.

For I2 = 0 To DataCount - 1
GetCellS( "result" , I2, CurRow).Value = GetCell(I2, I)
Next I2

Fit the columns to the width and select the result sheet to display the result.

After the loop at the end of FormatPrice

Sheets( "Result" ).Activate
Columns.AutoFit

Everything. You can admire the first version.



Ugly, but similar. Let's deal with formatting. First, change the procedure AddHeader :

Sub AddHeader(Ty As Integer , Name As String )
Sheets( "result" ).Range( "A" + CStr(CurRow) + ":C" + CStr(CurRow)).Merge
'
' With
With GetCellS( "result" , 0, CurRow)
.Value = Name
.Font.Italic = True
.Font.Name = "Cambria"
Select Case Ty
Case 1 '
.Font.Bold = True
.Font.Size = 16
Case 2 '
.Font.Size = 12
End Select
.HorizontalAlignment = xlCenter
End With
CurRow = CurRow + 1
End Sub

Already better:



It remains only to make the border. Here we need to work with all the merged cells, otherwise there will be only one border:



Therefore, we slightly change the code with the addition of border style:

Sub AddHeader(Ty As Integer , Name As String )
With Sheets( "result" ).Range( "A" + CStr(CurRow) + ":C" + CStr(CurRow))
.Merge
.Value = Name
.Font.Italic = True
.Font.Name = "Cambria"
.HorizontalAlignment = xlCenter

Select Case Ty
Case 1 '
.Font.Bold = True
.Font.Size = 16
.Borders(xlTop).Weight = xlThick
Case 2 '
.Font.Size = 12
.Borders(xlTop).Weight = xlMedium
End Select
.Borders(xlBottom).Weight = xlMedium ' : xlThick, xlMedium, xlThin, xlHairline
End With
CurRow = CurRow + 1
End Sub



It remains only to achieve gaps before the start of the new group. It is easy:

At the beginning of FormatPrice

Dim I As Integer ' data
CurRow = 0 '
Dim Groups(1 To GroupsCount) As String

In the header placement loop

If Groups(I2) <> PrGroups(I2) Then
CurRow = CurRow + 1
Dim I3 As Integer



Exactly what they wanted.

I hope that this article has helped you get a little comfortable with Excel programming in VBA. Homework - add the headings " ID, Title, Price " to the result. Hint: CurRow = 0 CurRow = 1 .

The file can be downloaded here (min.us) or here (Dropbox). Do not forget to allow the execution of macros. If someone tells human file hosting, I'll upload it there.

Thanks for attention.
I will be glad to constructive criticism in the comments.
UPD: Perezalil example on Dropbox and min.us.
UPD2: In fact, when calling a procedure with a single parameter, brackets can be supplied. Or use the Call Foo construction (“bar”, 1, 2, 3) - here brackets are needed all the time.