VBA. Ошибка при присваивании массиву значений из "умной" таблицы.

Пользователь

Сообщений: 2871 Регистрация: 13.06.2014

29.03.2022 09:34:58

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

Код

Sub maxValue()
    valuesLst = ThisWorkbook.Sheets("values").Range("values[Значения]") 'Ошибка в этой строке
    ThisWorkbook.Sheets("values").Cells(1, 3).Value = Application.WorksheetFunction.Max(valuesLst)
End Sub

Код

Sub minValue()
    Dim valuesLst() As Variant
    valuesLst() = ThisWorkbook.Sheets("values").Range("values[Значения]").Value 'Ошибка в этой строке
    ThisWorkbook.Sheets("values").Cells(2, 3).Value = Application.WorksheetFunction.Min(valuesLst())
End Sub

В основном макросе немного другая ошибка:

Скрытый текст

Код

Sub createReports()
    Application.ScreenUpdating = False
    Dim isDistrLst(), branchLst() As Variant
    Path = ThisWorkbook.Path & "\reports\"
    branchLst() = ThisWorkbook.Sheets("Параметры").Range("branchs[Branch]").Value 'Ошибка в этой строке
    isDistrLst() = Array("Да", "Нет")
    Set pt = ThisWorkbook.Worksheets("Отчет").PivotTables("modulesResults")
    For Each x In branchLst
     For Each y In isDistrLst
        pt.PivotFields("[dim_CRMIDS].[Branch].[Branch]").CurrentPageName = "[dim_CRMIDS].[Branch].&[" & x & "]"
        pt.PivotFields("[dim_CRMIDS].[BU (outlet) Is Distributor].[BU (outlet) Is Distributor]").CurrentPageName = "[dim_CRMIDS].[BU (outlet) Is Distributor].&[" & y & "]"
        pt.TableRange1.Copy
        If y = "Да" Then isDistr = "_дистр" Else isDistr = "_не дистр"
        Workbooks.Add
        WB1 = ActiveWorkbook.Name
        Sh1 = ActiveSheet.Name
        Workbooks(WB1).Sheets(Sh1).Paste
        Rows("2:2").AutoFilter
        Range("A3").CurrentRegion.Select
        Selection.RowHeight = 15
        Selection.EntireColumn.AutoFit
        Selection.HorizontalAlignment = xlLeft
        phoneClmn = Rows(2).Find(What:="Phone").Column
        lastRow = Cells(1, 1).CurrentRegion.Rows.Count
        For i = 3 To lastRow
            Cells(i, phoneClmn).Value = WorksheetFunction.Replace(Cells(i, phoneClmn).Value, 4, 4, "****")
        Next i
        ActiveWorkbook.SaveAs Filename:=Path & x & isDistr & ".xlsx"
        ActiveWorkbook.Close
     Next y
    Next x
    pt.PivotFields("[dim_CRMIDS].[Branch].[Branch]").ClearAllFilters
    pt.PivotFields("[dim_CRMIDS].[BU (outlet) Is Distributor].[BU (outlet) Is Distributor]").ClearAllFilters
    Application.ScreenUpdating = True
End Sub

Прикрепленные файлы

test.xlsm (17.35 КБ)

Изменено: surkenny - 29.03.2022 09:35:28

Jack Famous

Пользователь

Сообщений: 12041 Регистрация: 07.11.2014

OS: Win 10 Корп. x64 | Excel 2016 x64: | Browser: Chrome

29.03.2022 09:47:22

surkenny, здравствуйте!

Цитата
surkenny: valuesLst = ThisWorkbook.Sheets("values").Range("values[Значения]")

1. Проверить существование листа "values" в книге из которой запускается макрос.
Если макрос запускается из надстройки, то ThisWorkbook вернёт её, а не книгу, в которой вы работаете.
2. Не пробовал присваивать диапазон таким образом (скорее всего, ошибка именно тут). Поробуйте задать столбцу умной таблицы именованный диапазон и ссылаться на него. Возможно, в вашем варианте не хватает апострофа(-ов)

Изменено: Jack Famous - 29.03.2022 09:47:48

Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄

surkenny Пользователь Сообщений: 2871 Регистрация: 13.06.2014	#3 29.03.2022 09:52:16 Jack Famous, лист существует:) А у Вас работает макрос в файле-примере? Макрос запускается кнопкой с листа.

Пытливый

Пользователь

Сообщений: 4714 Регистрация: 22.12.2012

29.03.2022 09:52:52

Добрый.
По второму вопросу - вы объявили массив неизвестной размерности.

Код
branchLst() As Variant

А потом непонятно какому члену массива пытаетесь присвоить значения диапазона

Код
branchLst() = ThisWorkbook.Sheets("Параметры").Range("branchs[Branch]").Value

Там проще:

Код
Dim branchLst branchLst = ThisWorkbook.Sheets("Параметры").Range("branchs[Branch]").Value

Кому решение нужно - тот пример и рисует.

Дмитрий(The_Prist) Щербаков

Пользователь

Сообщений: 14890 Регистрация: 15.09.2012

Профессиональная разработка приложений для MS Office

29.03.2022 10:18:55

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

Код

    Dim valuesLst 'скобки не обязательны, в принципе, но инициализация так будет быстрее
    valuesLst = ThisWorkbook.Sheets("values").Range("values[Значения]").Value 'Ошибка в этой строке
    If Not IsArray(valuesLst) Then
        ReDim valuesLst(1 To 1, 1 To 1)
        valuesLst(1, 1) = ThisWorkbook.Sheets("values").Range("values[Значения]").Value
    End If

Скорее всего в этом и причина.

Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...

surkenny

Пользователь

Сообщений: 2871 Регистрация: 13.06.2014

29.03.2022 10:30:51

Уважаемые помогающие, я дико извиняюсь перед Вами! Пошел к коллеге, а он с мака в parallels отчет запускает. Вероятнее всего, проблема может быть в этом. Может, сталкивался кто?

Цитата
Пытливый написал: Там проще:

Такой вариант я пробовал, тоже не работает.

Цитата
Дмитрий(The_Prist) Щербаков написал: Скорее всего в этом и причина.

Дмитрий, не работает даже файл из примера

Теоретически, конечно, одна строка может быть, но в реальных данных их 8.

Дмитрий(The_Prist) Щербаков

Пользователь

Сообщений: 14890 Регистрация: 15.09.2012

Профессиональная разработка приложений для MS Office

29.03.2022 10:47:05

Цитата
написал: не работает даже файл из примера

попробуйте все поля таблицы назвать на латинице - без русских букв. Тоже самое с именами листов. Это самая частая проблема в Parallels.
Плюс можно еще и так попробовать обращаться:

Код
Dim valuesLst Dim lo As ListObject, rr As Range Set lo = ThisWorkbook.Sheets("values").ListObjects("values") Set rr = lo.ListColumns("Значения").DataBodyRange valuesLst = rr.Value

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

P.S. К умным таблицам можно и как к именованным диапазонам с областью Книга обращаться - без указания конкретного листа:

Код
valuesLst = [values[Значения]].Value

Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...

surkenny Пользователь Сообщений: 2871 Регистрация: 13.06.2014	#8 29.03.2022 10:58:39 Дмитрий(The_Prist) Щербаков, благодарю! Все опробую и отпишусь.

surkenny

Пользователь

Сообщений: 2871 Регистрация: 13.06.2014

29.03.2022 11:44:28

Дмитрий(The_Prist) Щербаков, вариант branchLst() = [branchs[Branch]].Value сработал. Спасибо огромное!

А возможно ли обращаться к сводной таблице без ссылки на лист?

Код
Set pt = ThisWorkbook.Sheets("Report").PivotTables("modulesResults")

Дмитрий(The_Prist) Щербаков

Пользователь

Сообщений: 14890 Регистрация: 15.09.2012

Профессиональная разработка приложений для MS Office

#10

29.03.2022 11:54:14

К сводной так обращаться нельзя, к сожалению. Это объект другого типа, да и к тому же такое обращение будет не однозначным, т.к. в одной книге может быть сразу три сводных с одним и тем же именем, если все они на разных листах. С умными таблицами так нельзя - все умные таблицы в книге должны иметь уникальное имя.

Цитата
surkenny написал: вариант branchLst() = [branchs[Branch]].Value сработал

вообще здесь по хорошему разобраться бы для начала, почему этот вариант сработал, а прямое обращение по имени листа и объекта - нет. Точно ли дело в Parallels или нет. Ведь ошибка потом может вылезти в каком-нибудь совсем неожиданном месте.

Изменено: Дмитрий(The_Prist) Щербаков - 29.03.2022 11:56:54

Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...

Jack Famous Пользователь Сообщений: 12041 Регистрация: 07.11.2014 OS: Win 10 Корп. x64 \| Excel 2016 x64: \| Browser: Chrome	#11 29.03.2022 11:55:56 del Изменено: Jack Famous - 29.03.2022 11:56:27 Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄

surkenny

Пользователь

Сообщений: 2871 Регистрация: 13.06.2014

#12

29.03.2022 12:01:28

Цитата
Дмитрий(The_Prist) Щербаков написал: Точно ли дело в Parallels или нет. Ведь ошибка потом может вылезти в каком-нибудь совсем неожиданном месте.

Ок, попробую. Но у меня на Windows все варианты макроса отрабатывали корректно без ошибок.

Изменено: surkenny - 29.03.2022 12:01:42

Дмитрий(The_Prist) Щербаков Пользователь Сообщений: 14890 Регистрация: 15.09.2012 Профессиональная разработка приложений для MS Office	#13 29.03.2022 12:03:12 Я про то, что неплохо бы для начала попробовать везде где можно кириллицу заменить на латиницу, включая кодовые имени листов и книги. Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...