Блог

Итак, я пытаюсь создать функцию, которая автоматически записывает один диапазон значений в другой, другой диапазон ячеек. У меня функция работает просто отлично, но возникает проблема, когда я начинаю использовать INDIRECT() для передачи ей аргументов.

 Public Function TranscribeRng(ReadInRng As Range, CopyToRng As Range)
If AreRngsEqual(ReadInRng, CopyToRng) = True Then
    Dim r As Long
    r = 0
    Dim c As Long
    c = 0
    
    While c < ReadInRng.Columns.Count
        r = 0
        While r < ReadInRng.Rows.Count
            
            Evaluate "TranscribeValueRNG(" amp; ReadInRng.Cells(r   1, c   1).Address(False, False) amp; "," amp; CopyToRng.Cells(r   1, c   1).Address(False, False) amp; ")"
            
            r = r   1
            Wend
        c = c   1
    Wend
    
    TranscribeRng = ReadInRng.Address(False, False) amp; " writing to " amp; CopyToRng.Address(False, False)
    
Else
    TranscribeRng = CVErr(xlErrValue)
End If
End Function


Private Sub TranscribeValueRNG(ReadValue As Range, CopyToCell As Range)
    CopyToCell.Value = ReadValue
    
End Sub
  

Это отлично работает, когда аргументы ReadInRng и CopyToRng передаются как обычные ссылки A1:B2 (например, =TranscribeRng(A1:B2,C1: D2)), но когда я использую эту функцию с КОСВЕННЫМИ () ссылками на ячейки (например, =TranscribeRng(INDIRECT(«A1:B2»,True), КОСВЕННЫЙ («C1: D2», True)) ) его поведение становится реальной проблемой.

при использовании КОСВЕННЫХ ссылок на ячейки функции пытаются воздействовать на ВСЕ листы в книге. Чтобы уточнить, при использовании этой функции на «Листе 1», вводя значение в ячейку A1 «Листа 1», функция TranscribeRng скопирует это значение в ячейку C1 на «Листе 1», как и предполагалось. ОДНАКО, когда я затем переключаюсь на «Лист2» и ввожу значение в ячейку A1 на «Листе 2», это значение ТАКЖЕ будет скопировано в ячейку C1 на «Листе 2», ДАЖЕ ЕСЛИ функция TranscribeRng присутствует только на «Листе 1».

Этого не происходит, когда я использую обычные ссылки на ячейки (например, A1: B2). В этом случае функция не действует на листы, на которых она не должна. Только когда я добавляю ссылки INDIRECT(), он начинает действовать на всех листах постоянно.

Я попытался добавить строку

 Application.Volatile(False)
  

в код, и это останавливает его активное копирование значений, под которым я подразумеваю, что функция работает только тогда, когда ее основная ячейка пересчитывается вручную (даже с КОСВЕННЫМИ () ссылками). Но затем, когда я пытаюсь пересчитать его основную ячейку с помощью другого процесса, такого как событие изменения рабочего листа или что-то вроде

 Sub ReCalc(Host as range)
   Host.calculate
End Sub
  

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

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

Я застрял на этой проблеме уже почти неделю, и это действительно сводит меня с ума. Любой совет, который вы можете предложить, был бы очень признателен. Спасибо!