Excel VBA - использовать массив для функции (3 переменные)

avatar
CW87
7 апреля 2018 в 21:44
664
2
0

В Excel VBA у меня есть функция для y с вводом 3 переменных (a, b, c). Что я хотел бы сделать, так это использовать VBA для автоматического применения этой функции к диапазону ячеек (комбинациям a, b и c, как указано на листе).

Как я настроил свои данные:

  b   1  1  1  1  1  2  2  2  2  3  3  3  4  4  etc.
  c   2  3  4  5  6  3  4  5  6  4  5  6  5  6  etc.

a  
1     .  .  .  .  .  .  .  .  .  .  .  .  .  .
2     .  .  .  .  .  .  .  .  .  .  .  .  .  .
3     .  .  .  .  .  .  .  .  .  .  .  .  .  .
4     .  .  .  .  .  .  .  .  .  .  .  .  .  .
etc.

'Точки' - это то, где должны заканчиваться результаты функции (a,b,c) (например, верхняя левая точка должна быть результатом "функции (1,1,2)"). Следует отметить, что в столбце должны использоваться одни и те же парные значения b и c (поэтому в одном и том же столбце меняется только «a», а b и c остаются постоянными).

Предыдущая версия этой функции имела только 2 переменные в качестве входных данных (a и b, настроенные так же, как данные выше), и я использовал 2D-массив (значения 'a' в строках по вертикали, значения «b» в столбцах по горизонтали), чтобы применить функцию ко всем комбинациям a и b, используя следующий код: 

Sub applyfunction()
Dim ws As Worksheet
Dim arr_ab()
Dim a, b, i As Long, j As Long

For Each ws In Worksheets
   If ws.Name Like "Util*" Then
       With ws
           a = .Range("B5:B244").Value
           b = .Range("C2:CG2").Value
           ReDim arr_ab(1 To UBound(a), 1 To UBound(b, 2))
           For i = LBound(arr_ab) To UBound(arr_ab)
              For j = LBound(arr_ab, 2) To UBound(arr_ab, 2)
                arr_ab(a, b) = "=function(" & a(i, 1) & ", " & b(1, j) & ")"
              Next j
           Next i
           .Range("C5:CG244").Value = arr_ab()
       End With
   End If
Next ws

End Sub

Однако теперь, когда я добавил в функцию третью переменную (c), я хочу использовать функцию (a,b,c). У меня есть некоторые проблемы с тем, чтобы заставить его работать, как я делал, когда у меня было только 2 переменные. Есть ли способ использовать для этого массив?

Заранее спасибо за любую помощь.

Изменить: я хочу использовать значения (а не ссылки) a,b,c в функции

Источник
user4039065
7 апреля 2018 в 21:49
0

Возможный дубликат Расширение ячеек столбца для каждой ячейки столбца.

chris neilsen
7 апреля 2018 в 23:57
0

Вы хотите, чтобы полученная формула использовала значения a, b, c или ссылки на ячейки, содержащие значения?

CW87
8 апреля 2018 в 08:10
0

@chrisneilsen Я бы хотел, чтобы в полученной формуле использовались значения a, b, c (а не ссылки). Я отредактировал вопрос, чтобы прояснить это.

chris neilsen
8 апреля 2018 в 08:16
0

Измените на `b = .Range("C2:CG3").Value` и = "=function(" & a(i, 1) & ", " & b(1, j) & "," &b(2,j) & ")"

CW87
8 апреля 2018 в 08:42
0

@chrisneilsen Кажется, это работает, спасибо! Есть ли способ отметить ваш комментарий как полезный?

Ответы (2)

avatar
DisplayName
8 апреля 2018 в 08:43
0

Используйте Application.Transpose() для работы с одномерным массивом, это упрощает работу:

Sub applyfunction()
    Dim ws As Worksheet
    Dim a As Variant, b As Variant, c As Variant
    Dim i As Long, j As Long

    For Each ws In Worksheets
        If ws.name Like "Util*" Then
            With ws
                a = Application.Transpose(.Range("B5", Cells(.Rows.Count, "B").End(xlUp)).Value)
                b = Application.Transpose(Application.Transpose(.Range("C2", .Cells(2, .Columns.Count).End(xlToLeft)).Value))
                c = Application.Transpose(Application.Transpose(.Range("C3").Resize(, UBound(b)).Value))
                ReDim abc(1 To UBound(a), 1 To UBound(b))
                For i = LBound(a) To UBound(a)
                    For j = LBound(b) To UBound(b)
                        abc(i, j) = "=MyFunction(" & a(i) & ", " & b(j) & ", " & c(j) & ")"
                    Next j
                Next i
                .Range("C5").Resize(UBound(a), UBound(b)).Value = abc()
            End With
        End If
    Next
End Sub

Хотя вы можете сохранить b и c в двумерном массиве, чтобы немного сократить код: 

Sub applyfunction()
    Dim ws As Worksheet
    Dim a As Variant, bc As Variant
    Dim i As Long, j As Long

    For Each ws In Worksheets
        If ws.name Like "Util*" Then
            With ws
                a = Application.Transpose(.Range("B5", Cells(.Rows.Count, "B").End(xlUp)).Value)
                bc = .Range("C2", .Cells(2, .Columns.Count).End(xlToLeft)).Resize(2).Value
                ReDim abc(1 To UBound(a), 1 To UBound(bc, 2))
                For i = LBound(a) To UBound(a)
                    For j = LBound(bc, 2) To UBound(bc, 2)
                        abc(i, j) = "=MyFunction(" & a(i) & ", " & bc(1, j) & ", " & bc(2, j) & ")"
                    Next
                Next
                .Range("C5").Resize(UBound(a), UBound(bc, 2)).Value = abc()
            End With
        End If
    Next
End Sub

ПРИМЕЧАНИЕ. Я использовал "MyFunction" вместо "function" для запуска теста. Измените «MyFunction» на фактическое имя функции

.
avatar
perfo
8 апреля 2018 в 00:25
0
Option Explicit
Sub ApplyFunction()

Dim Arng As Range: Set Arng = Application.Range("A4:A8")
Dim AVal As Integer
Dim ACell As Range
Dim ARow As Integer

Dim Brng As Range: Set Brng = Application.Range("C1:G1")
Dim BVal As Integer
Dim BCell As Range
Dim BCol As Integer
Dim BRow As Integer

Dim CVal As Integer


For Each ACell In Arng
  AVal = ACell.Value
  ARow = ACell.Row
  For Each BCell In Brng
    BVal = BCell.Value
    BCol = BCell.Column
    BRow = BCell.Row
    CVal = Cells(BRow + 1, BCol)
    Cells(ARow, BCol) = ABC(AVal, BVal, CVal)
  Next
Next


End Sub

Function ABC(A As Integer, B As Integer, C As Integer) As Integer
   ABC = A * B * C
End Function

Я не уверен, хотите вы этого или нет. Проблема, с которой вы сталкиваетесь при использовании трех элементов в массиве, заключается в том, что вы теряете отображение из двумерного набора результатов в трехмерный массив. Таким образом, вам придется делать что-то другое, когда вы хотите перенести результаты на свой рабочий лист, то есть не просто отображать содержимое массива. Короткий ответ на ваш вопрос: да, если это можно сделать, вы можете добавить много измерений, но это будет становиться все менее и менее эффективным и более неуклюжим при сопоставлении его с двумерным набором результатов. Я не воссоздал весь ваш код с моим выше, но я включил достаточно, чтобы показать вам другой способ сделать это. Я не проверяю, на каком листе я нахожусь, или полностью ссылаюсь на ячейки и т. д., но помещаю его в лист, и он будет делать то, что, как я думаю, вы хотите, а затем модифицировать, чтобы атаковать нужные листы и т. д.