Excel VBA ListRows.Add Fails

Я, вероятно, упускаю что-то простое, но ListRows.Add дает мне горе. Вот функция:

Sub addEmployee(employeeName As String, tableToAddTo As ListObject)
   Dim newRow As ListRow

   Set newRow = tableToAddTo.ListRows.Add()
   newRow.Range.Cells(1, 1).Value = employeeName
   tableToAddTo.Sort.Apply
End Sub

В большинстве случаев это работает нормально. Однако всякий раз, когда функция запускается в определенной таблице в моей рабочей таблице, строки, следующие за вызовом ListRows.Add, никогда не выполняются (по крайней мере, так указывает отладчик), и строка не добавляется в таблицу. Есть мысли / идеи?

ОБНОВИТЬ:

Вот что я узнал с момента публикации. Если я подпитываю Sub с данными заглушки, это работает отлично. Например:

Sub driver() 
    Dim myTable As ListObject 
    Set myTable = getTableObject("myTableName") 
    Call addEmployee("myName", myTable)
End Sub

Примечание: getTableObject циклически перебирает рабочие листы и возвращает ListObject с соответствующим именем.

Кажется, это проблема, связанная с контекстом, в котором вызывается код. В случае неудачи формула (вызов функции) помещается в различные ячейки различных таблиц. Формула содержит ссылки на данные в других ячейках. Когда данные в других ячейках изменяются, вызывается формула, которая, в свою очередь, вызывает подпрограмму addEmployee, приведенную выше. Это тот случай, который терпит неудачу.

Источник

7 ответов

Решение

Похоже, что то, что я пытался сделать, невозможно.

При разработке пользовательских функций важно понимать ключевое различие между функциями, которые вы вызываете, от других процедур VBA и функций, которые вы используете в формулах рабочего листа. Процедуры функций, используемые в формулах рабочего листа, должны быть пассивными. Например, код в процедуре Function не может манипулировать диапазонами или что-либо менять на рабочем листе. Пример может помочь прояснить это.

У вас может возникнуть желание написать собственную функцию рабочего листа, которая изменяет форматирование ячейки. Например, может быть полезно иметь формулу, которая использует пользовательскую функцию для изменения цвета текста в ячейке на основе значения ячейки. Как ни старайся, такую ​​функцию написать невозможно. Независимо от того, что вы делаете, функция не изменит лист. Помните, функция просто возвращает значение. Он не может выполнять действия с объектами.

Тем не менее, я должен указать на одно заметное исключение. Изменить текст в комментарии к ячейке можно с помощью пользовательской функции VBA.

Walkenbach, J. (2007). Microsoft Office Excel 2007 Power Программирование с VBA. John Wiley & Sons, стр. 280.

Источник

Извините за несоответствие этому вопросу, но это единственное сообщение Stackru, которое появляется в Google при поиске аналогичного сбоя по другой причине. Крушение на ListRows.Add долгое время мучил меня, поэтому поделился вопросом, который вызвал его для меня.

На листе есть таблица (скажем, MySourceTable). В моей пользовательской форме RowSource ComboBox является столбцом из этой таблицы (MySourceTable[ColName]). При нажатии другой кнопки на той же пользовательской форме я вставлял новую строку, используя ListRows.Add в эту таблицу и вылетает Excel. Если этот сценарий похож на ваш, читайте дальше.

Сбой вызван ComboBox, который все еще прикреплен к таблице и не может обработать вставку строк (имейте в виду, что он все еще создается, потому что я все еще на UserForm при вставке новой строки). После большого количества боли следующие строки выше и ниже вставки решили это навсегда:

    comboSetSelect.RowSource = ""
    Set newRow = myTable.ListRows.Add
    comboSetSelect.RowSource = "MySourceTable[ColName]" 'col set in properties

Это отсоединяет ComboBox от таблицы перед вставкой новой строки, а после завершения вставки обновляет ComboBox снова. Кроме того, вновь добавленное значение строки (если оно обновляется сразу после вставки) также отображается в ComboBox.

Источник

Я понимаю, что это действительно старый вопрос, но с ним я сталкивался много раз, пытаясь ответить за себя. Я до сих пор не понимаю специфику, почему это происходит, но вот мой обходной путь.

Sub AddRow()
     Dim Tbl as ListObject
     Dim MyColumn as Integer

     Tbl.ListRows(1).Range.EntireRow.Insert
     Tbl.ListColumns(MyColumn).DataBodyRange.Cells(1,1).Value = "My Value"
End Sub

Добавляя новую строку над первой существующей записью, таблица автоматически корректируется вниз, как и предполагалось, и тогда я могу начать записывать свои значения в таблицу.

Опять же, извините за возрождение старого поста, но я не смог найти адекватного решения в другом месте, так что это мой собственный обходной путь, который я надеялся, будет полезным для других в будущем.

Источник

В моем случае таблица была связана списком

 Dim intClientRow As Integer
Dim strLstSource As String
strLstSource = frmHNW.lstB01People_List.RowSource
frmHNW.lstB01People_List.RowSource = ""
intClientRow = UpdateTableFromFrame(frmHNW.fraB01People, "Clients_tblClientDetails", True)
If intClientRow > 0 Then
    UpdateTableFromList frmHNW.lstB01Contact_Method, "ClientPeople_tblClientContactMethod", "=getClientID(" & intClientRow & ")"
    UpdateTableFromList frmHNW.lstB01Pref_Days, "ClientPeople_tblClientPrefDay", "=getClientID(" & intClientRow & ")"
    UpdateTableFromList frmHNW.lstB01Pref_Time, "ClientPeople_tblClientPrefTime", "=getClientID(" & intClientRow & ")"
End If

frmHNW.lstB01People_List.RowSource = strLstSource

Формы UpdateTableFromFrame применяют метод lisrows.add к исходной таблице списка. После нескольких часов тестирования я обнаружил, что, когда я не удаляю источник списка, мой метод listrows.add не работает. Поэтому я удалил источник строк из списка перед вызовом UpdateTableFromFrame и восстановил обратно после добавления строки. Это решило проблему

Источник

Я знаю, что это старая ветка, я не хочу переиздавать, и я верю, что у меня есть решение, которое может помочь другим.

Немного информации о моих сценариях, у меня есть ListRows.Add in Private Sub BTN_Save_DblClick Я удалил весь свой код и оставил только этот модуль, к моему удивлению сработал, поэтому я начал реализовывать каждую подпрограмму и функцию, пока не добрался до того, который был вызывая проблему.

Оказывается, что (для меня), потому что у меня UserForm_Initialize() в качестве частного Sub вместо открытого Sub, это привело к сбою ListRows, в результате чего произошел сбой Excel.

Так что, если у вас возникла эта проблема, и у вас есть UserForm_Initialize(), убедитесь, что для нее установлено значение Public, а не private.

В любом случае, если это не сработает, тогда это простой процесс удаления, удалите весь свой код, кроме кода с ListRows, и добавляйте каждый дополнительный тест / функцию каждый раз, пока не найдете тот, который вызывает проблему.

Надеюсь это поможет.

РЕДАКТИРОВАТЬ Так что это было временное исправление, оно снова начало падать, поэтому я удалил строку кода внутри userform_initialize и исправил ее снова... это так странно, потому что все, что он делает, это ComboBox1.RowSource = NamedRange.

У меня никогда не было такого рода проблем с Excel VBA раньше.

Источник

Коротко

tab_.ListRows(1).Range.EntireRow.Insert может (!) работать вместо tab_.ListRows.Add.
(Как уже было предложено в ответе SandPiper)

инкапсулируя его в более общую функцию, которая также возвращаетListRow:

Function TabRowAdd(tab_ As ListObject) As ListRow
    tab_.ListRows(1).Range.EntireRow.Insert
    Set TabRowAdd = tab_.ListRows(1)
End Function

вы бы использовали это так: TabRowAdd(tab_) вместо того tab_.ListRows.Add

Возможная причина

Если под (или, может быть, даже под) текущей таблицей есть другие ячейки, отличные от таблицы, это не сработает, потому что (я думаю) "смещение вниз" этого другого содержимого может вызвать плохие побочные эффекты или может быть более сложным.

Итак, например, представьте себе следующую компоновку таблицы и как можно напрямую вставить строку в таблицу 1? (вообще плохая / тупая верстка - знаю:))

| table 1 header |  | table 2 header |
|         row 1  |  |         row 1  |
|         row 2  |  |         row 2  |         
                    |         row 3  |  | table 5 header |
| table 3 header |  |         row 4  |  |         row 1  |
|         row 1  |                      |         row 2  |
|         row 2  |                      |         row 3  |
                                        |         row 4  |
    | table 4 header      |             |         row 5  |
    |         row 1       |

=> это непросто, и поэтому я предполагаю, что они могут просто проверить, есть ли что-то, не являющееся таблицей, под или под таблицей, которой нужно управлять (вставить строку / столбец, удалить строку / столбец), и, таким образом, выбросить некоторые 1004исключение.
(возможно, даже после "не-A-column-start"таблицы уже будут иметь эту проблему - я не проверял это)

Источник

Это также подействовало на меня некоторое время. Мне показалось, что вызов ListRows.Add несколько раз в цикле - вызвало эту ошибку, а иногда и сбой Excel. Решение, которое сработало для меня: я добавил команду DoEvents после команды ListRows.Add:

tbl.ListRows.Add
DoEvents
Источник

Еще одна причина для ListObject.ListRows.Add потерпеть неудачу может быть защита листа. Так как я не увидел это ни в одном из моих результатов поиска, я хотел бы добавить его сюда для дальнейшего использования.

Если лист был защищен

worksheet.Protect

обязательно

worksheet.Unprotect

это перед применением ListObject.ListRows.Add!

Источник
Другие вопросы по тегам