Remkomplekty.ru

IT Новости из мира ПК
4 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Formular1c1 vba excel

Select и Activate — зачем нужны и нужны ли?

Все начинающие изучать VBA сталкиваются с тем, что записанные через макрорекордер коды пестрят методами Select и Activate.
Если не знакомы с работой макрорекордера — Что такое макрос и где его искать?
Это значительно ухудшает читабельность кода и, как ни странно — быстродействие. Но есть недостатки и куда более критичные. Если код выполняется достаточно долго и он постоянно что-то выделяет — пользователь может заскучать и забыться и начнет тыкать мышкой по листам и ячейкам, выделяя не то, что выделил ранее код. Что повлечет ошибки логики. Т.е. код может и выполнится, но совершенно не так, как ожидалось. Поэтому избавляться от Select и Activate необходимо везде, где это возможно.

Для начала рассмотрим два кода, выполняющие одни те же действия — запись в ячейку А3 листа Лист2 слова «Привет». При этом сам код запускается с Лист1 и после выполнения код Лист1 должен остаться активным. Чтобы сделать эти действия вручную потребуется сначала перейти на Лист2, выделить ячейку А3, записать в неё слово «Привет» и вернуться на Лист1. Поэтому запись макрорекордером этих действий приведет к такому коду:

Sub Макрос1() Sheets(«Лист2»).Select ‘выделяем Лист2 Range(«A3»).Select ‘выделяем ячейку А3 ActiveCell.FormulaR1C1 = «Привет» ‘записываем слово Привет Range(«A4»).Select ‘после нажатия Enter автоматически выделяется ячейка А4 Sheets(«Лист1»).Select ‘возвращаемся на Лист1 End Sub

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

Sub Макрос1() Sheets(«Лист2»).Range(«A3»).FormulaR1C1 = «Привет» End Sub

Как видно, вместо 5-ти строк кода получилась одна строка. Которая выполняет ту же задачу, что и код из 5-ти строк.
Прежде чем понять как правильно избавляться от лишнего давайте разберемся зачем же тогда VBA записывает эти Select и Activate? Как ни странно, но здесь все очень просто. VBA просто не знает, что Вы будете делать после того, как выделили Лист2. И когда Вы переходите на Лист2 — VBA записывает именно переход(его активацию, выделение). Когда выделяете ячейку — так же именно это действие записывает VBA. Захотите ли Вы затем выделить еще что-то, или закрасить ячейку, или записать в неё формулу/значение — VBA не знает. Поэтому в дальнейшем VBA работает именно с выделенным объектом Selection на активном листе.
Но при написании кода вручную или при правке записанного рекордером мы уже вольны в выборе и знаем, чего хотели добиться и какие действия нам точно не нужны.
Итак, чтобы записать в ячейку слово «Привет» рекордер предложит нам такой код:

Sub Макрос1() Range(«A3»).Select ‘выделяем ячейку А3 ActiveCell.FormulaR1C1 = «Привет» ‘записываем слово Привет Range(«A4»).Select ‘после нажатия Enter автоматически выделяется ячейка А4 End Sub

однако выделять ячейку( Range(«A3»).Select ) совершенно необязательно. Значит один Select уже лишний. После этого идет обращение к активной ячейке — ActiveCell . .FormulaR1C1 = «Привет» означает запись значения «Привет» в эту ячейку.
Пусть не смущает FormulaR1C1 — VBA всегда так указывает запись и значения и формулы. Т.к. перед словом «Привет» нет знака равно — то это значение.
Т.к. ActiveCell является обращением к выделенной ячейке, а выделили мы до этого А3, значит их можно просто «сократить»:

Sub Макрос1() Range(«A3»).FormulaR1C1 = «Привет» Range(«A4»).Select ‘после нажатия Enter автоматически выделяется ячейка А4 End Sub

Теперь у нас код получился короче и понятнее. Однако остался один Select: Range(«A4»).Select . Если нет необходимости выделять ячейку А4 после записи в А3 значения, то надо просто удалить эту строку и после выполнения кода активной будет та ячейка, которая была выделена до выполнения(т.е. выделенная ячейка просто не изменится). Таким образом мы с трех строк сократим код до 1-ой:

Sub Макрос1() Range(«A3»).FormulaR1C1 = «Привет» End Sub

Теперь несложно догадаться, что с листами все в точности так же. Sheets(«Лист2»).Select — Select хоть и не нужен, но и ActiveSheet после него нет. Здесь необходимо знать некоторую иерархию в Excel. Сначала идет сам Excel — Application, потом книга — Workbook. В книгу входят рабочие листы(Worksheets), а уже в листах — ячейки и диапазоны — Range и Cells(Application ->Workbook ->Worksheet ->Range). Если перед Range или Cells не указывать явно лист: Range(«A3»).FormulaR1C1 = «Привет» , то значение будет записано на активный лист. Подробнее можно прочесть в статье: Как обратиться к диапазону из VBA

Маленький нюанс: если сокращаем обращение к объектам, то Select-ов быть не должно вообще. Иначе есть шанс получить ошибку «Subscript out of range»:

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

Sub Макрос2() Windows(«Книга3»).Activate ‘здесь появится ошибка, т.к. пытаемся выделить лист в Книга2 ‘а на данный момент активной является Книга3 Windows(«Книга2»).Sheets(«Лист3»).Select End Sub

Ошибка обязательно появится, т.к. сначала мы активировали кодом книгу «Книга3», а потом пытаемся активировать лист НЕактивной на этот момент книги «Книга2». А это сделать невозможно без активации той книги, в которой активируемый лист. Т.е. активация должна происходить именно последовательно: Книга ->Лист ->Ячейка. И никак иначе, если мы хотим активировать именно конкретную ячейку конкретного листа в конкретной книге.
И пример с ячейками:

Читать еще:  Как определить среднее значение в excel

Sub Макрос2() Sheets(«Лист3»).Select ‘здесь появится ошибка, т.к. пытаемся выделить ячейку на листе «Лист1» ‘а на данный момент активным является Лист3 Sheets(«Лист1»).Range(«C7»).Select End Sub

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

Еще небольшой пример оптимизации:

Sub Макрос2() Windows(«Книга3»).Activate Sheets(«Лист3»).Select Range(«C7»).Select ActiveCell.FormulaR1C1 = «Привет» Range(«C7»).Select Selection.Font.Bold = True With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 65535 .TintAndShade = 0 .PatternTintAndShade = 0 End With End Sub

Этот код записывает в ячейку С7 Лист3 книги «Книга3» слово «Привет», потом делает жирным шрифт и назначает желтый цвет заливке. Убираем активацию книги, листа и ячейки, заменив их прямым обращением:

Formular1c1 vba excel

Возникла следующая проблема:
Из c# сбрасываем информацию в Excel, причем в некоторые ячейки сбрасываем формулы, так как предполагаем, что цифры можно будет изменять непосредственно в Excel. Все работает, если просто присваивать ячейке строку типа «=СУММАЕСЛИ(. )». Но есть одно но. Предполагается, что программа будет работать не только с русской, но с английской и немецкой версиями Excel (а в перспективе и с любыми другими). И в этом случае данный прием не проходит: при попытках запустить ту же программу с нерусской версией Excel на месте формул оказывается белиберда. Помогите пожалуста. Что делать, если нужно в ячейки Excel из C# вставить формулы, чтобы они работали в любой версии Excel?

Volkopus’
Что делать, если нужно в ячейки Excel из C# вставить формулы, чтобы они работали в любой версии Excel?
Используйте английские имена функций. Они будут работать везде.

СУММ — SUMM
СУММЕСЛИ — SUMIF
СРЗНАЧ — AVERAGE
НОРМ — RATE

Истинные (английские) имена функций легко смотреть, например, с помощью записи макросов.

Volkopus’ Толко присваивать формулу с функциями поименованные «по англицки» надо не значению ячеки .Value, а соотвесвующему свойству .Formula. В случае же когда желается или приходится таки формулу с функциями озаглавленными всенепременно на «ридной локальной мове» впихивать пользовать свойство .FormulaLocal и будет всем Щастье.

Ну и Help по VBA почитывать где все этоподробнейшим образом расписанно.

Kid_Deceiver,
CrazyElk
Спасибо!
Но не работает! И непонятно, почему. В VBA все то же самое работает, а в C# нет.
Строчка, которая вставляет формулу в ячейку Excel из C# cейчас выглядит следующим образом:

oXL.ActiveCell.FormulaR1C1= «=SUM(R[1]C:R[3]C)»; (где oXL — Excel._Application)

Пробовалось также просто:

И не работает! Уже в русской версии Excel.
В соответствующей ячейке (A1) Excel появляется ‘#ИМЯ?’. Если проверить, что же c# записал в строку формул этой ячейки (просмотрев эту ячейку в Excel), то выясняется: «=SUM(A2:A4)”. То есть диапазон он понимает, а формулу нет, так как она записана на английском.

то все работает в русской версии Excel. Есть ли шанс, что это заработает в английской версии? Я очень сильно сомневаюсь.

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

Kid_Deceiver,
спасибо еще раз!
Но, по-прежнему не работает! И дело тут, видимо, в C#.
В C# oSheet.Cells[10,2] возвращает тип object. Ему можно присвоить значение. Например,
oSheet.Cells[10,2]=”10”, но никаких методов и атрибутов(помимо тех что есть у любого объекта) у него нет. Соответственно, атрибута FormulaR1C1 у него тоже нет. Зато есть такой атрибут у типа
Excel.Range.
Мы пробовали 3 варианта:
1)
((Excel.Range)oSheet.Cells[10,2]).FormulaR1C1=»=SUM(R1C1:R10C1)»;

Результат везде один и тот же. Не работает в русском Excel. В строчке формул ячейки B10 появляется ‘=SUM($A$1:$A$10)’, которая не работает, так как написана на английском (высвечивается ‘#ИМЯ?’).

Если поменять SUM на СУММ, то любым методом работает в русском Excel, но есть бооольшие сомнения, что заработает в иностранном.

А еще как-нибудь можно вбивать формулы, понятные Excel’ям любых стран?

Volkopus’
Вообще даже если просто набрать в русском Exсel формулу =sum(A1:A5) данная формула не работает а вот с СУММ будет.
Наверно есть какойто параметр который определяет какой Excel русский или английский запущен, и потом по условию подставлять или СУММ или sum (ИМХО)

Ну а так аналоги функций русских по английски можно посмотреть в файле FUNCS.XLS

Volkopus’ — Погоди стреляться.

Специально для тебя хотя шарпа под рукой нет но для Automation должно быть по баробану откуда только что проверил на VB. Excel — русский, стиль формул по умолчанию A1 и все работат хоть через нетипизированные обьекты хоть черз типизированные (для типизированных первые три закомментировать следующие за ними раскомментировать и незабыть секцию References пополнить указанием на Excel ).

Читать еще:  Количество вхождений подстроки в строку excel

Прогони у себя код: У меня печатает
И в интерфейсе Excel тоже все нормально в локальной кодировке. (только его еще и видимым надо сделать если хочеш увидить)

3 совета по написанию формул с помощью макросов VBA в Excel

Итог: ознакомьтесь с 3 советами по написанию и созданию формул в макросах VBA с помощью этой статьи и видео.

Уровень мастерства: Средний

Скачать файл

Загрузите файл Excel, чтобы сопровождать видео.

3 Tips For Writing Excel Formulas In VBA.xlsm (82.3 KB)

Автоматизировать написание формул

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

Поначалу написание формул в VBA может быть немного сложнее, поэтому вот три совета, которые помогут сэкономить время и упростить процесс.

Совет № 1: Свойство Formula

Свойство Formula является членом объекта Range в VBA. Мы можем использовать его для установки / создания формулы для отдельной ячейки или диапазона ячеек.

Есть несколько требований к значению формулы, которые мы устанавливаем с помощью свойства Formula:

  1. Формула представляет собой строку текста, заключенную в кавычки. Значение формулы должно начинаться и заканчиваться кавычками.
  2. Строка формулы должна начинаться со знака равенства = после первой кавычки.

Вот простой пример формулы в макросе.

Свойство Formula также можно использовать для чтения существующей формулы в ячейке.

Совет № 2: Используйте Macro Recorder

Если ваши формулы более сложные или содержат специальные символы, их будет сложнее написать в VBA. К счастью, мы можем использовать рекордер макросов, чтобы создать код для нас.

Вот шаги по созданию кода свойства формулы с помощью средства записи макросов.

  1. Включите средство записи макросов (вкладка «Разработчик»> «Запись макроса»)
  2. Введите формулу или отредактируйте существующую формулу.
  3. Нажмите Enter, чтобы ввести формулу.
  4. Код создается в макросе.

Если ваша формула содержит кавычки или символы амперсанда, макрос записи будет учитывать это. Он создает все подстроки и правильно упаковывает все в кавычки. Вот пример.

Совет № 3: Нотация формулы стиля R1C1

Если вы используете средство записи макросов для формул, вы заметите, что он создает код со свойством FormulaR1C1.

Нотация стиля R1C1 позволяет нам создавать как относительные (A1), абсолютные ($A$1), так и смешанные ($A1, A$1) ссылки в нашем макрокоде.

R1C1 обозначает строки и столбцы.

Относительные ссылки

Для относительных ссылок мы указываем количество строк и столбцов, которые мы хотим сместить от ячейки, в которой находится формула. Количество строк и столбцов указывается в квадратных скобках.

Следующее создаст ссылку на ячейку, которая на 3 строки выше и на 2 строки справа от ячейки, содержащей формулу.

Отрицательные числа идут вверх по строкам и столбцам слева.

Положительные числа идут вниз по строкам и столбцам справа.

Абсолютные ссылки

Мы также можем использовать нотацию R1C1 для абсолютных ссылок. Обычно это выглядит как $A$2.

Для абсолютных ссылок мы НЕ используем квадратные скобки. Следующее создаст прямую ссылку на ячейку $A$2, строка 2, столбец 1

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

Проще всего использовать макро-рекордер, чтобы понять это.

Свойство FormulaR1C1 и свойство формулы

Свойство FormulaR1C1 считывает нотацию R1C1 и создает правильные ссылки в ячейках. Если вы используете обычное свойство Formula с нотацией R1C1, то VBA попытается вставить эти буквы в формулу, что, вероятно, приведет к ошибке формулы.

Поэтому используйте свойство Formula, если ваш код содержит ссылки на ячейки ($ A $ 1), свойство FormulaR1C1, когда вам нужны относительные ссылки, которые применяются к нескольким ячейкам или зависят от того, где введена формула.

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

Я надеюсь, что эти советы помогут. Пожалуйста, оставьте комментарий ниже с вопросами или предложениями.

Формула Excel в коде VBA

Итак, в Sheet1 у меня есть база некоторых имен, и она выглядит так:

В Sheet2 я работаю с этими именами из Sheet1. Я делаю это так, чтобы вводить значение Кода в столбце A и в столбце BI получить имя, в столбце CI получить имя. Это выглядит так:

Я сделал это с помощью формул, введя их в панель формул. Для столбца A (или Name) я использовал эту формулу: =IFERROR(VLOOKUP(A2;Sheet1!A:C;2;FALSE);»») а для столбца B (или Фамилия) я использовал этот: =IFERROR(VLOOKUP(A2;Sheet1!A:C;3;FALSE);»») . Я перетащил эти формулы в строку 20, и он отлично работает.

Теперь, что я хотел бы сделать, это поместить эти формулы в код Excel VBA, и они будут работать для отмеченного диапазона. Я только начал использовать VBA, и я не знаю, как это сделать в нем, попробовал что-то, но не работает,…, я сделал это до сих пор. Я новичок в этой работе Excel/Macro/VBA, поэтому любая помощь будет оценена по достоинству.

Читать еще:  Как убрать скобки в excel

Следующий код будет работать, если вы sheet2 значения Code в sheet2 и выделите их и запустите этот макрос:

Изменить: если вы хотите обновлять значения по мере ввода (спасибо @PeterAlbert за дополнительную оптимизацию!):

Explinatioin RC:

FormulaR1C1 формулы FormulaR1C1 хороши для использования при ссылке на ячейку относительно текущей ячейки. Есть несколько правил, которые нужно запомнить:

  • R означает, что Row и C для столбца, а целое число после него, если оно есть, определяет строку или столбец;
  • В качестве основы RC формула ссылается сама;
  • Любое число, следующее за R или C спрятанным в [] является смещением к себе, например, если вы находитесь в ячейке A1 и используете R[1]C[1] вы будете ссылаться на ячейку B2 ;
  • Также любое число, следующее за R и C является точным, например, если вы ссылаетесь на R2C2 независимо от того, R2C2 ячейка вы находитесь, также указывается на B2 ; а также

Чтобы усложнить ситуацию, если вы были в ячейке C5 , например, с помощью Range(«C5»).FormulaR1C1 = и закодировал следующее:

  1. «=RC[-1]» ссылается на ячейку B5
  2. «=RC1» ссылается на ячейку A5 , более правильно $A5
  3. «=R[1]C[-2]» ссылается на ячейку A6
  4. «=Sum(C[-1]:C5)» is =Sum(B:E) , более справедливо =Sum(B:$E)

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

Вместо этого я предлагаю вам использовать защиту листа: все, что вам нужно сделать, — это разблокировать ячейки, которые вы хотите изменить, например, выбрать столбец A и в диалоговом окне «Формат ячеек» снять флажок «Заблокировано» на вкладке «Защита». Аналогично для столбцов B & C отметьте «Скрытый». Теперь щелкните правой кнопкой мыши имя листа и выберите «Защитить лист». Как только это будет сделано, пользователь может отредактировать столбец A, но не увидит формулу в B & C и не сможет редактировать эти ячейки.

Если по некоторым причинам вам необходимо обеспечить это в VBA, используйте следующий код:

Вы должны поместить это в модуль рабочего листа.

Excel VBA: скрыть строки с помощью FormulaR1C1

Я пытаюсь скрыть строки, содержащие даты праздников. В другом модуле у меня есть команда «да» в ячейке R3, чтобы активировать этот суб, поэтому мне просто нужно выяснить суб. Ошибок нет, он просто не скрывает строки, которые мне нужны, чтобы скрыть. Формула, которую я использую, работает, чтобы найти праздники, потому что она правильно выделяет ячейки, когда я помещаю их в Условное форматирование. Код ниже:

HIDE WEEKEND CODE: Sub HideWeekends () Dim beginRow As Long, endRow As Long, chkCol As Long, chkCommCol As Long, rowCnt As Long

вы используете ActiveCell.FormulaR1C1 но

ActiveCell не «обновляется» (с некоторыми инструкциями Select )

и не имеет никакого отношения к фактической переменной цикла, которая является Cells(rowCnt, chkCol)

вы используете FormulaR1C1 а «=Match($A1,Holidays!$B$2:$B$11,0)» не находится в стиле R1C1

с ws.Cells(rowCnt, chkCol).FormulaR1C1 = «=Match($A1,Holidays!$B$2:$B$11,0)» вы сравниваете два выражения Formula , тогда как

фактическое содержимое ваших проверочных ячеек является реальной date (а не формулой, возвращающей date )

вы хотите проверить, находится ли текущий контент ячейки (дата) в заданном диапазоне: это можно достичь с помощью метода Find() объекта Range или метода Match() объекта Application

вы не отбираете все диапазоны до своего рабочего листа

для всего, что выше, вы можете попробовать этот рефакторинг вашего кода:

отредактирован, чтобы следовать запросу OP, чтобы отменить код, если содержимое ячейки «R7» «2017 Все районы» – «Да»,

Вы должны полностью квалифицировать объекты, с которыми хотите работать.

Я предлагаю вам написать Option Explicit в верхней части ваших кодовых листов, чтобы вы были вынуждены объявить все свои переменные. На самом деле, у вас было много констант, и самый эффективный способ объявить числовые константы – посредством перечисления. Это то, что я сделал.

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

Он работает, но я не уверен, что он делает то, что вы хотите. Предполагается, что существует только один лист. Если есть другой, он не появляется в вашем коде. Пожалуйста, имейте в виду, что вы должны указать книгу, чтобы найти лист, лист, чтобы найти строку, и столбец, чтобы найти ячейку. Иногда эти спецификации неявные, как лист, прекрасно знающий, к какой книге он принадлежит, но это не относится к Range («A1»), который может быть на любом листе. И, конечно, если вы хотите обратиться к ActiveCell, вы должны сначала активировать ячейку. Все несоответствия адресации были разрешены.

Существует проблема с вашим желанием искать формулу, потому что формула имеет синтаксис =MATCH($A9 в ней, где строка 9 является переменной. Таким образом, на вашем листе есть только одна формула, точно такая же. глядя только на диапазон матчей «Holidays!$B$2:$B$11,0)» . Если этого недостаточно, есть другие способы решения этой проблемы с большей точностью.

Ссылка на основную публикацию
ВсеИнструменты
Adblock
detector
×
×