Lỗi Không khớp Loại là gì?
Lỗi không khớp thường có thể xảy ra khi bạn chạy mã VBA của mình. Lỗi này sẽ ngăn mã của bạn chạy hoàn toàn và gắn cờ bằng hộp thông báo rằng lỗi này cần được sắp xếp
Lưu ý rằng nếu bạn chưa kiểm tra đầy đủ mã của mình trước khi phân phối cho người dùng, thông báo lỗi này sẽ hiển thị cho người dùng và gây mất niềm tin lớn vào ứng dụng Excel của bạn. Thật không may, người dùng thường làm những điều rất đặc biệt đối với một ứng dụng và thường là những thứ mà bạn với tư cách là nhà phát triển không bao giờ tính đến.
Lỗi không khớp kiểu xảy ra do bạn đã xác định một biến bằng cách sử dụng câu lệnh Dim là một kiểu nhất định, ví dụ: số nguyên, ngày tháng và mã của bạn đang cố gắng gán giá trị cho biến không được chấp nhận, ví dụ: chuỗi văn bản được gán cho một biến số nguyên như trong ví dụ này:
Đây là một ví dụ:
Nhấp vào Gỡ lỗi và dòng mã vi phạm sẽ được đánh dấu bằng màu vàng. Không có tùy chọn nào trên cửa sổ bật lên lỗi để tiếp tục, vì đây là một lỗi lớn và không có cách nào để mã có thể chạy thêm.
Trong trường hợp cụ thể này, giải pháp là thay đổi câu lệnh Dim thành một kiểu biến hoạt động với giá trị mà bạn đang gán cho biến. Mã sẽ hoạt động nếu bạn thay đổi loại biến thành 'Chuỗi' và có thể bạn cũng muốn thay đổi tên biến.
Tuy nhiên, việc thay đổi loại biến sẽ cần thiết lập lại dự án của bạn và bạn sẽ phải chạy lại mã của mình ngay từ đầu, điều này có thể rất khó chịu nếu liên quan đến một quy trình dài.
Lỗi không khớp do tính toán trang tính gây ra
Ví dụ trên là một ví dụ rất đơn giản về cách tạo ra lỗi không khớp và trong trường hợp này, nó có thể dễ dàng khắc phục
Tuy nhiên, nguyên nhân của lỗi không khớp thường sâu hơn nhiều và không quá rõ ràng khi bạn đang cố gắng gỡ lỗi mã của mình.
Ví dụ: giả sử rằng bạn đã viết mã để nhận một giá trị ở một vị trí nhất định trên trang tính và nó chứa một phép tính phụ thuộc vào các ô khác trong sổ làm việc (B1 trong ví dụ này)
Trang tính trông giống như ví dụ này, với công thức để tìm một ký tự cụ thể trong một chuỗi văn bản
Theo quan điểm của người dùng, ô A1 là định dạng tự do và họ có thể nhập bất kỳ giá trị nào họ muốn. Tuy nhiên, công thức đang tìm kiếm sự xuất hiện của ký tự ‘B’ và trong trường hợp này, nó không được tìm thấy nên ô B1 có giá trị lỗi.
Mã kiểm tra bên dưới sẽ tạo ra lỗi không khớp vì một giá trị sai đã được nhập vào ô A1
| 1234 | Sub TestMismatch ()Làm mờ MyNumber dưới dạng số nguyênMyNumber = Sheets ("Sheet1"). Phạm vi ("B1"). Giá trịKết thúc Sub |
Giá trị trong ô B1 đã tạo ra lỗi vì người dùng đã nhập văn bản vào ô A1 không phù hợp với những gì mong đợi và nó không chứa ký tự ‘B’
Mã cố gắng gán giá trị cho biến ‘MyNumber’ đã được xác định để mong đợi một số nguyên và do đó bạn gặp lỗi không khớp.
Đây là một trong những ví dụ mà việc kiểm tra tỉ mỉ mã của bạn sẽ không cung cấp câu trả lời. Bạn cũng cần phải xem trên trang tính giá trị đến từ đâu để tìm hiểu lý do tại sao điều này lại xảy ra.
Vấn đề thực sự là trên trang tính và công thức trong B1 cần thay đổi để các giá trị lỗi được xử lý. Bạn có thể thực hiện việc này bằng cách sử dụng công thức 'IFERROR' để cung cấp giá trị mặc định là 0 nếu không tìm thấy ký tự tìm kiếm
Sau đó, bạn có thể kết hợp mã để kiểm tra giá trị 0 và hiển thị thông báo cảnh báo cho người dùng rằng giá trị trong ô A1 không hợp lệ
| 12345678 | Sub TestMismatch ()Làm mờ MyNumber dưới dạng số nguyênMyNumber = Sheets ("Sheet1"). Phạm vi ("B1"). Văn bảnNếu MyNumber = 0 ThìMsgBox "Giá trị tại ô A1 không hợp lệ", vbCriticalThoát SubKết thúc nếuKết thúc Sub |
Bạn cũng có thể sử dụng xác thực dữ liệu (nhóm Công cụ dữ liệu trên tab Dữ liệu của ruy-băng) trên bảng tính để ngăn người dùng làm bất cứ điều gì họ thích và gây ra lỗi trang tính ngay từ đầu. Chỉ cho phép họ nhập các giá trị sẽ không gây ra lỗi trang tính.
Bạn có thể viết mã VBA dựa trên sự kiện Thay đổi trong trang tính để kiểm tra những gì đã được nhập.
Đồng thời khóa và mật khẩu bảo vệ trang tính để không thể nhập dữ liệu không hợp lệ
Lỗi không khớp do giá trị ô đã nhập gây ra
Lỗi không khớp có thể được gây ra trong mã của bạn bằng cách đưa vào các giá trị bình thường từ một trang tính (không phải lỗi), nhưng trong đó người dùng đã nhập một giá trị không mong muốn, ví dụ: giá trị văn bản khi bạn mong đợi một số. Họ có thể đã quyết định chèn một hàng trong một dãy số để họ có thể ghi chú vào ô giải thích điều gì đó về số đó. Rốt cuộc, người dùng không biết mã của bạn hoạt động như thế nào và họ vừa ném toàn bộ mọi thứ ra ngoài bằng cách nhập ghi chú của họ.
Mã ví dụ bên dưới tạo một mảng đơn giản có tên là ‘MyNumber’ được xác định bằng các giá trị số nguyên
Sau đó, mã sẽ lặp qua một dải ô từ A1 đến A7, gán giá trị ô vào mảng, sử dụng một biến ‘Coun’ để lập chỉ mục từng giá trị
Khi mã đạt đến giá trị văn bản, lỗi không khớp là do điều này gây ra và mọi thứ sẽ tạm dừng
Bằng cách nhấp vào 'Gỡ lỗi' trong cửa sổ bật lên lỗi, bạn sẽ thấy dòng mã có sự cố được đánh dấu bằng màu vàng. Bằng cách di con trỏ qua bất kỳ phiên bản nào của biến ‘Coun’ trong mã, bạn sẽ có thể thấy giá trị của ‘Coun’ mà mã bị lỗi, trong trường hợp này là 5
Nhìn vào bảng tính, bạn sẽ thấy rằng 5NS ô xuống có giá trị văn bản và điều này đã khiến mã bị lỗi
Bạn có thể thay đổi mã của mình bằng cách đặt một điều kiện kiểm tra giá trị số trước khi thêm giá trị ô vào mảng
| 12345678910111213 | Sub TestMismatch ()Dim MyNumber (10) Dưới dạng số nguyên, Coun dưới dạng số nguyênCoun = 1LàmIf Coun = 11 then Exit DoIf IsNumeric (Sheets ("sheet1"). Cells (Coun, 1) .Value) ThìMyNumber (Coun) = Trang tính ("sheet1"). Các ô (Coun, 1). Giá trịKhácMyNumber (Coun) = 0Kết thúc nếuCoun = Coun + 1VòngKết thúc Sub |
Mã sử dụng hàm ‘IsNumeric’ để kiểm tra xem giá trị có thực sự là một số hay không và nếu là số thì nó sẽ nhập giá trị đó vào mảng. Nếu nó không phải là số thì nó nhập giá trị bằng không.
Điều này đảm bảo rằng chỉ số mảng được giữ phù hợp với số hàng ô trong bảng tính.
Bạn cũng có thể thêm mã sao chép giá trị lỗi ban đầu và chi tiết vị trí vào trang tính 'Lỗi' để người dùng có thể biết họ đã làm gì sai khi chạy mã của bạn.
Kiểm tra số sử dụng mã đầy đủ cho ô cũng như mã để gán giá trị vào mảng. Bạn có thể tranh luận rằng điều này nên được chỉ định cho một biến để không tiếp tục lặp lại cùng một đoạn mã, nhưng vấn đề là bạn cần phải xác định biến đó là 'Biến thể', đây không phải là điều tốt nhất nên làm.
Bạn cũng cần xác thực dữ liệu trên trang tính và mật khẩu bảo vệ trang tính. Điều này sẽ ngăn người dùng chèn hàng và nhập dữ liệu không mong muốn.
Lỗi không khớp do gọi hàm hoặc quy trình con sử dụng tham số gây ra
Khi một hàm được gọi, bạn thường truyền các tham số cho hàm bằng cách sử dụng các kiểu dữ liệu đã được định nghĩa bởi hàm. Hàm có thể là một hàm đã được xác định trong VBA, hoặc nó có thể là một hàm do Người dùng Xác định mà bạn đã tự xây dựng. Một quy trình phụ đôi khi cũng có thể yêu cầu các tham số
Nếu bạn không tuân theo các quy ước về cách các tham số được truyền vào hàm, bạn sẽ gặp lỗi không khớp
| 12345678 | Sub CallFunction ()Dim Ret As IntegerRet = MyFunction (3, "test")Kết thúc SubHàm MyFunction (N As Integer, T As String) As StringMyFunction = TChức năng kết thúc |
Ở đây có một số khả năng gặp phải lỗi không khớp
Biến trả về (Ret) được định nghĩa là một số nguyên, nhưng hàm trả về một chuỗi. Ngay sau khi bạn chạy mã, nó sẽ không thành công vì hàm trả về một chuỗi và điều này không thể chuyển thành một biến số nguyên. Điều thú vị là chạy Debug trên mã này không nhận được lỗi này.
Nếu bạn đặt dấu ngoặc kép xung quanh tham số đầu tiên được truyền (3), nó được hiểu là một chuỗi, không khớp với định nghĩa của tham số đầu tiên trong hàm (số nguyên)
Nếu bạn thực hiện tham số thứ hai trong lệnh gọi hàm thành một giá trị số, nó sẽ không khớp với giá trị không khớp vì tham số thứ hai trong chuỗi được định nghĩa là một chuỗi (văn bản)
Lỗi không khớp gây ra do sử dụng các chức năng chuyển đổi trong VBA không chính xác
Có một số hàm chuyển đổi mà bạn có thể sử dụng trong VBA để chuyển đổi giá trị sang các kiểu dữ liệu khác nhau. Một ví dụ là ‘CInt’ chuyển đổi một chuỗi chứa một số thành một giá trị số nguyên.
Nếu chuỗi được chuyển đổi chứa bất kỳ ký tự alpha nào thì bạn sẽ gặp lỗi không khớp, ngay cả khi phần đầu tiên của chuỗi chứa ký tự số và phần còn lại là ký tự alpha, ví dụ: '123abc'
Phòng ngừa chung của các lỗi không khớp
Chúng tôi đã thấy trong các ví dụ ở trên một số cách xử lý các lỗi không khớp tiềm ẩn trong mã của bạn, nhưng có một số cách khác, mặc dù chúng có thể không phải là lựa chọn tốt nhất:
Xác định các biến của bạn là Loại biến thể
Loại biến thể là loại biến mặc định trong VBA. Nếu bạn không sử dụng câu lệnh Dim cho một biến và chỉ bắt đầu sử dụng nó trong mã của mình, thì nó sẽ tự động được cung cấp loại Biến.
Biến Variant sẽ chấp nhận bất kỳ loại dữ liệu nào, cho dù đó là số nguyên, số nguyên dài, số chính xác kép, boolean hay giá trị văn bản. Điều này nghe có vẻ là một ý tưởng tuyệt vời, và bạn tự hỏi tại sao mọi người không chỉ đặt tất cả các biến của họ thành biến thể.
Tuy nhiên, kiểu dữ liệu biến thể có một số nhược điểm. Thứ nhất, nó chiếm nhiều bộ nhớ hơn so với các kiểu dữ liệu khác. Nếu bạn xác định một mảng rất lớn là biến thể, nó sẽ chiếm một lượng lớn bộ nhớ khi mã VBA đang chạy và có thể dễ dàng gây ra các vấn đề về hiệu suất
Thứ hai, nói chung nó có hiệu suất chậm hơn so với khi bạn đang sử dụng các kiểu dữ liệu cụ thể. Ví dụ: nếu bạn đang thực hiện các phép tính phức tạp bằng cách sử dụng số thập phân động, các phép tính sẽ chậm hơn đáng kể nếu bạn lưu trữ các số dưới dạng biến thể, thay vì số chính xác gấp đôi
Sử dụng kiểu biến thể được coi là lập trình cẩu thả, trừ khi có sự cần thiết tuyệt đối cho nó.
Sử dụng lệnh OnError để xử lý lỗi
Lệnh OnError có thể được bao gồm trong mã của bạn để đối phó với bẫy lỗi, để nếu có lỗi xảy ra, người dùng sẽ thấy một thông báo có ý nghĩa thay vì cửa sổ bật lên lỗi VBA tiêu chuẩn
| 1234567 | Sub ErrorTrap ()Làm mờ MyNumber dưới dạng số nguyênLỗi GoTo Err_HandlerMyNumber = "thử nghiệm"Err_Handler:MsgBox "Lỗi" & Err.Description & "đã xảy ra"Kết thúc Sub |
Điều này có hiệu quả ngăn chặn lỗi ngừng chạy mã của bạn trơn tru và cho phép người dùng khôi phục sạch sẽ khỏi tình huống lỗi.
Quy trình Err_Handler có thể hiển thị thêm thông tin về lỗi và người cần liên hệ về lỗi đó.
Từ quan điểm lập trình, khi bạn đang sử dụng một quy trình xử lý lỗi, bạn sẽ thấy khá khó khăn để xác định dòng mã có lỗi. Nếu bạn đang xem qua mã bằng F8, ngay sau khi dòng mã vi phạm được chạy, nó sẽ chuyển sang quy trình xử lý lỗi và bạn không thể kiểm tra xem nó đang sai ở đâu.
Một cách giải quyết vấn đề này là thiết lập một hằng số chung là Đúng hoặc Sai (Boolean) và sử dụng điều này để bật hoặc tắt quy trình xử lý lỗi bằng cách sử dụng câu lệnh ‘If’. Khi bạn muốn kiểm tra lỗi, tất cả những gì bạn phải làm là đặt hằng số chung thành False và trình xử lý lỗi sẽ không hoạt động nữa.
| 1 | Global Const ErrHandling = Sai |
| 1234567 | Sub ErrorTrap ()Làm mờ MyNumber dưới dạng số nguyênIf ErrHandling = True Then On Error GoTo Err_HandlerMyNumber = "thử nghiệm"Err_Handler:MsgBox "Lỗi" & Err.Description & "đã xảy ra"Kết thúc Sub |
Một vấn đề với điều này là nó cho phép người dùng khôi phục sau lỗi, nhưng phần còn lại của mã trong quy trình phụ không được chạy, điều này có thể gây ra hậu quả rất lớn sau này trong ứng dụng.
Sử dụng ví dụ trước đó về lặp qua một dải ô, mã sẽ đến ô A5 và gặp lỗi không khớp. Người dùng sẽ thấy một hộp thông báo cung cấp thông tin về lỗi, nhưng không có gì từ ô đó trở đi trong phạm vi sẽ được xử lý.
Sử dụng lệnh OnError để khắc phục lỗi
Thao tác này sử dụng lệnh 'On Error Resume Next'. Điều này rất nguy hiểm khi đưa vào mã của bạn vì nó ngăn chặn bất kỳ lỗi tiếp theo nào được hiển thị. Về cơ bản, điều này có nghĩa là khi mã của bạn đang thực thi, nếu lỗi xảy ra trong một dòng mã, việc thực thi sẽ chỉ chuyển sang dòng có sẵn tiếp theo mà không thực hiện dòng lỗi và tiếp tục như bình thường.
Điều này có thể giải quyết tình huống lỗi tiềm ẩn, nhưng nó vẫn sẽ ảnh hưởng đến mọi lỗi trong tương lai trong mã. Khi đó, bạn có thể nghĩ rằng mã của mình không có lỗi, nhưng thực tế không phải vậy và các phần của mã của bạn không hoạt động như những gì bạn nghĩ nó phải làm.
Có những tình huống cần sử dụng lệnh này, chẳng hạn như nếu bạn đang xóa tệp bằng lệnh 'Kill' (nếu tệp không có mặt, sẽ có lỗi), nhưng bẫy lỗi phải luôn được chuyển trở lại vào ngay sau nơi có thể xảy ra lỗi bằng cách sử dụng:
| 1 | On Error Goto 0 |
Trong ví dụ trước đó về lặp qua một phạm vi ô, sử dụng 'Khi tiếp tục lỗi tiếp theo', điều này sẽ cho phép vòng lặp tiếp tục, nhưng ô gây ra lỗi sẽ không được chuyển vào mảng và phần tử mảng cho chỉ mục cụ thể đó sẽ giữ một giá trị null.
Chuyển đổi dữ liệu sang kiểu dữ liệu để khớp với khai báo
Bạn có thể sử dụng các hàm VBA để thay đổi kiểu dữ liệu của dữ liệu đến để nó khớp với kiểu dữ liệu của biến nhận.
Bạn có thể làm điều này khi chuyển các tham số cho các hàm. Ví dụ: nếu bạn có một số được giữ trong một biến chuỗi và bạn muốn chuyển nó dưới dạng một số cho một hàm, bạn có thể sử dụng CInt
Có thể sử dụng một số hàm chuyển đổi này, nhưng đây là những hàm chính:
CInt - chuyển đổi một chuỗi có giá trị số (dưới + hoặc - 32,768) thành giá trị số nguyên. Hãy lưu ý rằng điều này sẽ cắt bớt bất kỳ dấu thập phân nào
CLng - Chuyển đổi một chuỗi có giá trị số lớn thành một số nguyên dài. Dấu thập phân bị cắt bớt.
CDbl - Chuyển đổi một chuỗi chứa một số thập phân động thành một số chính xác kép. Bao gồm các dấu thập phân
CDate - Chuyển đổi một chuỗi chứa một ngày thành một biến ngày. Phụ thuộc một phần vào cài đặt trong Bảng điều khiển Windows và ngôn ngữ của bạn về cách ngày được diễn giải
CStr - Chuyển đổi một giá trị số hoặc ngày thành một chuỗi
Khi chuyển đổi từ một chuỗi thành một số hoặc một ngày, chuỗi không được chứa bất kỳ thứ gì khác với số hoặc ngày. Nếu có các ký tự alpha, điều này sẽ tạo ra lỗi không khớp. Đây là một ví dụ sẽ tạo ra lỗi không khớp:
| 123 | Kiểm tra phụ ()MsgBox CInt ("123abc")Kết thúc Sub |
Kiểm tra các biến trong mã của bạn
Bạn có thể kiểm tra một biến để tìm ra kiểu dữ liệu đó trước khi bạn gán nó cho một biến thuộc một kiểu cụ thể.
Ví dụ: bạn có thể kiểm tra một chuỗi để xem nó có phải là số hay không bằng cách sử dụng hàm 'IsNumeric' trong VBA
| 1 | MsgBox IsNumeric ("123test") |
Đoạn mã này sẽ trả về False vì mặc dù chuỗi bắt đầu bằng các ký tự số nhưng nó cũng chứa văn bản nên nó không thành công trong quá trình kiểm tra.
| 1 | MsgBox IsNumeric ("123") |
Mã này sẽ trả về True vì nó là tất cả các ký tự số
Có một số hàm trong VBA để kiểm tra các kiểu dữ liệu khác nhau, nhưng đây là những hàm chính:
IsNumeric - kiểm tra xem một biểu thức có phải là một số hay không
IsDate - kiểm tra xem một biểu thức có phải là ngày hay không
IsNull - kiểm tra xem một biểu thức có rỗng hay không. Giá trị null chỉ có thể được đưa vào một đối tượng biến thể, nếu không bạn sẽ gặp lỗi ‘Sử dụng giá trị không hợp lệ’. Hộp thông báo trả về giá trị null nếu bạn đang sử dụng nó để đặt câu hỏi, vì vậy biến trả về phải là một biến thể. Hãy nhớ rằng bất kỳ phép tính nào sử dụng giá trị null sẽ luôn trả về kết quả là null.
IsArray - kiểm tra xem biểu thức có đại diện cho một mảng hay không
IsEmpty - kiểm tra xem biểu thức có trống hay không. Lưu ý rằng rỗng không giống với null. Một biến trống khi nó được xác định lần đầu tiên nhưng nó không phải là giá trị rỗng
Đáng ngạc nhiên là không có chức năng nào cho IsText hoặc IsString, điều này sẽ thực sự hữu ích
Đối tượng và lỗi không khớp
Nếu bạn đang sử dụng các đối tượng như một dải ô hoặc một trang tính, bạn sẽ gặp lỗi không khớp tại thời điểm biên dịch, không phải tại thời điểm chạy, điều này cung cấp cho bạn cảnh báo thích hợp rằng mã của bạn sẽ không hoạt động
| 123456 | Sub TestRange ()Dim MyRange As Range, I As LongĐặt MyRange = Range ("A1: A2")I = 10x = UseMyRange (I)Kết thúc Sub |
| 12 | Hàm UseMyRange (R As Range)Chức năng kết thúc |
Mã này có một chức năng được gọi là ‘UseMyRange’ và một tham số được truyền dưới dạng một đối tượng phạm vi. Tuy nhiên, tham số được truyền qua là Số nguyên dài không khớp với kiểu dữ liệu.
Khi bạn chạy mã VBA, mã này ngay lập tức được biên dịch và bạn sẽ thấy thông báo lỗi sau:
Thông số vi phạm sẽ được đánh dấu bằng nền màu xanh lam
Nói chung, nếu bạn mắc lỗi trong mã VBA bằng cách sử dụng các đối tượng, bạn sẽ thấy thông báo lỗi này, thay vì thông báo loại không khớp:
