Chapter 14 - Excel VBA for dummies (1 người xem)

[kyo]

I am trying to translate the 14th chapter of the book "VBA for dummies". Although my translation has many mistakes, I believe that my uncles and my aunts will help me to corect my mistakes. Thanks a lot

Con cố thử dịch chương 14 của cuốn sách "VBA for dummies". Mặc dù bài dịch của con có rất nhiều lỗi, con tin rằng các chú, các cô sẽ giúp con sửa các lỗi này. Con cám ơn chú nhiều

My philosophy for learning how to write Excel macros-places heavy emphasis on examples. I find that a well-thought-out example often communicates a concept much better than a lengthy description of the underlying theory. Because you’re reading this book, you probably agree with me. This chapter presents several examples that demonstrate common VBA techniques

Triết lý của tôi về việc nghiên cứu cách viết những đoạn Excel Macro thế nào nhấn mạnh trên các thí dụ. Tôi nhận thấy rằng một thí dụ được cân nhắc kỹ thường truyền đạt một quan điểm tốt hơn sự mô tả dài dòng lý thuyết cơ bản. Vì bạn đang đọc cuốn sách này, có lẽ bạn đồng ý với tôi. Chương này trình bày vài thí dụ để giải thích những kỹ thuật VBA chung

I organize these examples into the following categories :
- Working with ranges
- Changing Excel settings
- Working with charts
- Speeding up your VBA code

Tôi tổ chức các thí dụ thành các loại sau :
- Làm việc với các dãy
- Thay đổi các thiết lập của Excel
- Làm việc với các biểu đồ
- Cải thiện tốc độ code VBA của bạn

Although you might be able to use some of the examples directly, in most cases you must adapt them to your own needs

Mặc dù bạn có thể sử dụng trực tiếp một vài thí dụ này, nhưng trong nhiều trường hợp bạn cần chỉnh lại cho phù hợp với nhu cầu của bạn

Working with Ranges – Làm việc với các vùng

Most of your VBA programming probably involves worksheet ranges. (For a refresher course on Range objects, refer to Chapter 8. When you work with Range objects, keep the following points in mind

- Your VBA doesn’t need to select range to work with it
- If your code does select a range, its worksheet must be active
- The macro recorder doesn’t always generate the most efficient code. Often, you can create your macro by using the recorder and then edit the code to make it more efficient
- It’s a good idea to use named range in your VBA code. For example, using Range(Total) is better using Range(D45). In latter case, if you add a row above row 45, you need to modify the macro so it uses the correct range address (D46)
- When running a macro that works on the current range selection, the user might select entire columns or rows. In most cases, you don’t want to loop through every cell in the selection (that could take a long time). Your macro should create a subset of the selection consisting of the only the nonblank cells.
- Excel allows multiple selections. For examples, you can select range, press Ctrl, and select another range. (Do your range selection with the mouse, of course). You can test for this in your macro and take appropriate actions

Hầu hết chương trình VBA của bạn có lẽ bao gồm những vùng của bảng tính. (Nhắc lại tài liệu về các đối tượng (Object) vùng, hãy tham khảo chương 8). Khi bạn làm việc với đối tượng vùng, hãy chú ý các vấn đề sau đây :

- VBA của bạn không cần thiết chọn một vùng để làm việc với nó
- Nếu code của bạn chọn một vùng, bảng tính phải được kích hoạt
- Việc ghi bằng Macro luôn phát sinh những đoạn code kém hiệu quả. Thường, bạn có thể tạo Macro bằng cách sử dụng việc ghi lại và sau đó, chỉnh sửa lại đoạn code để làm cho nó hiệu quả hơn
- Thật là một ý tưởng hay nếu sử dụng một vùng được đặt tên trong code VBA của bạn. Thí dụ, việc sử dụng Range(Total) sẽ tốt hơn Range(D45). Trong trường hợp sau này, nếu bạn thêm vào một hàng phía trên hàng 45, bạn cần hiệu chỉnh lại macro để code dùng địa chỉ đúng là (D46)
- Khi chạy một macro đang làm việc trên một dãy được chọn hiện hành, người dùng có lẽ đã chọn toàn bộ những hàng hoặc những cột. Trong hầu hết các trường hợp, bạn không muốn lặp qua mọi cell trong vùng chọn (điều này cần nhiều thời gian ). Macro của bạn nên tạo một tập hợp con của vùng chọn chỉ gồm những Cell không rỗng
- Excel cho phép chọn nhiều vùng. Thí dụ, bạn có thể chọn một vùng, nhấn Ctrl, và chọn một vùng khác. (Dĩ nhiên, bạn phải thực hiện việc này với chuột). Bạn có thể thử nghiệm việc này với Macro của bạn và thực hiện những hành động thích hợp

This examples in this section, which are available at this book’s Web site, demonstrate these points

If you prefer to enter these examples yourself, press Alt+F11 to activate the VBE. Then insert a VBA module and type the code. Make sure that the workbook is set up properly. For example, if the example uses two sheets named Sheet1 and Sheet2, make sure that the workbook has sheets with those names

Những thí dụ trong phần này sẵn có trên Website của sách, chứng minh những quan điểm trên

Nếu bạn thích nhập vào những thí dụ của chính bạn hơn, hãy nhấn Alt+F11 để kích hoạt VBE. Sau đó, bạn insert một module VBA, và đánh code vào. Hãy chắc chắn rằng workbook được thiết lập một cách thích hơp. Thí dụ, nếu trong thí dụ sử dụng 2 bảng tính được đặt tên là Sheet1 và Sheet2, hãy chắc chắn rằng Workbook của bạn có các trang bảng tính có những tên kia.

 

[kyo]

Copy a range – Sao chép một dãy

Copy a range – Sao chép một dãy

Copying a range ranks right up there as one of the most favorite Excel activities of all time. When you turn on the macro recorder and copy a range from A1:A5 to B1:B5, you get this VBA macro :

Sao chép một dãy sang bên phải là một trong những phạm vi hoạt động của Excel được yêu thích nhất. Khi bạn bật Macro ghi lại và sao chép một dãy từ A1:A5 sang B1:B5, bạn sẽ thu được Macro này :

Sub CopyRange()
 
Range(“A1:A5”).Select
Selection.Copy
Range(“B1”).Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub

Notice the last statement. This statement was generated by pressing Esc, which cancels the marching ants display that appears in the worksheet whwn you copy a range
This macro works fine, but you can copy a range more efficiently than this. You can produce the same result with the following one-line macro, which doesn’t select any cells :

Hãy chú ý dòng lệnh cuối. Dòng lệnh này được phát sinh bởi việc nhấn phím Esc, nhằm huỷ bỏ đàn kiến hành quân xuất hiện trong bảng tính khi bạn sao chép một dãy
Macro này làm việc rất tốt, nhưng bạn có thể sao chép một vùng hiệu quả hơn macro này. Bãn có thể thu được kết quả tương tự với một macro chỉ 1 dòng như dưới đây mà không cần chọn bất kỳ một cell nào

Sub CopyRange2()
Range(“A1:A5).Copy Range(“B1”)
End Sub

This procedure takes advantage of the fact that the Copy method can use an argument that specifies the destination. This example also demonstrates that the macro recorder doesn’t always generate the most efficient code

Thủ tục này xem như một thuận lợi của sự việc mà phương thức Copy chỉ cần sử dụng một đối số đặc trưng cho điểm cuối. Thí dụ này cũng chứng minh rằng việc ghi macro không luôn phát sinh code hiệu quả nhất

 

[kyo]

Copying a variable–sized range – Sao chép một vùng có kích thước thay đổi

Copying a variable–sized range – Sao chép một vùng có kích thước thay đổi

In many cases, you need to copy a range of cells but don’t know the exact row and column dimensions. For example, you might have a workbook that tracks weekly sales. The number of rows changes as you add new data.

Trong nhiều trường hợp, bạn cần sao chép một vùng ô nhưng không biết chính xác kích thước hàng và cột. Thí dụ, bạn có thể có một workbook theo dõi việc bán hàng hàng tuần. Số lượng hàng thay đổi khi bạn thêm vào dữ liệu mới

Figure 14-1 shows a range on a worksheet. This range consists of several rows, and the number of rows can change from day to day. Because you don’t know the exact range address at any given time, writing a macro to copy the range can be challenging. Are you up for the challenge ?

Hình 14-1 trình bày một vùng của bảng tính. Vùng này bao gồm một số hàng và số hàng này có thể thay đổi từ ngày này sang ngày khác. Bởi vì bạn không biết chính xác địa chỉ của vùng tại bất kỳ thời điểm thực hiện, nên việc viết macro để sao chép vùng có thể là một thử thách. Bạn có đối mặt với thử thách không ?

The following macro demonstrates how to copy this range from Sheet1 to Sheet2 (beginning at cell A1). It uses the CurrentRegion property, which returns a Range object that corresponds to the block of cells around a particular cell. In this case, that’s A1

Macro dưới đây giải thích cách sao chép vùng này từ Sheet1 sang Sheet2 (bắt đầu tại Cell A1). Nó sử dụng thuộc tính CurrentRegion, thuộc tính này trả kết quả là một vùng tương ứng với một khối ô xung quanh ô có liên quan. Trong trường hợp này, đó là A1

Sub CopyCurrentRegion()
Range(“A1”).CurrentRegion.Copy
Sheets(“Sheet2”).Select
Range(“A1”).Select
ActveSheet.Paste
Sheets(“Sheet1”).Select
Application.CutCopyMode = False
End Sub

Using the CurrentRegion property is equivalent to choosing the Edit / GoTo command, clicking the Special button, and selecting the Current region option. To see how this works, record your action while issuing that command. Generally, the CurrentRegion consists of a rectangular block of cells surrounded by one or more blank rows or columns


Sử dụng thuộc tính CurrentRegion tương đương với việc chọn lệnh Edit / GoTo, nhấn nút Special, và chọn option Current Region. Để xem công việc này thế nào, hãy ghi lại hành động của bạn trong khi bạn thao tác lệnh đó. Thông thường, CurrentRegion gồm một khối ô hình chữ nhật được bao quanh bởi một hay nhiều hàng hay cột trống

You can make this macro even more efficient by not selecting the destination. The following macro takes advantage of the fact that the Copy method can use an argument for the destination range :

Bạn có thể thực hiện Macro này bằng cách không chọn điểm đích. Macro dưới đây thực hiện sự việc thuận lợi hơn bằng phương thức Copy có thể sử dụng một đối số cho dãy đích

Sub CopyCurrentRegion2()
Range(“A1”).CurrentRegion.Copy_
Sheets(“Sheet2”).Range(“A1”)
Application.CutCopyMode = False
End Sub

 

[kyo]

Selecting to the end of a row or a column - Chọn đến cuối hàng hay cột

Selecting to the end of a row or a column - Chọn đến cuối hàng hay cột

You’re probably in the habit of using key combinations such as Ctrl + Shift + Right Arrow and Ctrl + Shift + Down arrow to select a range that consists of everything from the active cell to the end of a row or a column. Not surprisingly, you can write macros that perform these types of selections.

Có lẽ bạn có thói quen sử dụng tổ hợp phím Ctrl + Shift + phím mũi tên bên phải và Ctrl + Shift + phím mũi tên xuống dưới để chọn một dãy bao gồm bất cứ những gì từ ô hiện hành đến cuối hàng hay cột. Không có gì ngạc nhiên, bạn có thể viết những macro để thực hiện những kiểu chọn lựa này

You can use the CurrentRegion property to select an entire block of cells. But what if you want to select, say, one column from a block of cells ? Fortunately, VBA can accommodate this type of action. The following VBA procedure selects the range beginning at the active cell and extending down to the cell just above the first blank cell in the column. After selecting the range, you can do whatever you want it – copy it, move it, format it, and so on

Bạn có thể sử dụng thuộc tính CurrentRegion để chọn toàn bộ khối các ô. Nhưng cái gì sẽ xảy ra nếu bạn muốn chọn, nói thế này, một cột của khối các ô đó ? Thật may mắn, VBA có kèm theo loại hoạt động này. Thủ tục VBA theo sau chọn một vùng bắt đầu từ ô hiện hành và trải xuống dưới đến ngay ô phía trên hàng trống đầu tiên của cột. Sau khi chọn xong vùng này, bạn có thể thực hiện bất kỳ việc gì bạn muốn – sao chép vùng, di chuyển vùng, định dạng vùng, và vân vân

Sub SelectDown()
Range(ActiveCell, ActiveCell.End(xlDown)).Select
End Sub

This example uses the End method of the ActiveCell object, which returns a Range object. The End method takes one argument, which can be any of the following constants :


Những thí dụ này sử dụng phương thức End của đối tượng ô hiện hành, mà kết quả thu được là đối tượng vùng. Phương thức End cần một đối số, điều này có thể được thực hiện với bất cứ các hằng số sau : (chú sửa giúp con – con không biết dịch chỗ này thế nào )

- xlUp
- xlDown
- xlToLeft
- xlToRight​

Keep in mind that it’s unnecessary to select a range before doing something with it. The following macro applies bold formatting to a variable-sized range without selecting the range

Hãy nhớ rằng không cần phải chọn một vùng trước khi thực hiện một điều gì với nó. Macro sau đây kèm theo việc định dạng chữ đậm đối với một vùng có kích thườc thay đổi không có việc chọn một dãy ô

Sub MakeBold()
Range(ActiveCell. ActiveCell.End(xlDown)).Font.Bold = True
End Sub

 

[kyo]

Selecting a row or a column - Chọn một hàng hay một cột

Selecting a row or a column - Chọn một hàng hay một cột

The following procedure demonstrates how to select the column containing the active cell. It uses the EntireColumn property, which returns a Range object that consists of a full column :

Thủ tục sau đây chỉ ra cách chon lựa một cột có chứa ô hiện hành như thế nào. Nó sử dụng thuôc tính EntireColumn, thuộc tính này trả kết quả là đối tượng vùng gồm toàn bộ một cột.

Sub SelectColumn()
ActiveCell.EntireColumn.Select
End Sub

As you may expect. VBA also offers an EntireRow property, which returns a Range object that consists of an entire row

Như bạn mong chờ. VBA cũng cung cấp thuộc tính EntireRow, thuộc tính này trả kết quả về một đối tượng vùng gồm toàn bộ một hàng

 

[kyo]

Moving a Range – Di chuyển một vùng

Moving a Range – Di chuyển một vùng

You move a range by cutting it to the clipboard and then pasting it in another area. If you record your actions while performing a move operation, the macro recorder generates code like the following

Bạn có thể di chuyển một vùng bằng cách cắt vùng dữ liệu vào clipboard và sau đó dán nó vào một nơi khác. Nếu bạn ghi lại hành động của bạn trong khi bạn thực hiện thao tác di chuyển này, việc ghi macro phát sinh đoạn code như dưới đây :

Sub MoveRange()
Range(“A1:C6”).Select
Selection.Cut
Range(“A10”).Select
ActiveSheet.Paste
End Sub

As with the copying example earlier in this chapter, this is not the most efficient way to move a range of cells. In fact, you can move a range with a single VBA statement, as follows :


Cũng như thí dụ về việc sao chép trước đây trong chương này, đây không phải là cách hiệu quả nhất để di chuyển một vùng các ô. Thật vậy, bạn có thể di chuyển một vùng với duy nhất 1 dòng lệnh VBA, như sau

Sub MoveRange2()
Range("A1:C6").Cut Range("A10")
End Sub

This macro takes advantage of the fact that the Cut method can use an argument that specifies the destination. Notice also that the range was not selected. The cell pointer remains in its original position

Macro này thực hiện lợi điểm của sự việc mà phương thức Cut có thể sử dụng một đối số đặc trưng cho điểm đích. Cũng để ý rằng vùng cũng không được chọn. Con trỏ ô giữ nguyên vị trí ban đầu của nó

 

[kyo]

Looping through a range efficiently - Lặp qua một vùng một cách hiệu quả

Many macros perform an operation on each cell in a range, or they might perform selected actions based on each cell’s content. These macros usually include a For-Next loop that processes each cell in the range

Nhiều Macro thực hiện thao tác trên mỗi ô của vùng, hoặc chúng có thể thực hiện những tác động chọn lọc dựa trên nội dung của mỗi ô. Những Macro này luôn luôn bao gồm vòng lặp For-Next xử lý trên mỗi ô của vùng

The following example demonstrates how to loop through a range of cells. In this case, the range is the current selection. A variable named Cell refers to the cell being processed. Within the For-Next loop, the single statement evaluates the cell and changes its interior color if the cell contains a positive value

Thí dụ dưới đây giải thích việc lặp qua một vùng ô như thế nào. Trong trường hợp này, vùng là vùng chọn hiện hành. Một biến số được đặt tên là Cells tham chiếu đến ô đang được xử lý. Trong thời gian lặp For-Next, dòng lệnh duy nhất định giá trị ô và thay đổi màu nền của ô nếu ô chứa giá trị dương

Sub ProcessCells()
Dim Cell As Range
For Each Cell In Selection
If Cell.Value >0 Then Cell.Interior.ColorIndex = 6
Next Cell
End Sub

This example works, but what if the selection consists of an entire column or row ? This is not uncommon because Excel lets you perform operations on entire rows or columns. In such a case, the macro seems to take forever because it loops through each cell in the selection – even the blank cells. To make the macro more efficient, you need a means for processing only the nonblank cells


Thí dụ này làm việc, nhưng cái gì sẽ xảy ra nếu vùng chọn bao gồm toàn bộ cột hay hàng ? Điều này hiếm thấy vì Excel để bạn thực hiện thao tác trên toàn bộ cột hay hàng. Trong trường hợp như thế, macro dường như thực hiện mãi mãi bởi vì nó lặp qua mỗi ô của vùng chọn – ngay cả những ô trống. Để thực hiện macro hiệu quả hơn, bạn cần điều kiện cho quá trình chỉ thực hiện trên những ô không trống

The following routine does just that by using the SpecialCells method. (Refer to the VBA Help system for specific details about its argument). This routine uses the Set keyword to create two new objects : the selection’s subset that consists of cells with constants and the selection’s subset that consists of cells with formulas. The routine processes each of these subsets, with the net effect of skipping all blank cells. Pretty slick, eh ?

Thủ tục dưới đây đúng hơn bằng cách sử dụng phương thức SpecialCells. (Tham khảo hệ thống trợ giúp VBA cho những chi tiết cụ thể về đối số của nó). Thủ tục này sử dụng từ khoá Set để tạo thành 2 đối tượng mới : tập con của sự chọn lựa bao gồm các ô với các hằng số, và tạp con của sự chọn lựa bao gồm các ô với các công thức. Thủ tục xử lý tại mỗi tập con này, với hiệu quả thực sự của việc bỏ qua những ô trống. Một sự khéo léo thú vị, phải không ?

Sub SkipBlanks()
Dim ConstantCells As Range
Dim FormulaCells As Range
Dim cell As Range
‘Ignore errors
On error Resume Next
‘Process the constants
Set ConstantCells = Selection _
.SpecialCells(xlConstant)
For Each cell In ConstantCells
If cell.Value > 0 Then
Cell.Interior.ColorIndex = 6
End If
Next Cell
‘Process the formulas
Set FormulaCells = Selection _
.SpecialCells(xlFormulas)
For Each cell In FormulaCells
If Cell.Value > 0 Then
Cell.Interior.ColorIndex = 6
End If
Next Cell
End Sub

The SkipBlanks procedure works equally fast, regardless of what you select. For example, you can select the range, all columns in the range, all rows in the range, or even the entire worksheet. It’s a vast improvement over the ProcessCells procedure presented earlier in this section
Notice that I use the following statement in this code

Thủ tục SkipBlanks làm việc nhanh tương đương nhau, bất chấp bạn chọn cái gì. Thí dụ, bạn có thể chọn một vùng, hoặc tất cả các cột trong một vùng, hoặc tất cả các hàng trong một vùng, hoặc ngay cả toàn bộ bảng tính. Nó là một sự cải thiện rất lớn vượt qua phương thức ProcessCells được trình bày trước đây trong chương này
Hãy chú ý rằng tôi sử dụng dòng lệnh dưới đây trong code này :

On Error Resume Next

This statement tells Excel to ignore any errors that occur and simply process the next statement (see Chapter 12 for a discussion of error handling). This statement is necessary because the SpecialCells method produces an error if no cells qualify

Dòng lệnh này nói lên để Excel bỏ qua bất kỳ các lỗi xảy ra và xử lý (dễ dàng) 1 cách đơn giản là nhảy sang dòng lệnh kế tiếp (xem chương 12 thảo luận về bẫy lỗi). Dòng lệnh này cần thiết vì phương thức SpecialCells tạo ra lỗi nếu không có những ô đủ điều kiện

Using the SpecialCells method is equivalent to choosing the Edit / GoTo command, clicking the Special button and selecting the Constants option or the Formulas option. To get a feel for how this works, record your actions while you issue that command and select various options

Sử dụng phương thức SpecialCells tương đương với việc chọn lệnh Edit / GoTo, nhấn nút Special và chọn Constants option hay Formulas option. Để nắm chắc công việc này thế nào, hãy ghi lại hành động của bạn trong khi bạn phát ra những lệnh và chọn những sự lựa chọn khác nhau

 

[kyo]

Prompting for a cell value - Gợi ý cho giá trị của một ô

As shown in Fifure 14-2, you can use VBA’s InputBox function to get a value from the user. Then you can insert that value into a cell. The following procedure demonstrates how to ask the user for a value and place the value in cell A1 of the active worksheet, using only one statement

Như hình 14-2 được trình bày, bạn có thể sử dụng hàm InputBox của VBA để có được một giá trị từ người sử dụng. Sau đó, bạn có thể nhập giá trị đó vào trong một ô. Thủ tục dưới đây giải thích làm thế nào yêu cầu người sử dụng cho giá trị và đặt giá trị vào ô A1 của bảng tính hiện hành, sử dụng chỉ một dòng lệnh :

Sub GetValue()
Range(“A1”).Value = InputBox(“Enter the value for cell A1”)
End Sub

If you try out this example, you find that clicking the Cancel button in the InputBox erases the current value in cell A1. The following macro demonstrates a better approach : using a variable (x) to store the value entered by the user. If the valua is not empty (that is, the user didn’t click cncel), the value of x is placed into cell A1. Otherwise, nothing happens


Nếu bạn kiểm tra thí dụ này, bạn thấy rằng việc nhấn nút Cancel trong InputBox xoá giá trị hiện tại trong cell A1. Macro dưới đây chứng minh một phương pháp tốt hơn : sử dụng một biến (x) để chứa giá trị người dùng nhập vào. Nếu giá trị không rỗng (đó là, người dùng không nhấn nút Cancel), giá trị của x được đặt vào ô A1. Ngoài ra, không (có gì xảy ra) làm gì cả.

Sub GetValue2()
Dim x As Variant
X = InPutBox(“Enter the value for cell A1”)
If x <> “” Then Range(“A1”).Value = x
End Sub

The variable x is defined as variant because it could be a number or an empty string (if the user clicks Cancel)


Biến x được khai báo (xác định) như một biến Variant (thể) vì nó có thể là một số hay một chuỗi rỗng (nếu người dùng nhấn nút Cancel)

 

[kyo]

Determining the selection type – Xác dịnh kiểu lựa chọn

If you design your macro to work with a range selection, the macro must be able to determine whether a range is actually selected. If something other than a range is selected (such as a chart or a shape), the macro will probably bomb. The following procedure uses the VBA TypeName function to identify the type of object that is currently selected :

Nếu bạn thiết kế Macro của bạn để làm việc với vùng chọn, macro phải có thể xác định được một vùng thực sự được chọn hay không. Nếu một cái gì đó khác hơn một vùng được chọn (như một đồ thị hay một hình), macro có lẽ ném bom (bomb – Kyo không dịch được)
Thủ tục dưới đây sử dụng hàm VBA TypeName để nhận ra kiểu đối tượng đang được chọn

Sub SelectionType()
MsgBox TypeName(Selection)
End Sub

If a Range object is selected, the MsgBox displays Range. If your macro works only with ranges, you can use an If statement to ensure that a range is selected. This example displays a message and exit (exists) the procedure if the current selection is not a Range object :

Nếu một đối tượng vùng được chọn, hộp MsgBox hiển thị vùng đó. Nếu macro của bạn chỉ làm việc với những vùng, bạn có thể dùng câu lệnh If để củng cố rằng vùng phải được chọn. Thí dụ này trình bày một thông báo và thoát khỏi (tồn tại) thủ tục nếu sự lựa chọn hiện thời không phải là đối tượng vùng

Sub CheckSelection()
If TypeName(Selection) <> “Range” Then
MsgBox “Select a Range”
Exit Sub
End If
……………..(Other statements go here) – (Những dòng lệnh khác ở đây)
End Sub

 

[kyo]

Identifying a multiple selection - Nhận biết nhiều vùng chọn

As you know, Excel allows multiple selections by pressing Ctrl while choosing objects or ranges. This can cause problems with some macros. For example, you can’t copy a multiple selection that consists of nonadjacent ranges. (Try it if you don’t believe me)

Như bạn biết, Excel cho phép chọn nhiều vùng bằng cách nhấn phím Ctrl và chọn các đối tượng hoặc các vùng. Việc này gây ra nhiều vấn đề với một vài Macro. Thí dụ, bạn không thể sao chép nhiều vùng gồm mhững vùng không kề cận nhau (Hãy thử nếu bạn không tin tôi)


The following macro demonstrates how to determine whether the user made a multiple selection, so your macro can take appropriate action :

Macro dưới đây giải thích làm thế nào để xác định dù người dùng có thực hiện nhiều vùng chọn hay không, để (vì) macro của bạn có thể thực hiện tác động thích hợp

Sub MultipleSelection()
If Selection.Areas.Count > 1 then
MsgBox “Multiple selections not allowed”
Exit Sub
End If
…. (Other statements go here)
End Sub

This example uses the Areas method, which returns a collection of all objects in the selection. The Count property returns the number of objects in the collection



Thí dụ này sử dụng phương thức Areas, phương thức này sẽ cho kết quả là một tập hợp tất cả các đối tượng trong vùng chọn
Thuộc tính Count cho kết quả là số lượng các đối tượng trong tập hợp

 

[kyo]

Changing Excel Settings – Thay đổi các thiết lập của Excel

Some of the most useful macros are simple procedures that change one once or more of Excel’s settings. For example, simply changing the recalculation mode from automatic to manual requires numerous steps. You can save yourself some keystrokes and menu choices (not to mention time) by creating a macro that automates this task

Một vài macro có ích nhất là những thủ tục đơn giản làm thay đổi một lần hay nhiều hơn các thiết lập của Excel. Thí dụ, đơn giản là thay đổi cách tính toán lại của Excel từ tự động sang thủ công cần nhiều bước. Bạn có thể tự lưu một vài phím bấm và lựa chọn menu (chưa tính đến thời gian) bằng việc tạo ra macro để tự động hoá nhiệm vụ này

This section presents two examples that show you how to change settings in Excel. You can apply the general principles demonstrated by these examples to other operations that change settings

Phần này đưa ra 2 thí dụ giải thích bạn làm thế nào để thay đổi các thiết lập của Excel. Bạn có thể áp dụng những nguyên tắc chung được giải thích bằng những thí dụ này cho các phép toán khác nhau để thay đổi các thiết lập

Changing Boolean settings – Thay đổi các thiết lập Boolean

Like a light switch, a Boolean setting is either on or off. For example, you might want to create a macro that turns the worksheet row and the column headings on and off. With the headings turned on, Excel generates the following code if you record your actions while accessing the Options dialog box :

Giống như công tắc đèn, thiết lập Boolean là bật họăc tắt. Thí dụ, bạn có thể tạo macro để bật hoặc tắt các tiêu đề hàng hay cột của bảng tính. Với tiêu đề được bật lên, Excel sinh ra code dưới đây nếu bạn ghi lại hành động của bạn trong khi vào hộp thoại Options

ActiveWindow.DisplayHeadings = [COLOR=green]True[/COLOR] [COLOR=cyan]False[/COLOR]

On the other hand, if the headings are turned off when you record the macro Excel generates the following code :

Mặt khác, nếu các tiêu đề được tắt trong khi bạn ghi macro, Excel sinh ra code sau đây :

ActiveWindow.DisplayHeadings = [COLOR=green]False[/COLOR] [COLOR=cyan]True[/COLOR]

This may lead you suspect that you need two macros : one to turn on the headings and one to turn them off. Not true. The following procedure uses the Not operator to effectively toggle the heading display from True to False and from False to True :

Điều này khiến bạn nghi ngờ rằng bạn cần 2 macro : một để bật các tiêu đề và một để tắt chúng. Không đúng. Thủ tục sau đây sử dụng toán tử Not để thay đổi qua lại sự trình bày tiêu đề một cách hiệu quả hơn từ True sang False và từ False sang True :

Sub ToggleHeadings()
If TypeName(ActiveSheet) <> “Worksheet” Then Exit Sub
ActiveWindow.DisplayHeadings = Not _
ActiveWindow.DisplayHeadings
End Sub

The first statement ensures that the active sheet is a worksheet. (Chart sheets don’t have headings) If a worksheet is not active, the procedure ends. You can use this technique with any settings that have Boolean (True or False) values.

Dòng lệnh đầu tiên bảo đảm rằng bảng tính hoạt động là worksheet. (Bảng tính đồ thị không có tiêu đề). Nếu bảng tính không được kích hoạt, thủ tục sẽ kết thúc. Bạn có thể sử dụng kỹ thuật này với bất kỳ các thiết lập nào có giá trị Boolean (True hay False)

 

[kyo]

Changing non-Boolean settings – Thay đổi các thiết lập không phải là Boolean

Use Select Case structure for non-Boolean settings. This example toggles the calculation mode between manual and automatic and displays a message indicating the current mode

Sử dụng cấu trúc Select Case cho các thiết lập không phải là Boolean. Thí dụ này sẽ thay đổi qua lại cách thức tính toán giữa thủ công và tự động và trình bày một thông báo chỉ định cách thức hiện thời

Sub ToggleCalcMode()
Select Case Application.Calculation
Case xlManual
Application.Calculation = xlCalculationAutomatic
MsgBox “Automatic Calculation Mode”
Case xlAutomatic
Application.Calculation = xlCalculationManual
MsgBox “ Manual Calculation Mode”
End Select
End Sub

You can adapt this technique for changing other non-Boolean settings

Bạn có thể áp dụng kỹ thuật này để thay đổi các thiết lập không phải là Boolean

 

[kyo]

Working with Charts – Làm việc với các biểu đồ

Charts are packed with different objects, so manipulating charts with VBA can be quite confusing. To get a feel for this, turn on the macro recorder, create a chart, and perform some routine chart-editing tasks. You may be surprised by the amount of code Excel generates. After you understand the objects in a chart, however, you can create some useful macros

Biểu đồ được đóng gói cùng với nhiều đối tượng khác, vì thế thao tác chỉnh sửa biểu đồ bằng VBA khá phức tạp.những biểu đồ thao tác thủ công với VBA có thể khá lộn xộn. Để đạt được việc này, bật bộ ghi macro lên, tạo đồ thị, và hoàn tất một vài thao tác biên tập đồ thị quen thuộc. Bạn có lẽ ngạc nhiên bởi số lượng code Excel đã phát sinh. Sau khi bạn hiểu được những đối tương trong biểu đồ, song, bạn có thể tạo một vài macro có ích

To write macros that manipulate charts, you must understand some terminology. An embedded chart on a worksheet is ChartObject object. You can activate a ChartObject much like you activate a sheet. The following statement activates the ChartObject named Chart 1 :

Để viết macro chỉnh sửa điều khiển thủ công các biểu đồ, bạn phải hiểu được một vài thuật ngữ. Một biểu đồ được nhúng vào bảng tính là một đối tượng ChartObject. Bạn có thể kích hoạt một ChartObject giống hệt như bạn kích hoạt một bảng tính. Dòng lệnh dưới đây kích hoạt ChartObject có tên là Chart1 :

ActiveSheet.ChartObjects(“Chart 1”).Activate

After you activate the chart, you can refer to it in your VBA code as the ActiveChart. If the chart is on a separate chart sheet, it becomes the active chart as soon as you activate that chart sheet

Sau khi bạn kích hoạt biểu đồ, bạn có thể tham chiếu đến nó trong code VBA của bạn như ActiveChart. Nếu biểu đồ nằm trên một bảng tính biểu đồ riêng, nó trở thành biểu đồ hoạt động ngay chỉ khi nào bạn kích hoạt bảng tính biểu đồ đó

When you click an embedded chart, Excel actually selects an object inside the ChartObject object. You can select the ChartObject itself by pressing Ctrl while clicking the embedded chart. Select the ChartObject if you want to change an embedded chart’s name. After selecting the ChartObject object, use the Name box (the control to the left of the formula bar) to change the name

Khi bạn nhấp vào một biểu đồ được nhúng , Excel chọn thực sự một đối tượng bên trong biểu đồ ChartObject. Bạn có thể chọn chính ChartObject bằng cách nhấn Ctrl trong khi nhấp chọn biểu đồ được nhúng vào. Chọn ChartObject nếu bạn muốn thay đổi tên biểu đồ được nhúng đó. Sau khi chọn đối tượng ChartObject, sử dụng NameBox (điều khiển ở bên trái thanh formula) để thay đổi tên

 

[kyo]

Modifying the chart type - Bổ sung kiểu đồ thị

Here’s a confusing statement for you : A ChartObject object acts as a container for a Chart object

Đây là một lời phát biểu lộn xộn dành cho bạn : Một ChartObject hoạt động như một vật chứa cho các đối tượng biểu đồ

To modify a chart with VBA, you don’t have to activate the chart. Rather, the Chart method can return the chart contained in the ChartObject. Are you thoroughly confused yet ? The following two procedures have the same effecf – they change the chart named Chart 1 to an area chart. The first procedure activates the chart first : the second one doesn’t. The built-in constant xlArea represents an area chart

Để chỉnh sửabổ sung một biểu đồ với VBA, bạn không phải kích hoạt biểu đồ. Đúng hơn, phương thức Chart có thể trả về biểu đồ được chưa trong ChartObject. Bạn có thấy hoàn toàn rắc rồi chưa ? 2 thủ tục dưới đây có hiệu quả giống nhau – chúng thay đổi biểu đồ có tên là Chart 1 thành một biểu đồ dạng Area.miền biểu đồ. Thủ tục đầu tiên kích hoạt biểu đồ đầu tiên, cái thứ hai thì không. Hằng số dựng sẵn xlArea đại diện cho biểu đồ dạng Area một miền biểu đồ

Sub ModifyChart1()
ActivateSheet.ChartObjects(“Chart 1”).Activate
ActiveChart.Type = xlArea
ActiveWindow.Visible = False
End Sub

Sub ModifyChart2()
ActiveSheet.ChartObjects(“Chart 1”).Chart.Type = xlArea
End Sub

 

[kyo]

Looping through the ChartObjects collection - Lặp qua tập hợp ChartObjects

This example changes the chart type of every embedded chart on the active sheet. The procedure uses a For-Next loop to cycle through each object in the ChartObjects collection, access the Chart object in each, and change its Type property

Thí dụ này thay đổi kiểu đồ thị của bất cứ đồ thị nào được nhúng vào bảng tính hiện hành. Thủ tục sử dụng vòng lặp For-Next vòng qua mỗi đối tượng trong tậphợp ChartObjects, truy cập vào mỗi đối tượng biểu đồ và thay đổi thuộc tínhType của nó

Sub ChartType()
Dim cht as ChartObject
For Each cht In ActiveSheet.ChartObjects
Cht.Chart.Type = xlArea
Next cht
End Sub

The following macro performs the same function but works on all chart sheets in the active workbook :

Macro dưới đây thực hiện chức năng tương tự, nhưng làm việc trên tất cả các bảng tính biểu đồ trong workbook hoạt động

Sub ChartType2()
Dim cht As Chart
For Each cht In ActiveWorkbook.Charts
Cht.Type = xl.Area
Next cht
End Sub

 

[kyo]

Modifying properties - Bổ sung thuộc tính

The following example changes the Legend font for all charts on the active sheet. It uses a For-Next loop to process all ChartObject objects :

Thí dụ sau đây thay đổi font Legend cho tất cả các biểu đồ trên bảng tính hoạt động. Nó sử dụng vòng lặp For-Next để thực hiệntrên tất cả các đối tượng ChartObject :

Sub LegendMod()
Dim cht As ChartObject
For Each cht In ActiveSheet.ChartObjects
With cht.Chart.Legend.Font
.Name = “arial”
.FontStyle = “Bold”
.Size = 12
End With
Next cht
End Sub

Note that the Font object is contained in the Legend object, which is contained in the Chart object, which is contained in the ChartObjects collection. Now do you understand why it’s called an object hierarchy ?


Ghi chú rằng đối tượng Font được chứa trong đối tượng Legend, đối tượng này được chứa trong đối tượng Chart, đối tượng Chart được chứa trong tập hợp ChartObjects. Bây giờ, bạn có hiểu tại sao nó được gọi là một cấp bậc đối tượng không ?

 

[kyo]

Applying chart formatting – Áp dụng định dạng biểu đồ

This example applies several different types of formatting to the active chart :

Thí dụ này áp dụng một vài kiểu định dạng cho biểu đổ hoạt động :

Sub ChartMods()
ActiveChart.Type = xlArea
ActiveChart.ChartArea.Font.Name = “Arial”
ActiveChart.ChartArea.Font.FontStyle = “Regular” 
ActiveChart.ChartArea.Font.Size = 9
ActiveChart.PlotArea.Interior.ColorIndex = xlNone
ActiveChart.Axes(xlValue).TickLabels.Font.Bold = True
ActiveChart.Axes(xlCategory).TickLabels.Font.Bold = True
ActiveChart.Legend.Position = xlBottom
End Sub

You must activate a chart before executing this macro. Activate an embedded chart by clicking it. To activate a chart on a chart sheet, activate the chart sheet.


Bạn phải kích hoạt đồ thị trước khi thực hiện macro này. Kích hoạt biểu đồ được nhúng bằng việc nhấp chọn nó. Để kích hoạt một biểu đồ trên một bảng tính biểu đồ, phải kích hoạt bảng tính biểu đồ

To ensure that a chart is selected, you can add some error-handling code (see Chapter 12 for details about error handling). Here’s the modified macro, which displays a message if a chart is not selected

Để bảo đảm rằng một đồ thị được chọn, bạn có thể thêm vào một vài code bẫy lỗi. (xem chương 12 về các chi tiết cho việc bẫy lỗi). Đây là macro đã được bổ sung, macro này trình bày một thông báo nếu đồ thị không được chọn

Sub ChartMod2()
On Error GoTo ErrorHandler
ActiveChart.Type = xlArea
ActiveChart.ChartArea.Font.Name = “Arial”
ActiveChart.ChartArea.Font.FontStyle = “Regular” 
ActiveChart.ChartArea.Font.Size = 9
ActiveChart.PlotArea.Interior.ColorIndex = xlNone
ActiveChart.Axes(xlValue).TickLabels.Font.Bold = True
ActiveChart.Axes(xlCategory).TickLabels.Font.Bold = True
ActiveChart.Legend.Position = xlBottom
Exit Sub
ErrorHandler :
MsgBox “ Select a chart first”
End Sub

I created this macro by recording my actions as I formatted a chart. Then I cleaned up the recorder code by removing irrelevant lines


Tôi đã tạo macro này bằng việc ghi lại hoạt động của tôi khi tôi định dạng đồ thị. Sau đó, tôi sắp xếp lại code ghi lại bằng việc bỏ đi những dòng không thích hợp