Brian cần xử lý một trang tính trước khi nó có thể được giao cho người khác. Những gì anh ta cần là loại bỏ hầu hết, nhưng không phải tất cả, các công thức trong trang tính. Anh ta muốn xem qua tất cả các ô trong một dải ô đã chọn và nếu ô đó chứa công thức, hãy kiểm tra công thức đó. Nếu công thức chứa tham chiếu (bất kỳ tham chiếu nào) đến một trang tính khác trong sổ làm việc hiện tại, thì công thức sẽ bị bỏ qua. Nếu công thức không chứa tham chiếu như vậy, thì macro cần thay thế công thức bằng kết quả của công thức.

Đây là một nhiệm vụ tương đối đơn giản; tất cả những gì bạn cần làm là kiểm tra kỹ bước macro của bạn về các ô và tìm xem ô đó có chứa công thức hay không. Nếu có, hãy kiểm tra xem công thức có chứa dấu chấm than hay không. Dấu chấm than được sử dụng trong tham chiếu công thức, chẳng hạn như sau:

=Sheet2!A1

Vì vậy, nếu công thức chứa dấu chấm than, bạn có thể bỏ qua nó. Nếu nó không chứa dấu chấm than thì bạn có thể thay thế nó bằng giá trị của nó.

Sub ConvertFormulas1()

Dim c As Variant     Dim frm As String

On Error Resume Next

For Each c In Selection         If c.HasFormula Then             frm = c.Formula             If InStr(1, frm, "!") = 0 Then                 c.Value = c.Value             End If         End If     Next c End Sub

Có một nhược điểm đối với cách tiếp cận này: dấu chấm than sẽ xuất hiện trong tất cả các công thức bên ngoài trang tính hiện tại, bao gồm cả những công thức có trong các sổ làm việc khác. Nếu bạn thực sự muốn chỉ thay thế công thức cho các trang tính khác trong sổ làm việc hiện tại nhưng bỏ qua các công thức tham chiếu trang tính trên các sổ làm việc khác, thì bạn cần thêm một số logic bổ sung. Logic tự làm rõ khi bạn nhìn vào cách Excel tham chiếu các sổ làm việc khác đó:

=[OtherWorksheet.xls]Sheet1'!$C$9

Lưu ý rằng tên của sổ làm việc khác được chứa trong dấu ngoặc.

Do đó, sau khi kiểm tra dấu chấm than (thông báo cho bạn biết rằng tham chiếu đến một trang tính khác, bạn cần kiểm tra sự hiện diện của dấu ngoặc vuông bên trái. Nếu có, thì tham chiếu không nằm trong ô trong sổ làm việc hiện tại.

Sub ConvertFormulas2()

Dim c As Variant     Dim OtherSheet As Boolean     Dim frm As String

On Error Resume Next

For Each c In Selection         If c.HasFormula Then             frm = c.Formula             OtherSheet = False             If InStr(1, frm, "!") Then                 OtherSheet = True                 If InStr(1, frm, "[") Then                     OtherSheet = False                 End If             End If             If Not OtherSheet Then                 c.Value = c.Value             End If         End If     Next c End Sub

Cần lưu ý rằng sẽ tương đối dễ dàng để sửa đổi công thức được sử dụng trong macro này để nó loại bỏ tất cả các tham chiếu bên ngoài khi rời khỏi thực tế, tất cả những gì bạn cần làm là loại bỏ việc kiểm tra dấu ngoặc và sau đó loại bỏ từ khóa “Not” trong cấu trúc kiểm tra biến OtherSheet.

_Lưu ý: _

Nếu bạn muốn biết cách sử dụng macro được mô tả trên trang này (hoặc trên bất kỳ trang nào khác trên trang ExcelTips), tôi đã chuẩn bị một trang đặc biệt bao gồm thông tin hữu ích .

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (12158) áp dụng cho Microsoft Excel 2007, 2010, 2013 và 2016.

Bạn có thể tìm thấy phiên bản của mẹo này cho giao diện menu cũ hơn của Excel tại đây: