Блог

Главная - Вопросы по программированию - Как использовать коллекцию в vba для обновления другой ячейки по сравнению со значением одной ячейки

Как использовать коллекцию в vba для обновления другой ячейки по сравнению со значением одной ячейки

#excel #vba #collections

#excel #vba #Коллекции

Вопрос:

Мой лист Excel:

введите описание изображения здесь

Мой код VBA:

 Sub popdata()

    Set compreplace = New Collection
    compreplace.Add "AA-", "test@yahoo.com"
    compreplace.Add "BB-", "test2@yahoo.com"
    compreplace.Add "CC-", "test3@yahoo.com"
    compreplace.Add "DD-", "test4@yahoo.com"
    compreplace.Add "EE-", "test5@yahoo.com"

    Call popexcel

End Sub

Function popexcel()
    Dim rc As Integer
    rc = UsedRange.Rows.Count
    Dim i As Integer  

    For i = 2 To rc
        //fill in "L" column value respective to the row
        //example "L2" should have "AA-" because "K2" value is "test@yahoo.com"
        Range("L" amp; i).Value = compreplace.Item(Range("K" amp; i).Value)
    Next i
End Function

Однако я получаю следующую ошибку в VBA:

Ошибка времени выполнения ‘5’:

Недопустимый вызов процедуры или аргумент

Любая идея, как решить эту проблему, была бы высоко оценена.

Комментарии:

1.Ваша функция не может «видеть» Collection (это выходит за рамки). Либо передайте это в качестве аргумента, либо создайте его Global .

2. Итак, я обновил ее и переместил коллекцию внутри popexcel функции, и я все еще получаю ту же ошибку

3. Укажите Option Explicit , и вы захотите передать compreplace в popexcel качестве параметра (или продвинуть его в область модуля).

4. Итак, если я действительно передаю (string), это работает, но когда я использую Range (row, col). Значение Я получаю эту ошибку… Я предполагаю, что мне нужно преобразовать .Value в строку перед передачей?

5. Range.Value это a, Variant который может содержать любой тип данных, и VBA с радостью преобразует их в String для вас — за исключением случаев, когда ячейка содержит Error значение, в этом случае ни неявное, ни явное преобразование не сработает: вы получите ошибку несоответствия типов, если попытаетесь использовать Error и сравнить его или присвоить ему String . Используйте IsError функцию, чтобы проверить, является ли вариант Variant/Error .

Ответ №1:

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

 Public compreplace As Collection

Sub popdata()    
    Set compreplace = New Collection
    compreplace.Add "AA-", "test@yahoo.com"
    compreplace.Add "BB-", "test2@yahoo.com"
    compreplace.Add "CC-", "test3@yahoo.com"
    compreplace.Add "DD-", "test4@yahoo.com"
    compreplace.Add "EE-", "test5@yahoo.com"

    popexcel

End Sub

Function popexcel()
    Dim rc As Integer
    rc = ActiveSheet.UsedRange.Rows.Count
    Dim i As Integer

    For i = 2 To rc
        If ItemExists(compreplace, Range("K" amp; i).Value) Then
            Range("L" amp; i).Value = compreplace.item(Range("K" amp; i).Value)
        End If
    Next i
End Function


Function ItemExists(coll As Collection, item As String) As Boolean
    On Error Resume Next
    coll.item item
    ItemExists = (Err.Number = 0)
End Function

Функция ItemExists возвращает true, если есть соответствующий элемент. Общедоступное объявление вверху делает его доступным для вашего метода pop. Не лучший способ, но он работает.

Комментарии:

1. Вы, вероятно, захотите создать popexcel a Private Function , поскольку здесь происходит временная связь (ее использование без предварительного вызова popdata привело бы к ошибке) — передача коллекции в качестве параметра удалит временную связь.

2. @MathieuGuindon — Я согласен. В этом случае я попытался максимально повторно использовать исходный код, чтобы сосредоточиться на проблеме.

3. У меня это работает. Спасибо. В чем отличие от того, что я делал, просто для пояснения. Спасибо.

4. Можно ли в любом случае также добавить условие, если значение, которое получит значение «L», тогда не вставляйте его… Я думаю, что инструкции if там будет достаточно 🙂

5. @Si8 — Ваш код, как вы его опубликовали, будет работать, если все значения совпадают. В противном случае вы получите сообщение об ошибке, которое вы опубликовали. Я добавил функцию, которая проверяет, доступно ли значение, прежде чем пытаться его извлечь. Если она не существует, она не пытается.