Как изменить значение ячейки vba excel - Word и Excel - помощь в работе с программами

We use VBA to automate our tasks in excel. The idea of using VBA is to connect the interface of excel with the programming. One of the very most connections between them is by changing the cell values. The change in cell value by programming shows the power of VBA. In this article, we will see how to set, get and change the cell value.

Set Cell Value

Assigning a cell with a value can be achieved by very two famous functions in VBA i.e. Range and Cells function.

Range Function in VBA

The range function helps access the cells in the worksheet. To set the cell value using the range function, we use the .Value.

Syntax: Range(cell_name).Value = value_to_be_assinged.

Set the value to a single cell

If you want to assign ’11’ to cell A1, then the following are the steps:

Step 1: Use the Range function, in the double quotes type the cell name. Use .Value object function. For example, Range(“A1”).Value = 11.

Step 2: Run your macro. The number 11 appears in cell A1.

Set the value to multiple cells at the same time

Remember the days, when your teacher gives you punishment, by making you write the homework 10 times, those were the hard days, but now the effort has exponentially reduced. You can set a value to a range of cells with just one line of code. If you want to write your name, for example, “Arushi” 10 times, in the range A2 to A11. Use range function. Following are the steps:

Step 1: Use the Range function, in the double quotes, write “Start_of_cell: End_of_cell”. Use .Value object function. For example, Range(“A2:A11”).Value = “Arushi”.

Step 2: Run your macro. The text “Arushi” appears from cell A2 to A11 inclusive.

Cells Function in VBA

The Cells function is similar to the range function and is also used to set the cell value in a worksheet by VBA. The difference lies in the fact that the Cells function can only set a single cell value at a time while the Range function can set multiple values at a time. Cells function use matrix coordinate system to access cell elements. For example, A1 can be written as (1, 1), B1 can be written as (1, 2), etc.

Syntax: Cells(row_number, column_number).Value = value_to_be_assign

For example, you want to set the cell value to “Arushi cleared CA with Rank “, in cell B1. Also, set cell C1, to ‘1’. Following are the steps:

Step 1: Open your VBA editor. Use cells function, as we want to access cell B1, the matrix coordinates will be (1, 2). Type, Cells(1, 2).Value = “Arushi cleared CA with Rank” in the VBA code.

Step 2: To access cell C1, the matrix coordinates are (1, 3). Type, Cells(1, 3).Value = 1 in the VBA code.

Step 3: Run your macro. The required text appears in cell B1, and a number appears in C1.

Setting Cell values by Active cell and the input box

There are other ways by which you can input your value in the cell in a worksheet.

Active Cell

You can set the cell value of a cell that is currently active. An active cell is the selected cell in which data is entered if you start typing. Use ActiveCell.Value object function to set the value either to text or to a number.

Syntax: ActiveCell.Value = value_to_be_assigned

For example, you want to assign the active cell with a text i.e. “Arushi is practicing CA”, also want to change the color of the cell to yellow. Following are the steps:

Step 1: Use the ActiveCell object to access the currently selected cell in the worksheet. Use ActiveCell.Value function object to write the required text.

Step 2: Color the cell by using ActiveCell.Interior.Color function. For example, use vbYellow to set your cell color to yellow.

Step 3: Run your macro. The currently selected cell i.e. B1 has attained the requirements.

Input Box

You can use the input box to set the cell value in a worksheet. The input box takes the custom value and stores the result. This result could further be used to set the value of the cell. For example, set the cell value of A1, dynamically by taking input, from the input box.

Following are the steps

Step 1: Open your VBA editor. A sub-procedure name geeks() is created. Use the Range function to store the value given by the input box.

Step 2: Run your Macro. A dialogue-box name Microsoft Excel appears. Enter the value to be stored. For example, “geeks for geeks”. Click Ok.

Step 3: Open your worksheet. In cell A1, you will find the required text is written.

Get Cell Value

After setting the cell value, it’s very important to have a handsome knowledge of how to display the cell value. There can be two ways two get the cell value either print the value in the console or create a message box.

Print Cell Value in Console

The console of the VBA editor is the immediate window. The immediate window prints the desired result in the VBA editor itself. The cell value can be stored in a variable and then printed in the immediate window. For example, you are given a cell A1 with the value ’11’, and you need to print this value in the immediate window.

Following are the steps

Step 1: Press Ctrl + G to open the immediate window.

Step 2: The cell value in A1 is 1.

Step 3: Open your VBA editor. Declare a variable that could store the cell value. For example, Val is the variable that stores the cell value in A1. Use the Range function to access the cell value. After storing the cell value in the val, print the variable in the immediate window with the help of Debug.Print(val) function.

Step 4: Run your macro. The cell value in A1 is printed in the immediate window.

Print Cell Value in a Message Box

A message box can also be used to show the cell value in VBA. For example, a random string is given in cell A1 of your string i.e. “Arushi studies in Hansraj”. Now, if you want to display the cell value in A1, we can use Message Box to achieve this.

Following are the steps

Step 1: Open your VBA macro. Create a message box by using MsgBox. Use the Range(cell).Value function to access the cell value.

Step 2: Run your macro. A message box appears, which contains the cell value of A1.

Change Cell Values

The value, once assigned to the cell value, can be changed. Cell values are like variables whose values can be changed any number of times. Either you can simply reassign the cell value or you can use different comparators to change the cell value according to a condition.

By reassigning the Cell Value

You can change the cell value by reassigning it. In the below example, the value of cell A1 is initially set to 1, but later it is reassigned to 2.

Following are the steps

Step 1: Open your VBA code editor. Initially, the value of cell A1 is assigned to 1. This initial value is printed in the immediate window. After that, we changed the value of cell A1 to 2. Now, if we print the A1 value in the immediate window, it comes out to be 2.

Step 2: The immediate window shows the output as 1 and 2.

Changing cell value with some condition

We can use if-else or switch-case statements to change the cell value with some condition. For example, if your age is greater than 18 then you can drive else you cannot drive. You can output your message according to this condition.

Following are the steps

Step 1: A code is written in the image below, which tells whether you are eligible for a driving license or not. If your age is greater than 18 then cell A1 will be assigned with the value, “You are eligible for the driving license”, else A1 will be assigned with “You are not eligible for driving license”.

Step 2: Run your macro. An input box appears. Enter your age. For example, 19.

Step 3: According to the age added the cell value will be assigned.

Источник

Изменение значений других ячеек из пользовательской функции VBA Excel с помощью методов Range.Replace и Application.Volatile. Примеры кода.

В первых двух параграфах описано нетрадиционное использование процедуры Function, поэтому не применяйте его в серьезных проектах. Тестирование проводилось в Excel 2016.

Функция с методом Range.Replace

Пользовательская функция не предназначена для изменения значений ячеек, кроме той, в которой она расположена. Попытка присвоить какое-либо значение из функции другой ячейке приводит к неработоспособности функции и отображению в ячейке, где она расположена, сообщения «#ЗНАЧ!».

Но, как ни странно, внутри процедуры Function работает метод Range.Replace, которым мы воспользуемся для изменения значений других ячеек из пользовательской функции.

Пример 1
Эта функция заменяет значение ячейки Cell1 на значение ячейки Cell2 увеличенное на 100. Сама функция размещается в третьей ячейке, чтобы не возникла циклическая ссылка.

Function Primer1(Cell1 As Range, Cell2 As Range)

Cell1.Replace Cell1, Cell2 + 100

End Function

В этом примере мы не присваиваем пользовательской функции значение, поэтому отображается значение по умолчанию – 0. Если объявить эту функцию как строковую: Function Primer1(Cell1 As Range, Cell2 As Range) as String, будет возвращена пустая строка.

Изменение значения ячейки C1 (Cell2) приведет к пересчету значения ячейки B1 (Cell1).

Попробуйте очистить или перезаписать ячейку B1 (Cell1), ничего не получится, так как функция Primer1 вновь перезапишет ее значением C1 (Cell2) + 100.

Метод Application.Volatile

Application.Volatile – это метод, который запускает пересчет функции при изменении значения любой ячейки рабочего листа, а не только той, которая присвоена объявленной в функции переменной. Метод Application.Volatile используется только в функциях.

Рассмотрим пересчет функции на следующем примере:

Пример 2

Function Primer2(Cell1 As Range, Cell2 As Range) As String

Cell1.Replace Cell1, Cell2 + 100

Range(«B2»).Replace Range(«B2»), Range(«C2») + Cell1

End Function

Эта функция будет пересчитываться только при изменении значений ячеек B1 и C1, присвоенных переменным Cell1 и Cell2. При изменении значения ячейки C2, значение ячейки B2 не изменится, так как не будет запущен пересчет функции Primer2.

Функция Primer2 начнет вести себя по-другому, если добавить в нее оператор Application.Volatile (переименуем ее в Primer3):

Пример 3

Function Primer3(Cell1 As Range, Cell2 As Range) As String

Application.Volatile

Cell1.Replace Cell1, Cell2 + 100

Range(«B2»).Replace Range(«B2»), Range(«C2») + Cell1

End Function

Теперь при смене значения в ячейке C2, значение ячейки B2 тоже изменится.

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

Безопасное использование функции

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

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

В простых случаях для выбора можно использовать функцию Choose или, в более сложных, оператор If…Then…Else и оператор Select Case.

Пример 4
Используем функцию Choose для выбора способа вычисления пользовательской функции в зависимости от значения дополнительного аргумента:

Function Primer4(Cell1 As Range, Cell2 As Range, a As Byte)

On Error Resume Next

Primer4 = Choose(a, Cell1 + Cell2, Cell1 — Cell2, Cell1 * Cell2)

End Function

В функцию Primer4 добавлен дополнительный аргумент a, от которого зависит, какое действие будет произведено со значениями ячеек B1 и C1:

На следующем скриншоте представлены результаты вычисления функции в зависимости от значения аргумента a:

В ячейке A1 вычисляется сумма значений ячеек B1 и C1 – аргумент a=1.
В ячейке A2 вычисляется разность значений ячеек B2 и C2 – аргумент a=2.
В ячейке A3 вычисляется произведение значений ячеек B3 и C3 – аргумент a=3.

Пример 5
Используем оператор If…Then…Else в сокращенном виде (If…Then…) для выбора способа вычисления функции в зависимости от значения дополнительного аргумента:

Function Primer5(Cell1 As Range, Cell2 As Range, a As Byte)

If a = 1 Then Primer5 = Cell1 + Cell2

If a = 2 Then Primer5 = Cell1 — Cell2

If a = 3 Then Primer5 = Cell1 * Cell2

End Function

Результаты будут те же, что и в четвертом примере.

Источник

Содержание

Событие Worksheet.Change (Excel)
Синтаксис
Параметры
Возвращаемое значение
Замечания
Пример
Поддержка и обратная связь
Как функцией пользователя изменить значение другой ячейки
VBA Excel. Изменение значений других ячеек из функции
Функция с методом Range.Replace
Метод Application.Volatile
Безопасное использование функции
VBA Excel. Запуск макроса при изменении ячейки
Запуск макроса при изменении ячейки
Пример кода
Примечания
Исключение ложного срабатывания
VBA Excel. Свойства ячейки (объекта Range)
Ячейка и объект Range
Свойства ячейки (объекта Range)
Простые примеры для начинающих
Форматирование ячеек

Событие Worksheet.Change (Excel)

Происходит при изменении ячеек на листе пользователем или внешней ссылкой.

Синтаксис

expression. Изменение (целевой объект)

Выражение Переменная, представляющая объект Worksheet .

Параметры

Имя	Обязательный или необязательный	Тип данных	Описание
Target (Целевое значение)	Обязательный	Диапазон	Измененный диапазон. Может быть несколько ячеек.

Возвращаемое значение

Nothing

Замечания

Это событие не возникает при изменении ячеек во время пересчета. Используйте событие Calculate для перехвата пересчета листа.

Пример

В следующем примере кода цвет измененных ячеек изменяется на синий.

В следующем примере кода проверяется, что при изменении значения ячейки измененная ячейка находится в столбце A, а также если измененное значение ячейки больше 100. Если значение больше 100, смежная ячейка в столбце B изменяется на красный цвет.

В следующем примере кода значения в диапазоне A1:A10 задаются в верхнем регистре при вводе данных в ячейку.

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

Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.

Источник

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

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

Синтаксис функции:
=ChangeAnotherCell( G1 ;»новое значение»)

rCell( G1 ) — ячейка, в которой необходимо поменять значение. Должна отличаться от адреса ячейки, в которой записана сама функция. Например, если функция записана в ячейке A1 , то в качестве аргумента rCell не может быть так же A1 . В лучшем случае получите ошибку цикличности, в худшем — Excel завершиться аварийно, а файл может быть при этом безвозвратно поврежден.

vVal(«новое значение») — значение для записи в ячейку rCell . Может быть как произвольным текстом или числом, так и ссылкой на другую ячейку. Значение в ячейке rCell не удаляется после удаления функции ChangeAnotherCell .
Но как уже писал выше: лучше не особо надеяться на данный метод. Может иногда и не сработать. Например, если в ячейку rCell ранее не было занесено никакое значение, то функция может просто отказаться менять значение ячейки.

Если чуть модернизировать функцию, то помимо значения можно изменить еще и формат ячейки:

‘————————————————————————————— ‘ Author : Щербаков Дмитрий(The_Prist) ‘ Профессиональная разработка приложений для MS Office любой сложности ‘ Проведение тренингов по MS Excel ‘ Procedure : ChangeAnotherCell ‘ Purpose : Функция меняет значение и формат указанной ячейки ‘ Аргументы: ‘ rCell — ячейка, в которой необходимо поменять значение. ‘ vVal — значение для записи в ячейку rCell. ‘ Произвольный текст или ссылка на ячейку. ‘ bRepFormat — ИСТИНА(TRUE) — будет скопирован формат из rFormatRng. ‘ rFormatRng — ссылка на ячейку, формат которой необходимо копировать. ‘————————————————————————————— ‘ Function ChangeAnotherCell(rCell As Range, vVal, Optional bRepFormat As Boolean = False, Optional rFormatRng As Range = Nothing) As String Application.Volatile If bRepFormat Then If Not rFormatRng Is Nothing Then With Application.ReplaceFormat .Clear .Font.Bold = rFormatRng.Font.Bold .Font.ColorIndex = rFormatRng.Font.ColorIndex .Font.FontStyle = rFormatRng.Font.FontStyle .Borders.Color = rFormatRng.Borders.Color .Borders.Weight = rFormatRng.Borders.Weight If Val(Application.Version) >= 12 Then ‘версии старше 2003 не поддерживают копирование заливки .Font.Name = rFormatRng.Font.Name Else .Interior.ColorIndex = rFormatRng.Interior.ColorIndex End If End With End If End If With rCell If .Value = Empty Then .Replace Empty, vVal, 1, , 0, , , bRepFormat Else .Replace .Value, vVal, 1, , 0, , , bRepFormat End If End With Application.ReplaceFormat.Clear End Function

Синтаксис функции:
=ChangeAnotherCell( G1 ;»новое значение»;ИСТИНА; E14 )

rCell( G1 ) — ячейка, в которой необходимо поменять значение. Должна отличаться от адреса ячейки, в которую записана сама функция.

bRepFormat(ИСТИНА) — если указан как ИСТИНА (TRUE) или 1, то так же меняется формат ячейки rCell . Формат копируется из ячейки, указанной аргументом rFormatRng. Поэтому в данном случае аргумент rFormatRng обязателен к указанию. Если ЛОЖЬ (FALSE) , 0 или не указан — формат не копируется.

rFormatRng( E14 ) — ссылка на ячейку, формат которой необходимо копировать.

Статья помогла? Поделись ссылкой с друзьями!

Источник

VBA Excel. Изменение значений других ячеек из функции

Функция с методом Range.Replace

В этом примере мы не присваиваем пользовательской функции значение, поэтому отображается значение по умолчанию – 0. Если объявить эту функцию как строковую: Function Primer1(Cell1 As Range, Cell2 As Range) as String , будет возвращена пустая строка.

Изменение значения ячейки C1 (Cell2) приведет к пересчету значения ячейки B1 (Cell1).

Метод Application.Volatile

Рассмотрим пересчет функции на следующем примере:

Пример 2

Функция Primer2 начнет вести себя по-другому, если добавить в нее оператор Application.Volatile (переименуем ее в Primer3):

Пример 3

Теперь при смене значения в ячейке C2, значение ячейки B2 тоже изменится.

Безопасное использование функции

На следующем скриншоте представлены результаты вычисления функции в зависимости от значения аргумента a:

В ячейке A1 вычисляется сумма значений ячеек B1 и C1 – аргумент a=1.
В ячейке A2 вычисляется разность значений ячеек B2 и C2 – аргумент a=2.
В ячейке A3 вычисляется произведение значений ячеек B3 и C3 – аргумент a=3.

Источник

VBA Excel. Запуск макроса при изменении ячейки

Запуск макроса (процедуры) из кода VBA Excel при изменении значения ячейки вручную или программным способом, а также при нажатии клавиши Delete.

Запуск макроса при изменении ячейки

Пример кода

Код VBA Excel для запуска макроса (процедуры) при изменении значения ячейки на рабочем листе:

Range(«A1:D8») — это диапазон, при изменении значения любой ячейки которого, произойдет выполнение функции MsgBox. Диапазон может состоять из одной ячейки.

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

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

Примечания

Макрос будет запущен при изменении значения ячейки вручную или программным способом, а также при нажатии клавиши Delete.
Вызываемая процедура будет запущена и в том случае, если в ячейку будет записано то же самое значение, которое в ней и было. Например, в ячейке было число 28, и в нее записали вручную или программно число 28; или нажали клавишу Delete на пустой ячейке.
Макрос не будет запущен при изменении значения ячейки с формулой в результате ее пересчета.

Исключение ложного срабатывания

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

Источник

VBA Excel. Свойства ячейки (объекта Range)

Свойства ячейки, часто используемые в коде VBA Excel. Демонстрация свойств ячейки, как структурной единицы объекта Range, на простых примерах.

Ячейка и объект Range

Объект Range в VBA Excel представляет диапазон ячеек. Он (объект Range) может описывать любой диапазон, начиная от одной ячейки и заканчивая сразу всеми ячейками рабочего листа.

Одна ячейка – Range(«A1») .
Девять ячеек – Range(«A1:С3») .
Весь рабочий лист в Excel 2016 – Range(«1:1048576») .

В VBA Excel есть свойство Cells объекта Range, которое позволяет обратиться к одной ячейке в указанном диапазоне (возвращает объект Range в виде одной ячейки). Если в коде используется свойство Cells без указания диапазона, значит оно относится ко всему диапазону активного рабочего листа.

Примеры обращения к одной ячейке:

Cells(1000) , где 1000 – порядковый номер ячейки на рабочем листе, возвращает ячейку «ALL1».
Cells(50, 20) , где 50 – номер строки рабочего листа, а 20 – номер столбца, возвращает ячейку «T50».
Range(«A1:C3»).Cells(6) , где «A1:C3» – заданный диапазон, а 6 – порядковый номер ячейки в этом диапазоне, возвращает ячейку «C2».

Подробнее о том, как обратиться к ячейке, смотрите в статье: Ячейки (обращение, запись, чтение, очистка).

В этой статье мы рассмотрим свойства объекта Range, применимые, в том числе, к диапазону, состоящему из одной ячейки.

Еще надо добавить, что свойства и методы объектов отделяются от объектов точкой, как в третьем примере обращения к одной ячейке: Range(«A1:C3»).Cells(6) .

Свойства ячейки (объекта Range)

Свойство	Описание
Address	Возвращает адрес ячейки (диапазона).
Borders	Возвращает коллекцию Borders, представляющую границы ячейки (диапазона). Подробнее…
Cells	Возвращает объект Range, представляющий коллекцию всех ячеек заданного диапазона. Указав номер строки и номер столбца или порядковый номер ячейки в диапазоне, мы получаем конкретную ячейку. Подробнее…
Characters	Возвращает подстроку в размере указанного количества символов из текста, содержащегося в ячейке. Подробнее…
Column	Возвращает номер столбца ячейки (первого столбца диапазона). Подробнее…
ColumnWidth	Возвращает или задает ширину ячейки в пунктах (ширину всех столбцов в указанном диапазоне).
Comment	Возвращает комментарий, связанный с ячейкой (с левой верхней ячейкой диапазона).
CurrentRegion	Возвращает прямоугольный диапазон, ограниченный пустыми строками и столбцами. Очень полезное свойство для возвращения рабочей таблицы, а также определения номера последней заполненной строки.
EntireColumn	Возвращает весь столбец (столбцы), в котором содержится ячейка (диапазон). Диапазон может содержаться и в одном столбце, например, Range(«A1:A20») .
EntireRow	Возвращает всю строку (строки), в которой содержится ячейка (диапазон). Диапазон может содержаться и в одной строке, например, Range(«A2:H2») .
Font	Возвращает объект Font, представляющий шрифт указанного объекта. Подробнее о цвете шрифта…
HorizontalAlignment	Возвращает или задает значение горизонтального выравнивания содержимого ячейки (диапазона). Подробнее…
Interior	Возвращает объект Interior, представляющий внутреннюю область ячейки (диапазона). Применяется, главным образом, для возвращения или назначения цвета заливки (фона) ячейки (диапазона). Подробнее…
Name	Возвращает или задает имя ячейки (диапазона).
NumberFormat	Возвращает или задает код числового формата для ячейки (диапазона). Примеры кодов числовых форматов можно посмотреть, открыв для любой ячейки на рабочем листе Excel диалоговое окно «Формат ячеек», на вкладке «(все форматы)». Свойство NumberFormat диапазона возвращает значение NULL, за исключением тех случаев, когда все ячейки в диапазоне имеют одинаковый числовой формат. Если нужно присвоить ячейке текстовый формат, записывается так: Range(«A1»).NumberFormat = «@» . Общий формат: Range(«A1»).NumberFormat = «General» .
Offset	Возвращает объект Range, смещенный относительно первоначального диапазона на указанное количество строк и столбцов. Подробнее…
Resize	Изменяет размер первоначального диапазона до указанного количества строк и столбцов. Строки добавляются или удаляются снизу, столбцы – справа. Подробнее…
Row	Возвращает номер строки ячейки (первой строки диапазона). Подробнее…
RowHeight	Возвращает или задает высоту ячейки в пунктах (высоту всех строк в указанном диапазоне).
Text	Возвращает форматированный текст, содержащийся в ячейке. Свойство Text диапазона возвращает значение NULL, за исключением тех случаев, когда все ячейки в диапазоне имеют одинаковое содержимое и один формат. Предназначено только для чтения. Подробнее…
Value	Возвращает или задает значение ячейки, в том числе с отображением значений в формате Currency и Date. Тип данных Variant. Value является свойством ячейки по умолчанию, поэтому в коде его можно не указывать.
Value2	Возвращает или задает значение ячейки. Тип данных Variant. Значения в формате Currency и Date будут отображены в виде чисел с типом данных Double.
VerticalAlignment	Возвращает или задает значение вертикального выравнивания содержимого ячейки (диапазона). Подробнее…

В таблице представлены не все свойства объекта Range. С полным списком вы можете ознакомиться не сайте разработчика.

Простые примеры для начинающих

Вы можете скопировать примеры кода VBA Excel в стандартный модуль и запустить их на выполнение. Как создать стандартный модуль и запустить процедуру на выполнение, смотрите в статье VBA Excel. Начинаем программировать с нуля.

Учтите, что в одном программном модуле у всех процедур должны быть разные имена. Если вы уже копировали в модуль подпрограммы с именами Primer1, Primer2 и т.д., удалите их или создайте еще один стандартный модуль.

Форматирование ячеек

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

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

Источник

Небольшая памятка по работе с макросами VBA Excel. Для вставки в код, измените название листа и диапазон ячеек на свой.

Добавить текст к значениям (в данном случае добавить слово «add» в начале ячеек):

1
2
3

For Each c In Worksheets("list1").Range("F7:F44").Cells 
  c.Value = "add" + CStr(c.Value)
Next

Добавить к значениям текст из соседней ячейки справа:

1
2
3

For Each c In Worksheets("list1").Range("F7:F44").Cells
  c.Value = CStr(c.Value) + " " + c.Cells(1, 2).Value
Next

Вывести строку обрезанную до 6 символов:

1
2
3

For Each c In Worksheets("list1").Range("J3:J175").Cells
  c.Value = Left(c.Value, 6)
Next

Убрать все после первого пробела:

For Each c In Worksheets("list1").Range("J3:J175").Cells
  c.Value = CStr(c.Value) + " "
  spacePos = inStr(1, c.Value, " ")
  artLen = spacePos - 1
  c.Value = Left(c.Value, artLen)
Next

Убрать все до первого пробела, затем убрать пробелы в начале строки:

For Each c In Worksheets("list1").Range("D2:D79").Cells
  spacePos = InStr(1, c.Value, " ")
  c.Value = Mid(c.Value, spacePos)
  c.Value = LTrim(c.Value)
Next

Если здесь нет нужного вам макроса, посмотрите ещё часть 2.

Источник

I have a «duration» column in an Excel sheet. Its cell format always changes — I want convert the duration from minutes to seconds, but because of the cell formatting it always gives me different answers.

I was thinking that before doing the conversion I could convert that cell format to text so that it will consider that as text value and not try to auto-format it.

Currently I am copying all data into Notepad and then saving it back to the Excel sheet to remove all of the previous format. Is there a way to automate setting a cell’s formatting to text using VBA?

Teamothy

1,9903 gold badges15 silver badges24 bronze badges

asked Nov 25, 2011 at 6:10

Code HungryCode Hungry

3,87022 gold badges66 silver badges95 bronze badges

To answer your direct question, it is:

Range("A1").NumberFormat = "@"

Cells(1,1).NumberFormat = "@"

However, I suggest changing the format to what you actually want displayed. This allows you to retain the data type in the cell and easily use cell formulas to manipulate the data.

answered Nov 25, 2011 at 20:03

One point: you have to set NumberFormat property BEFORE loading the value into the cell. I had a nine digit number that still displayed as 9.14E+08 when the NumberFormat was set after the cell was loaded. Setting the property before loading the value made the number appear as I wanted, as straight text.

OR:

Could you try an autofit first:

Excel_Obj.Columns("A:V").EntireColumn.AutoFit

Teamothy

1,9903 gold badges15 silver badges24 bronze badges

answered Apr 22, 2015 at 19:56

Well this should change your format to text.

Worksheets("Sheetname").Activate
Worksheets("SheetName").Columns(1).Select 'or Worksheets("SheetName").Range("A:A").Select
Selection.NumberFormat = "@"

answered Nov 25, 2011 at 20:02

JonJon

4333 gold badges6 silver badges24 bronze badges

for large numbers that display with scientific notation set format to just ‘#’

answered Feb 21, 2019 at 18:08

To prevent Scientific Notation

With Range(A:A)
    .NumberFormat = "@"
    .Value = .Formula
End With

answered Mar 14, 2022 at 13:02

Источник

Set Cell Value

Set the value to a single cell

Get Cell Value

Change Cell Values

Функция с методом Range.Replace

Метод Application.Volatile

Безопасное использование функции

Событие Worksheet.Change (Excel)

Синтаксис

Параметры

Возвращаемое значение

Замечания

Пример

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

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

VBA Excel. Изменение значений других ячеек из функции

Функция с методом Range.Replace

Метод Application.Volatile

Безопасное использование функции

VBA Excel. Запуск макроса при изменении ячейки

Запуск макроса при изменении ячейки

Пример кода

Примечания

Исключение ложного срабатывания

VBA Excel. Свойства ячейки (объекта Range)

Ячейка и объект Range

Свойства ячейки (объекта Range)

Простые примеры для начинающих

Форматирование ячеек

А вот еще интересные статьи: