Excel VBA: ListRows.Add không thích Tên bảng dưới dạng biến?

Tôi có mã VBA trong một dự án Excel như được hiển thị bên dưới (Tôi chỉ hiển thị các bit có liên quan và có thể bỏ qua các dòng in nghiêng). Phiên bản A thành công nhưng phiên bản B thất bại với lỗi "Thêm phương pháp của ListRows".

Liệu điều này có ý nghĩa với ai? Hay tôi đã phạm một sai lầm rõ ràng đối với những người khác? Sẽ không phải là lần đầu tiên.....

Phiên bản A sử dụng chữ thay vì các giá trị được giữ trong strTable& strColName

Riêng tư Sub New_Item(strTable dưới dạng chuỗi, strItem dưới dạng chuỗi, strColName dưới dạng chuỗi)

Làm mờ ThisCol dưới dạng số nguyên

Dim NextRow dưới dạng số nguyên

Làm mờ TopRow dưới dạng số nguyên

Làm mờ Tbl dưới dạng ListObject

Dim NewRow dưới dạng ListRow

Dim SortRange dưới dạng chuỗi

Worksheets("Factors").Activate

Đặt Tbl = ActiveSheet.ListObjects("tblNames")

Tbl.ListColumns("Tên").Range.Select

Đặt NewRow = Tbl.ListRows.Add(alwaysinsert:=True) 'Thêm hàng mới

Phiên bản B sử dụng các giá trị tham số

Riêng tư Sub New_Item(strTable dưới dạng chuỗi, strItem dưới dạng chuỗi, strColName dưới dạng chuỗi)

Làm mờ ThisCol dưới dạng số nguyên

Dim NextRow dưới dạng số nguyên

Làm mờ TopRow dưới dạng số nguyên

Làm mờ Tbl dưới dạng ListObject

Dim NewRow dưới dạng ListRow

Worksheets("Factors").Activate

Đặt Tbl = ActiveSheet.ListObjects(strTable)

Tbl.ListColumns(strColName).Range.Select

Đặt NewRow = Tbl.ListRows.Add(alwaysinsert:=True)

Sub gọi có cái này:

Nếu NewItem Thì Gọi New_Item("tblNames", ComboBox1.Value, "Firstname")

Trả lời:

Trước hết, không cần sử dụng phương thức Kích hoạt trong VBA, cũng như Chọn. Trong khi bạn có thể, nó chắc chắn là không cần thiết.

Tôi có thể đoán ActiveSheet, trong thời gian chạy, không phải là cái chứa Bảng theo tên biến của bạn sao? Nếu đúng như vậy, có thể là không thể chèn một hàng (không thể sử dụng hàng nào) hoặc trang tính đã được bảo vệ. Mã của bạn, như đã đăng, phù hợp với tôi.

Sub riêng tư New_Item( _
ByVal strTable dưới dạng chuỗi, _
ByVal strItem dưới dạng Chuỗi, _
ByVal strColName dưới dạng Chuỗi _
)

Làm mờ ThisCol dưới dạng số nguyên
Dim NextRow dưới dạng số nguyên
Làm mờ TopRow dưới dạng số nguyên
Làm mờ Tbl dưới dạng ListObject
Dim NewRow dưới dạng ListRow

Đặt Tbl = Worksheets("Factors").ListObjects(strTable)
Đặt NewRow = Tbl.ListRows.Add(AlwaysInsert:=True)

Sẽ khó chẩn đoán nếu không có thêm thông tin, nhưng dự đoán tốt nhất của tôi là Excel không thể chèn một hàng. Có lẽ có thứ gì đó ngăn Bảng mở rộng, rất có thể là dữ liệu bên dưới Bảng hoặc khu vực hợp nhất vượt qua giới hạn của Bảng [cột].

Có một số vấn đề với Bảng có thể xảy ra lỗi khi chèn (các) hàng. Trên hết, chức năng gốc chỉ cho phép chèn một hàng và nó luôn ở cuối. Điều này khiến tôi viết hàm của riêng mình để chèn hàng, nhưng cũng vì tôi muốn thực hiện nhiều hơn một hàng cũng như chỉ định vị trí. Đây là những gì tôi sử dụng.

Hàm InsertRows( _
Bảng ByVal dưới dạng ListObject, _
Vị trí ByVal tùy chọn miễn là dài, _
ByVal tùy chọn RowCount As Long = 1, _
Tùy chọn ByVal MessageOnError As Boolean = False _
) miễn là dài
'
' Chèn một số hàng trống đã chỉ định vào Bảng ở một vị trí nhất định.
'
Dim InsertRange As Range
Tiêu đề mờHiển thị dưới dạng Boolean

Nếu bảng không có gì thì thoát chức năng
Nếu RowCount < 1 thì thoát chức năng
Nếu Vị trí < 0 Thì Thoát Chức năng
HeadersVisible = Table.ShowHeaders

' Hiển thị tiêu đề
Khi gặp lỗi Tiếp tục Tiếp theo
Nếu HeadersVisible = False Thì Table.ShowHeaders = True
Khi Lỗi GoTo 0
Nếu Table.ShowHeaders = Sai Và HeadersVisible = Sai Thì
If MessageOnError Then MsgBox "Đã xảy ra sự cố. Trang tính Bảng có thể được bảo vệ hoặc Bảng không thể dịch chuyển được.", vbExclamation, "Rất tiếc!"
thoát chức năng
kết thúc nếu

' Chăm sóc một hàng chèn đơn giản, đơn giản
Nếu (RowCount = 1 And Position = 0) Hoặc Table.DataBodyRange không có gì thì
Table.ListRows.Add
Nếu RowCount = 1 Và Vị trí = 0 Thì Thoát Chức năng
kết thúc nếu

' Bình thường hóa vị trí hàng chèn
Nếu Vị trí = 0 Thì Vị trí = Table.ListRows.Count + 1

' Tìm phạm vi để chèn hàng
Khi gặp lỗi Tiếp tục Tiếp theo
Nếu Table.DataBodyRange không có gì thì
Đặt InsertRange = Table.InsertRowRange.Resize(RowCount)
Khác
Đặt InsertRange = Table.DataBodyRange.Resize(RowCount).Offset(Position - 1)
kết thúc nếu
Khi Lỗi GoTo 0
Nếu InsertRange không có gì thì
If MessageOnError Then MsgBox "Đã xảy ra sự cố. Không thể tìm thấy vị trí.", vbExclamation, "Rất tiếc!"
thoát chức năng
kết thúc nếu

' Thực hiện thao tác chèn
Khi gặp lỗi Tiếp tục Tiếp theo
Nếu Intersect(InsertRange.EntireRow, Table.Range) không có gì thì
Table.Resize Table.Parent.Range(Table.Range.Address).Resize(Table.Range.Rows.Count + RowCount, Table.ListColumns.Count)
Khác
InsertRange.Insert Shift:=xlDown
kết thúc nếu
Khi Lỗi GoTo 0
Nếu InsertRange không có gì thì
If MessageOnError Then MsgBox "Đã xảy ra sự cố. Không thể chèn hàng.", vbExclamation, "Rất tiếc!"
thoát chức năng
kết thúc nếu

' Đặt khả năng hiển thị của tiêu đề thành những gì nó đã được
Nếu Không HeadersVisible Thì Table.ShowHeaders = HeadersVisible

Chèn hàng = 1

chức năng kết thúc

Trả lời:

Nhưng tại sao hai đoạn mã không thực hiện chính xác cùng một việc? Đó là một câu hỏi về cú pháp trong VBA. Ví dụ mã đầu tiên sử dụng chữ sẽ thực hiện chính xác điều tương tự như ví dụ thứ hai sử dụng các giá trị tham số.

alancsears - Bạn đã sao chép mã từ sổ làm việc của mình và dán vào đây hay bạn đã nhập lại? Ngoài ra, bạn đã sửa đổi những gì bạn đã đăng theo bất kỳ cách nào?

Trả lời:

Gửi Zack & D0gknees

Trả lời:
Tôi không thể tái tạo sự cố và bạn đã đăng mã của mình dưới dạng ảnh chứ không phải văn bản, vì vậy tôi không thể sao chép mã đó để kiểm tra. Bạn có thể tải tệp thử nghiệm của mình lên đám mây ở đâu đó để chúng tôi có thể tải xuống không? Tôi làm việc với các bảng khá nhiều (thậm chí đã viết một cuốn sách về nó), vì vậy tôi thực sự quan tâm đến việc tìm ra vấn đề.

Trả lời:

Zack, tôi sẽ tải lên tệp như bạn đề xuất, khi tôi quay lại PC của mình. Trong thời gian chờ đợi, vì tôi là người mới sử dụng các bảng Excel và tôi đã không đụng đến VBA kể từ khi tôi nghỉ hưu hơn mười lăm năm trước, nên việc mua sách của bạn có vẻ hợp lý, vì vậy tôi đã mua.

Chúc mừng

alan

Trả lời:
Rất tuyệt! Chúng tôi cũng luôn tìm kiếm phản hồi. Tôi sẽ mong chờ liên kết [đã tải lên] của bạn.

Trả lời:

Zack

Đây là liên kết.

https://1drv.ms/f/s!AkH-gORCnL-QgchYTYgIPSkUC3mSWA

Tôi đã chạy chương trình này sáng nay và nó đã thành công. Sau đó, tôi chạy lại nó sau khi đặt điểm dừng tại dòng Listrows.Add trong New_Item2(). Nó không thành công ở dòng đó ("Thêm phương thức ListRows không thành công") và sau đó Excel bị đóng. Tôi đã không thay đổi gì khác.

Nhân tiện, sau đó tôi đã thực hiện các thay đổi, nhưng đây chỉ là phần bổ sung của một vài dòng nhận xét.

Chắc là do tôi đã mắc phải một sai lầm ngu ngốc nào đó, bởi vì tôi đã viết một ứng dụng ngắn khác làm cùng một việc và lần nào nó cũng hoạt động.

Cảm ơn sự kiên nhẫn của bạn.

Trân trọng

alan

Trả lời:

Tôi không thể tạo lại lỗi của bạn. Điều đó đang được nói, có một số điều tôi sẽ thay đổi, mà tôi sẽ liệt kê dưới đây.

Thay đổi thói quen cmdQuit_Click của bạn

Thay vì sử dụng

Kết thúc

Thay vào đó, bạn nên sử dụng

Gỡ bỏ tôi

Bạn không bao giờ nên sử dụng từ khóa Kết thúc. Nó tạm dừng tất cả mã, bao gồm mọi đối tượng/thuộc tính được khởi tạo trong bộ nhớ. Nói chung, điều khoản hoạt động là

Thoát phụ

Nhưng trong trường hợp này, bạn muốn đóng biểu mẫu, không tự ý kết thúc tất cả quá trình thực thi mã.

Biết được điều này, tôi sẽ thay đổi mã biểu mẫu người dùng của bạn thành một cái gì đó như thế này

Phụ riêng tư cmdOK_Click()

Bảng mờ dưới dạng ListObject
Tên mờTồn tại dưới dạng Boolean

Mờ Thành Công Lâu Rồi

Khi gặp lỗi Tiếp tục Tiếp theo
Đặt Bảng = Sheet1.ListObjects("tblNames")
Khi Lỗi GoTo 0

Nếu không phải Table.DataBodyRange không có gì thì NameExists = ExistingItem(Table.DataBodyRange, Me.ComboBox1.Value)

Nếu Không Có Tên Thì Thành Công = AddTableItem(Table, "Firstname", ComboBox1.Value, , True)
Debug.Print thành công

Tôi.ComboBox1.Value = ""

kết thúc phụ


Mục hiện có của chức năng ( _
ByVal SearchRange Là phạm vi, _
ByVal SearchString dưới dạng chuỗi _
) dưới dạng Boolean

Khi gặp lỗi Tiếp tục Tiếp theo
ExistingItem = Not SearchRange.Find(What:=SearchString) Is nothing
Khi Lỗi GoTo 0

chức năng kết thúc


Hàm AddTableItem( _
Bảng ByVal dưới dạng ListObject, _
Tên cột ByVal dưới dạng chuỗi, _
ByVal ItemValue Dưới dạng Chuỗi, _
Vị trí ByVal tùy chọn Miễn là dài = 0, _
Bảng sắp xếp ByVal tùy chọn dưới dạng Boolean = False, _
Tùy chọn ByVal UseExistingSort As Boolean = True _
) miễn là dài

Dim InsertRow As Range
Dim NewSắp xếp dưới dạng Boolean
Dim ColumnPosition As Long

Khi gặp lỗi Tiếp tục Tiếp theo
ColumnPosition = Table.ListColumns(ColumnName).Index
Khi có lỗi GoTo AddTableItem_Error

Nếu ColumnPosition = 0 Thì Thoát Chức năng
ColumnPosition = WorksheetFunction.Min(ColumnPosition, Table.ListColumns.Count)
Vị trí = WorksheetFunction.Max(1, WorksheetFunction.Min(Position, Table.ListRows.Count + 1))

Nếu Table.DataBodyRange không có gì thì hãy đặt InsertRow = Table.InsertRowRange
Nếu không phải Table.DataBodyRange không có gì thì hãy đặt InsertRow = Table.ListRows.Add(Position:=Position).Range

InsertRow(1, ColumnPosition).Value = ItemValue

Nếu SortTable thì
Nếu Table.Sort.SortFields.Count > 0 và UseExistingSort thì
Bảng.Sắp xếp.Áp dụng
Khác
Table.Sort.SortFields.Add Table.ListColumns(ColumnPosition).Range(1, 1), xlSortOnValues, xlAscending
Bảng.Sắp xếp.Áp dụng
kết thúc nếu
kết thúc nếu

ThêmTableItem = 1
thoát chức năng

AddTableItem_Error:

chức năng kết thúc

Có một vài mục cần lưu ý ở đây. Có một sự thay đổi logic trong cách bạn kiểm tra xem một giá trị có tồn tại trong một phạm vi hay không. Thay vì xem nó là 'là duy nhất', phần trên xem xét mặt trái của 'đang tồn tại'. Nó cũng linh hoạt hơn ở chỗ phạm vi cần tìm không phải là một tham số, giúp nó có thể sử dụng được trong các ứng dụng khác (trong trường hợp này là trong biểu mẫu người dùng, nhưng nó cũng có thể được đặt trong một mô-đun tiêu chuẩn và được sử dụng ở nơi khác trong dự án ).

Tôi thấy bạn muốn áp dụng cách sắp xếp cho bảng của mình sau khi thêm giá trị. Vì các bảng giữ lại [trường] sắp xếp của chúng, nên không có lý do gì để tiếp tục thêm trường sắp xếp mới, chỉ cần áp dụng cách sắp xếp ở đó. Đây là cách hai tham số [tùy chọn] cuối cùng xuất hiện. Thực sự chúng tôi chỉ cần biết liệu bạn có muốn sắp xếp dữ liệu của mình hay không và nếu có, bạn có muốn sử dụng (các) trường/thứ tự sắp xếp hiện có hay không.

'AddTableItem' ở trên được biểu diễn dưới dạng hàm trái ngược với phụ. Tôi đã làm điều này để trả về một kết quả. Bằng cách này, bạn có thể diễn giải kết quả của phương thức được gọi. Ngay bây giờ, nó chỉ in ra cửa sổ Ngay lập tức, nhưng nó minh họa việc trả về một giá trị từ một lệnh gọi phương thức phức tạp hơn. Bạn có thể thao tác nó theo cách bạn thấy phù hợp bên trong hàm được gọi.

Điều cuối cùng tôi muốn đề cập là bạn không thấy bất kỳ cuộc gọi Chọn hoặc Kích hoạt nào trong mã của tôi. Họ không cần thiết. Bây giờ, nếu bạn muốn kích hoạt hoặc chọn thứ gì đó để người dùng xem, đó là một chuyện, nhưng chắc chắn mã đó không cần thiết để chạy. Trên thực tế, làm như vậy sẽ làm chậm quá trình thực thi mã của bạn. Do đó, thông thường bạn không nên sử dụng các hành động này.

Tôi không thể lấy mã này để báo lỗi cho tôi. Bạn có thể thử mã này và xem liệu bạn có thể tạo lại lỗi không?

Trả lời:

Rất cám ơn vì công việc bạn đã bỏ ra, Zack.

Tôi đã nhập mã của bạn và kiểm tra nhanh – không có vấn đề gì. Tôi sẽ dành một chút thời gian để tìm hiểu chi tiết.

Bây giờ tôi nghĩ lý do bạn không thể khiến đoạn mã vụng về của tôi bị lỗi là do lỗi rõ ràng trong đó phát sinh từ bên ngoài mã. Kể từ khi gửi cho bạn liên kết, tôi đã viết một ứng dụng Excel khác, tương tự, kết hợp các quy trình phổ biến từ ứng dụng này. Tôi vui vẻ sử dụng nó trong vài ngày cho đến ngày hôm qua, khi nó không thành công với phương thức Thêm đó. Giờ đây, khi một chương trình được chạy hai mươi hoặc ba mươi lần mà không gặp sự cố và sau đó bị lỗi mà không có bất kỳ thay đổi nào đối với mã, tôi sẽ cho rằng vấn đề là do dữ liệu hoặc do một khu vực "bên ngoài": chính Excel (được cài đặt trên PC của tôi ), Windows hoặc PC. Vì sau đó nó đã chạy một vài lần mà không gặp sự cố, tôi cho rằng vấn đề không phải ở dữ liệu.

Do đó, vấn đề ban đầu là một con cá trích đỏ, nhưng tôi đã học được rất nhiều điều từ bạn thông qua điều này. Về các phương pháp Kích hoạt và Chọn, tôi đã sử dụng các phương pháp này sau khi làm theo mã mà tôi tìm thấy trên các diễn đàn trực tuyến. Tôi đã ngừng sử dụng Chọn theo lời khuyên của bạn, nhưng tôi nhận thấy rằng nếu không có Kích hoạt, một quy trình đã ghi sai Trang tính. Tuy nhiên, bây giờ tôi có thể thấy rằng nó không cần thiết.

Tất cả bắt đầu khi tôi được yêu cầu đảm nhận công việc kế toán cho một câu lạc bộ xã hội địa phương. Thủ quỹ trước đây đã sử dụng một hệ thống trên giấy mà tôi thấy khó sử dụng, vì vậy tôi đã tạo một ứng dụng Excel hoạt động rất thành công, đặc biệt là sau khi tôi mua Kỹ năng Chuyên gia Excel 2016 của Mike Smart và mở rộng kiến ​​thức về các công thức của mình . Tuy nhiên, tôi muốn làm cho kế toán viên trong tương lai, phi kỹ thuật, sử dụng dễ dàng hơn. Câu lạc bộ này có hai thành viên khác, trong số khoảng chín mươi người, sử dụng PC - để gửi email, và thậm chí sau đó một trong số họ dường như cảm thấy PC có thể đánh cắp linh hồn của mình. Cả hai đều sử dụng phiên bản Office thời kỳ đen tối. Đây là nước Anh.

Tôi cũng muốn bảo vệ dữ liệu khỏi lỗi bằng cách giới thiệu VBA và Biểu mẫu người dùng để xác thực và kiểm soát đầu vào. Tôi đã không nhận ra bao nhiêu Visual Basic đã phát triển và bao nhiêu tôi đã quên trong mười bảy năm qua. Như mã của tôi cho thấy….

Tôi đã có VBA và Macro Excel 2016 của Bill Jelen, giúp tôi bắt đầu với VBA và giới thiệu sách của bạn. Cuốn sách của Mike Smart đã thúc đẩy tôi đánh giá cao hơn về Bảng Excel và, như bạn biết đấy, cuối cùng tôi đã quyết định mua cuốn sách của bạn.

Dự án sổ sách kế toán của tôi sẽ mất một thời gian để hoàn thành, nhưng tôi đang rất vui với VBA trong thời gian chờ đợi. Giá như có nhiều giờ hơn trong ngày….

Cảm ơn lần nữa, Zack. Tôi sẽ cho bạn biết làm thế nào tôi tiếp tục.

Chúc mừng

alan

Trả lời:

Tôi hiểu rất rõ câu chuyện đó, tất cả đã quá quen thuộc với bản thân tôi. Tôi bắt đầu mày mò với Excel vì tôi nghĩ nó rất tuyệt, rồi một chút chỗ này, một chút chỗ kia, chẳng mấy chốc tôi sẽ sử dụng nó cho mọi thứ. :)

Tôi nhớ đã đọc cuốn sách của Bill về macro cách đây vài năm (tôi nghĩ là năm 2007?), và tôi thực sự thích nó. Thật khó để tìm các nguồn tốt để học VBA (do đó, tôi hy vọng sẽ phát triển một số khóa học về nó). Làm những gì bạn đang làm, học thông qua ứng dụng, là một trong những phương pháp tốt nhất. Mặc dù việc học qua các diễn đàn/blog có thể chậm - mọi người đều tuyên bố mình là chuyên gia, nhưng bạn sẽ bắt đầu thấy những người giống nhau xung quanh hoặc chỉ vào cùng một tài nguyên. Đó là một thế giới nhỏ. ;)

Một điều bạn nói thu hút sự chú ý của tôi.

"Bây giờ khi một chương trình được chạy hai mươi hoặc ba mươi lần mà không gặp sự cố và sau đó bị lỗi mà không có bất kỳ thay đổi nào được thực hiện đối với mã"

Điều này có nghĩa là bạn đang sử dụng phương thức ListRows.Add nhiều lần, chẳng hạn như trong một phép lặp, vòng lặp hoặc đệ quy khác? Nếu đây là trường hợp, nó có thể gây ra mối quan tâm. Mặc dù tôi không biết chắc chắn, nhưng tôi khá chắc chắn rằng có sự rò rỉ bộ nhớ trong Excel bằng phương pháp này. Lặp lại hàng trăm lần, thêm một listrow trên mỗi lần lặp, nó sẽ ngày càng chậm hơn, chậm hơn và chậm hơn nữa, cho đến khi bạn muốn đấm vào máy tính của mình. Đây là lý do tại sao tốt hơn là làm tất cả cùng một lúc, đó là lý do tôi nghĩ ra quy trình InsertRows mà tôi đã đăng ở trên.

Nếu có bất cứ điều gì khác tôi có thể làm, xin vui lòng cho tôi biết, tôi rất sẵn lòng giúp đỡ. Tôi yêu Excel và tôi yêu Bảng.