Shulou(Shulou.com)11/24 Report--

You must be very surprised to see this title, what, Excel can develop games? Yes, the strength of Excel depends on the user, strong when strong, weak when weak. But my article is not intended to show the tricks and tricks of using Excel, but mainly for those who are ready to learn programming but have no basic computer skills, or who are interested in Excel.

For those who are learning programming, especially those who enter the industry from other fields, interest is the biggest motivation. I have been engaged in computer programming for such a long time, I feel that programming is a very meaningful thing. However, I often hear some people, especially those at school, complain that programming is too boring to stick with. I think on the one hand, these people are in the wrong direction, on the other hand, they do not have any sense of achievement in the actual learning process, and the latter often occupies an important reason.

In my opinion, for beginners of programming, choosing the first language should have the following two characteristics:

1), as simple as possible, as little as possible associated with the underlying hardware (such as memory management, etc.), convenient debugging, simple IDE interface

2), the function is relatively powerful, can develop a variety of mini plug-in tools

At present, among the more commonly used programming languages in the industry, Python,office for VBA,Java is the one that can meet the above two characteristics at the same time. However, although Python is simple and powerful, it needs to configure the environment and install bloated IDE, which virtually increases the learning cost for beginners, not to mention Java, so the rest is VBA (Visual Basic for Application). VBA belongs to a subset of visual basic language, which not only inherits some vb functions, but also specially encapsulates the interface for some software, which is easy to use. Some people say that VBA grammar is too casual and not a good thing for beginners. If you learn C, it will be much easier to learn C++ in the future. I don't agree with this view, for the former, there are different opinions, but the latter is bullshit, because C++ is an extremely complex programming language, which not only inherits the tedious pointers of C, but also derives horror-level programming paradigms such as multiple inheritance, class templates, smart pointers and so on.

Why did you choose VBA as the language for beginners, because it not only meets the two characteristics mentioned above, but also has some other advantages, such as:

1) easy to use: you don't need to install development tools, let alone with environment or language pack, as long as you have office software in your computer.

2), a wide range of uses: almost all engineering software, office software support secondary development with VBA, for example, if financial personnel find that the formula of Excel has limitations, they can use VBA to develop their own controls; mechanical designers can develop some code blocks they need after learning VBA, which can greatly improve the drawing speed of their CAD. It's hard to imagine how scary the workload of Excel's heavy users, especially financial staff, would be if they didn't understand VBA.

3), the debugging is simple and convenient.

So, this time I also choose VBA as the language for writing Demo this time, in order to take care of more beginners, I will present the details of each step as much as possible, as each Excel version is different, my computer uses version 2010, so I use version 2010 to explain, other versions are the same, but the interface may be slightly different. I believe that as long as you make this game according to my method, you will not only realize the power of Excel, but also gradually experience the fun of programming. In view of the time constraint, there may be some negligence in the content. I hope you will put forward some corrections.

The following is the text:

First of all, take a look at the final general effect of the game:

First, let's think about the general structure of the Tetris game:

1), initialize the interface: create the map needed for the box.

2) randomly generate Tetris: Tetris has a total of 7 forms, each of which is composed of 4 boxes, and each box corresponds to a color. You can create an array to store the coordinates of each square, and then use another array to store the corresponding color of the square.

3), move the rotation box: divided into left, right, and down. After wiping, it is redrawn to produce the effect of movement and rotation.

4), no new squares are produced, they all fall at a certain speed, and once they encounter obstacles, they cannot fall, and new squares are generated.

5) keep scanning to see if any line is filled, and if it is true, the line will be deleted and fall above. The score of each row is 10 points.

First create an Excel file and name it as you like. After opening it, since office hides the development tool status bar by default, we need to call it up in the Excel option > Custom Ribbon and check it to confirm:

Subsequently, we found that the main interface has more options for development tools:

Then in the Sheet1 table, we adjust the width of the column of column Aqk to roughly the same height as the row, so that it is roughly called a square area:

We click the Visual Basic menu to open the coding interface, and we insert a code module first to write our own code:

Because there are seven shapes in the box, in order to make it easy for the program to draw, I use a three-dimensional array to store the coordinates of all shapes, each with a central coordinate (0P0). The coordinates of the other three boxes calculate the relative coordinates according to the central coordinates, such as a T-shaped square:

If the coordinate of the center is (0clockwise 0), the remaining three counterclockwise coordinates from right to left are (0jue 1), (- 1m 0), (0le le le 1), respectively. The reason why the vertical direction is taken as the X axis is because of the inherent attribute of Excel coordinates, for example, Cells (1J 2) represents the first row and the second column of cells. The object of each square has central coordinates, color, shape and other attributes, so we need to define several module variables, the code is as follows:

Option ExplicitDim MySheet As WorksheetDim iCenterRow As Integer 'square center row Dim iCenterCol As Integer' square center column Dim ColorArr ()'7 colors Dim ShapeArr ()'7 kinds of square Dim iColorIndex As Integer 'color index Dim MyBlock (4,2) As Integer' coordinate array of each box Will change with the movement of the square Dim bIsObjectEnd As Boolean 'whether this square falls to the lowest point Dim iScore As Integer' score considering the difference of each kind of square coordinates, so I use a three-dimensional array to store square coordinates. For convenience, I use the interface Array () function of VBA to assign a value to my ShapeArr (). At the same time, the player's score should be displayed on the main interface, so as an initialization function, we define an Init () sub-procedure with the following code:

'initialize By@yaxi_liuPrivate Sub Init () Set MySheet = Sheets ("Sheet1") ColorArr = Array (3,4,5,6,7,8,9) ShapeArr = Array (0,0), Array (0,1), Array (0,1), Array (0,2)), _ Array (Array (0,0), Array (0,1), Array (0,1), Array (- 1) ), _ Array (Array (0,0), Array (0,1), Array (0,1), Array (- 1,1)), _ Array (Array (0,0), Array (- 1,1), Array (- 1,0), Array (0,1)) _ Array (Array (0,0), Array (0,1), Array (- 1,0), Array (- 1,1)), _ Array (Array (0,0), Array (0,1), Array (- 1,0), Array (- 1,1)) _ Array (Array (0,0), Array (0,1), Array (0,-1), Array (- 1) 0)) With MySheet.Range ("B1:K20") .Interior.pattern = xlNone .Borders.LineStyle = xlNone .Borders (xlEdgeBottom). Weight = xlMedium .Borders (xlEdgeRight). Weight = xlMedium .Borders (xlEdgeLeft). Weight = xlMedium End With 'set length-width ratio MySheet.Columns ("avanza L"). ColumnWidth = 2 MySheet.Rows ("1:30"). RowHeight = 13.5 iScore = 0 MySheet.Range ("N1"). Value = "fraction" MySheet.Range ("O1"). Value = iScoreEnd Sub Our functions for initializing variables and functions are basically implemented. The next step is to write a function that generates a new box. In order to achieve modularization and low coupling of the program, we encapsulate this function into an independent function.

Since the drawing function DrawBlock () needs to draw according to the passed array of markers, and we need to know where the central coordinates of the box are and the corresponding colors, we need to pass four parameters, of which the array needs to be ByRef. The code is as follows:

'draw a square By@yaxi_liuPrivate Sub DrawBlock (ByVal center_row As Integer, ByVal center_col As Integer, ByRef block () As Integer, ByVal icolor As Integer) Dim Row As Integer, Col As Integer Dim i As Integer For i = 0 To 3 Row = center_row + block (I, 0) Col = center_col + block (I, 1) MySheet.Cells (Row Col). Interior.ColorIndex = icolor 'color index MySheet.Cells (Row, Col). Borders.LineStyle = xlContinuous' with a frame NextEnd Sub so far The drawing function is complete. To prevent Bug from appearing, we need to test it. We define another entry function, Start (), and define an array of temporary squares, calling DrawBlock () to test. Add a button to the main interface, assign it to the Start function, and drag it to the appropriate location:

The Start function code is as follows:

Sub Start () Call Init iCenterRow = 5 iCenterCol = 6 iColorIndex = 4 Dim i As Integer For i = 0 To 3 MyBlock (I, 0) = ShapeArr (iColorIndex) (I) (0) MyBlock (I, 1) = ShapeArr (iColorIndex) (I) (1) Next Call DrawBlock (iCenterRow, iCenterCol, MyBlock, ColorArr (iColorIndex)) End Sub let's run it and see how it works:

Well, the test results showed no problem at all.

Later, we need to randomly generate new squares at the top of the table, so we should encapsulate this function as an independent function again. In order to prevent the generation of pseudo-random numbers, we use Timer as the current seed to randomly generate an array between 0,6 and each corresponding shape array and color array. The code is as follows:

'randomly generate a new square function By@yaxi_liuPrivate Sub GetBlock () Randomize (Timer) Dim i As Integer iColorIndex = Int (7 * Rnd) iCenterRow = 2 iCenterCol = 6 For I = 0 To 3 MyBlock (I, 0) = ShapeArr (iColorIndex) (I) (0) MyBlock (I, 1) = ShapeArr (iColorIndex) (I) (1) Next Call DrawBlock (iCenterRow, iCenterCol MyBlock, ColorArr (iColorIndex)) End Sub now that the box is generated We have to allow the square to move left and right, divided into three directions. The method of moving is to erase the current square first, then calculate the new coordinates according to the specified direction of movement, and then redraw them according to the new coordinates, which leads to the phenomenon of movement. However, before we move, we need to determine whether we can move.

First of all, we need to write a function CanMoveRotate to determine whether it can be moved or rotated. This function is very simple, that is, it passes the moved or rotated coordinates to determine whether it is out of bounds or whether there are other colors in the current position. The code is as follows:

'can you move or rotate the function? By@yaxi_liuPrivate Function CanMoveRotate (ByVal center_row As Integer, ByVal center_col As Integer, ByRef block () As Integer) As Boolean 'the parameters of this function are all transformed coordinates' first judge whether to cross the boundary Dim Row As Integer, Col As Integer Dim i As Integer CanMoveRotate = True For i = 0 To 3 Row = center_row + block (I, 0) Col = center_col + block (I) 1) If Row > 20 Or Row

< 0 Or Col >

11 Or Col

< 2 Then '越界 CanMoveRotate = False End If If MySheet.Cells(Row, Col).Interior.Pattern xlNone Then '只要有一个颜色，则为阻挡 CanMoveRotate = False End If NextEnd Function我们还需要一个擦除当前方块的函数 EraseBlock，根据传递过来的坐标直接擦拭掉，代码如下： '擦除方块 By@yaxi_liuPrivate Sub EraseBlock(ByVal center_row As Integer, ByVal center_col As Integer, ByRef block() As Integer) Dim Row As Integer, Col As Integer Dim i As Integer For i = 0 To 3 Row = center_row + block(i, 0) Col = center_col + block(i, 1) MySheet.Cells(Row, Col).Interior.Pattern = xlNone MySheet.Cells(Row, Col).Borders.LineStyle = xlNone Next End Sub我们再编写移动方块的函数 MoveBlock，我们规定，形参 direction 代表方向，-1 代表向左，0 代表向下，1 代表向右，注意移动后需要保存当前坐标。新增形参 direction，代码如下： '移动方块 By@yaxi_liuPrivate Sub MoveBlock(ByVal center_row As Integer, ByVal center_col As Integer, ByRef block() As Integer, ByVal icolor As Integer, ByVal direction As Integer) Dim Row As Integer, Col As Integer Dim i As Integer Dim old_row As Integer, old_col As Integer '保存最早的中心坐标 old_row = center_row old_col = center_col '首先擦除掉原来位置的 Call EraseBlock(center_row, center_col, block) '-1 代表向左，1 代表向右，0 代表乡下 Select Case direction Case Is = -1 center_col = center_col - 1 Case Is = 1 center_col = center_col + 1 Case Is = 0 center_row = center_row + 1 End Select '再绘制 If CanMoveRotate(center_row, center_col, block) Then Call DrawBlock(center_row, center_col, block, icolor) '保存中心坐标 iCenterRow = center_row iCenterCol = center_col Else Call DrawBlock(old_row, old_col, block, icolor) '保存中心坐标 iCenterRow = old_row iCenterCol = old_col If direction = 0 Then bIsObjectEnd = True End If End If '保存方块坐标 For i = 0 To 3 MyBlock(i, 0) = block(i, 0) MyBlock(i, 1) = block(i, 1) Next End Sub移动方块实现后，我们再来编写旋转方块函数 RotateBlock，这里我们统一规定为逆时针旋转。跟移动函数一样，方法也是先擦除掉旧坐标的后，再根据新坐标绘制出新的方块。只不过旋转稍微麻烦一点。 不难计算出，假如一个向量 (x,y) 在逆时针旋转 90 度后的坐标为 (-y,x).根据这个公式，编写旋转函数。但是注意事先应该先判断是否达到旋转的条件。代码如下： '旋转方块函数 By@yaxi_liuPrivate Sub RotateBlock(ByVal center_row As Integer, ByVal center_col As Integer, ByRef block() As Integer, ByVal icolor As Integer) Dim i As Integer '先擦除原来的 Call EraseBlock(center_row, center_col, block) Dim tempArr(4, 2) As Integer '保存数组 For i = 0 To 3 tempArr(i, 0) = block(i, 0) tempArr(i, 1) = block(i, 1) Next '旋转后的坐标重新赋值 For i = 0 To 3 block(i, 0) = -tempArr(i, 1) block(i, 1) = tempArr(i, 0) Next i '重新绘制新的方块 If CanMoveRotate(center_row, center_col, block) Then Call DrawBlock(center_row, center_col, block, icolor) '保存方块坐标 For i = 0 To 3 MyBlock(i, 0) = block(i, 0) MyBlock(i, 1) = block(i, 1) Next Else Call DrawBlock(center_row, center_col, tempArr, icolor) '保存方块坐标 For i = 0 To 3 MyBlock(i, 0) = tempArr(i, 0) MyBlock(i, 1) = tempArr(i, 1) Next End If '保存中心坐标 iCenterRow = center_row iCenterCol = center_col End Sub这时候，旋转、移动函数均已编写完毕。为了能够让游戏相应键盘事件，我们需要在对应的工作表代码层添加事件函数，注意这里我们需要调用 Windows API。我们规定键盘的左键为方块向左 MoveObject (-1)，右键为方块向右 MoveObject (1)，下键为方块向下 MoveObject (0)，上键为方块旋转 RotateObject ()。我们再 Sheet1 工作表里面编写如下 WorkSheet 事件代码： '键盘事件代码，By@yaxi_liu#If VBA7 And Win64 Then Private Declare PtrSafe Function GetKeyboardState Lib "user32" (pbKeyState As Byte) As Long#Else Private Declare Function GetKeyboardState Lib "user32" (pbKeyState As Byte) As Long#End IfPrivate Sub Worksheet_SelectionChange(ByVal Target As Range) Dim keycode(0 To 255) As Byte GetKeyboardState keycode(0) If keycode(38) >

Call RotateObject ElseIf keycode (39) on 127 Then'> 127 Then 'right Call MoveObject (1) ElseIf keycode (40) > under 127 Then' Call MoveObject (0) ElseIf keycode (37) > 127 Then 'left Call MoveObject (- 1) End IfEnd Sub cannot be called directly in event response because of the formal parameters of our own defined MoveBlock and RotateBlock package class objects. Here, we will use two Public MoveObject and RotateObject functions to encapsulate the class module again to facilitate event calls. The code is as follows:

'moving object By@yaxi_liuPublic Sub MoveObject (ByVal dir As Integer) Call MoveBlock (iCenterRow, iCenterCol, MyBlock, ColorArr (iColorIndex), dir) End Sub' rotating object By@yaxi_liuPublic Sub RotateObject () Call RotateBlock (iCenterRow, iCenterCol, MyBlock, ColorArr (iColorIndex)) End Sub. Since the box function has been fully implemented, we randomly generate one for testing:

For convenience, we changed the text in button 1 to "start the game in four words":

Then, start writing code that runs automatically. Because Tetris is generated after the box, according to a certain speed of decline, once encountered obstacles after the end of the box, and then generate a new box, such a cycle. Since VBA does not support timers, we use the method of while (true) loop to generate blocks continuously. To avoid excessive use of CPU resources, we add a delay function between loops for loop calls. The code is as follows:

'delay function By@yaxi_liuPrivate Sub delay (T As Single) Dim T1 As Single T1 = Timer Do DoEvents Loop While Timer-T1 < TEnd Sub in the descending process, we need to know whether a certain line is full. The method is very simple, just query whether the whole row is painted. If it is full, we delete the line and fill in the first line to the line. Update the score at the same time. So we introduce another function, DeleteFullRow, with the following code:

'eliminate the full line function By@yaxi_liuPrivate Sub DeleteFullRow () Dim i As Integer, j As Integer For i = 1 To 20 For j = 2 To 11 If MySheet.Cells (I, j). Interior.ColorIndex < 0 Then Exit For ElseIf j = 11 Then MySheet.Range (Cells (1,2)) Cells (I-1, j). Cut Destination:=MySheet.Range (Cells (2,2), Cells (I) J) 'Range ("B2:K18") iScore = iScore + 10 End If Next j Next i MySheet.Range ("N1"). Value = "fraction" MySheet.Range ("O1"). Value = iScoreEnd Sub add a while loop to the Start () function The code added to the above two functions is as follows:

'Startup function By@yaxi_liuSub Start () Call Init While (True) Call GetBlock bIsObjectEnd = False 'whether this box object ends While (bIsObjectEnd = False) Call delay (0.5) Call MoveBlock (iCenterRow, iCenterCol, MyBlock, ColorArr (iColorIndex) 0) MySheet.Range ("L21"). Select With MySheet.Range ("B1:K20") .Borders (xlEdgeBottom). Weight = xlMedium .Borders (xlEdgeRight). Weight = xlMedium .Borders (xlEdgeLeft). Weight = xlMedium End With Wend Call DeleteFullRow Wend End Sub came here. The preparation of this game is completely over, click on the Sheet1 interface above the "button 1" button to start the game. Let's try again, the left key represents the left, the right button represents the right, the upper key represents rotation, and the lower key represents decline. Take a look at the effect:

Haha, the end of the trial is no problem, very perfect, although the process is long, but it is worth your careful study, and I hope you can experience the fun of programming. If you feel that you have learned the knowledge, I hope you will share this article with more people, thank you!

If you want the source code file, please click my name at the beginning of the article to follow my official account, reply "Tetris" in the dialogue bar, and I will send you the source code and files to facilitate your later study of the code.

This article comes from the official account of Wechat: coding ID:gh_f65e0111d17a, author: Liu Yaxi

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.