В этой статье мы узнаем, как создавать пользовательские функции в Microsoft Excel с помощью VBA .

_ Функция, определяемая пользователем: — Microsoft Excel уже имеет множество функций, но все же у всех разные требования, ситуации, мы можем создать нашу собственную функцию в соответствии с требованием, которое называется функцией, определяемой пользователем. Мы можем использовать функцию, определяемую пользователем, как и другие функции в Excel ._

Ниже приведены темы, для которых мы создадим пользовательскую функцию: 1). Как посчитать количество слов в ячейке или диапазоне?

2). Как извлечь слово из предложения или ячейки в Excel?

3). Как создать формулу для ISO?

4). Как узнать имя рабочего листа и книги с помощью VBA?

5). Как извлечь первое и последнее слово из ячейки в Excel?

Как создать определяемую пользователем функцию для подсчета количества слов в ячейке или диапазоне? У нас есть данные на листе 1, в котором у нас есть несколько адресов, поэтому мы хотим подсчитать слова в ячейке или диапазоне, создав пользовательскую функцию через VBA в Excel.

image 1

Чтобы сделать функцию, определяемую пользователем, выполните следующие действия: — * Откройте страницу VBA и нажмите клавиши Alt + F11.

  • Вставьте модуль.

Напишите указанный ниже код:

Function WORDSCOUNT(rRange As Range) As Long

Dim rCell As Range

Dim Count As Long

For Each rCellInrRange

lCount = lCount + Len(Trim(rCell)) - Len(Replace(Trim(rCell), " ", "")) + 1

Next rCell

WORDSCOUNT = lCount

End Function

Пояснения к коду: — Чтобы сделать функцию определяемой пользователем, мы начинаем код с имени функции и определяем переменные. Мы использовали «цикл для каждого» в коде для подсчета слов в диапазоне.

Как использовать эту функцию в Excel?

Чтобы использовать эту функцию, выполните следующие действия: — * Перейти к таблице Excel.

  • Чтобы подсчитать количество слов в ячейке, введите формулу в ячейку D7.

  • = WORDSCOUNT (C7), ячейка C7 — это ячейка, в которой мы хотим вычислить слова.

  • Функция вернет 6, что означает, что ячейка C7 содержит 6 слов.

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

image 2

  • Чтобы подсчитать количество слов в диапазоне, используйте формулу = WORDSCOUNT (C7: C16) и нажмите Enter.

  • Функция вернет количество слов.

image 3

Примечание: — Этот UDF будет полезен для подсчета слов в диапазоне или в одной ячейке.

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

Function SEPARATECOUNTWORDS(rRange As Range, Optional separator As Variant) As Long

Dim rCell As Range

Dim Count As Long

If IsMissing(separator) Then

separator = ","

End If

For Each rCellInrRange

lCount = lCount + Len(Trim(rCell)) - Len(Replace(Trim(rCell), separator, ""))

Next rCell

SEPARATECOUNTWORDS = lCount

End Function

Чтобы использовать эту функцию, выполните следующие действия: —

  • Перейти к таблице Excel.

  • Чтобы подсчитать конкретные разделители в слове, мы будем использовать эту определенную функцию.

  • = ОТДЕЛЬНЫЕ СЛОВА (C7) и нажмите Enter.

  • Функция вернет количество определенных разделителей.

image 4

Как извлечь слово из предложения или ячейки в Microsoft Excel с помощью VBA? У нас есть данные на листе 1. В котором у нас есть несколько адресов, поэтому мы хотим извлечь слова из предложения, ячейки или диапазона, создав пользовательскую функцию через VBA в Excel.

image 5

Чтобы сделать функцию, определяемую пользователем, выполните следующие действия: —

  • Откройте страницу VBA и нажмите клавиши Alt + F11.

  • Вставьте модуль.

Напишите указанный ниже код *: —

Function GETWORD(Text As Variant, N As Integer, Optional Delimiter As Variant) As String

If IsMissing(Delimiter) Then

Delimiter = " "

End If

GETWORD = Split(Text, Delimiter)(N - 1)

End Function

Пояснение кода: — В вышеупомянутом коде мы упомянули имя функции с переменными. А затем мы определили критерии для извлечения слова из предложения или ячейки.

Теперь узнаем, как пользоваться этой формулой. Выполните следующие шаги: — * Перейти к таблице Excel.

  • Используйте эту формулу в ячейке D7.

  • = GETWORD (C7,2) и нажмите Enter.

  • Функция вернет второе слово из ячейки, потому что в формуле, которую мы упоминали для слова 2 ^ nd ^ number. Если вы хотите получить слово, которое находится в позиции 3 ^ rd ^, вам нужно изменить число с 2 на 3 в формуле.

image 6

Как создать формулу номера недели ISO в Microsoft Excel с помощью VBA? Мы узнаем, как создать формулу номера недели ISO в Excel с помощью этого UDF. Эту функцию мы будем использовать, чтобы определить, что указанная дата относится к какому номеру недели в году.

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

image 7

Чтобы создать UDF для этого требования, выполните следующие действия: — * Откройте страницу VBA и нажмите клавишу Alt + F11.

  • Вставьте модуль.

  • Напишите указанный ниже код: —

Function ISOWEEKNUMBER(Indate As Date) As Long

Dim Dt As Date

Dt = DateSerial(Year(Indate - Weekday(Indate - 1) + 4), 1, 3)

ISOWEEKNUMBER = Int((Indate - Dt + Weekday(Dt) + 5) / 7)

End Function

Пояснение кода: -: — В приведенном выше коде мы упомянули имя функции с переменными. Затем мы установили значение даты, а затем мы определили критерии функции «ISOWEENUMBER».

Как мы можем использовать эту функцию в нашем файле Excel?

  • Перейти к листу Excel.

  • Введите формулу в ячейку D7.

  • = НОМЕР НЕДЕЛИ ISO (C7) и нажмите Enter.

  • Функция вернет неделю для введенной даты в ячейку.

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

image 8

Теперь мы узнаем, как вернуть начало года по стандартам ISO в Excel — первый понедельник года.

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

image 9

Выполните следующие действия: — * Откройте страницу VBA и нажмите клавиши Alt + F11.

  • Вставьте модуль.

Напишите указанный ниже код *: —

Function ISOSTYR(Year As Integer) As Date

Dim WD As Integer

Dim NY As Date

NY = DateSerial(Year, 1, 1)

WD = (NY - 2) Mod 7

If WD < 4 Then

ISOSTYR = NY - WD

Else

ISOSTYR = NY - WD + 7

End If

End Function

Объяснение кода: — В приведенном выше коде мы упомянули имя функции с переменными. Затем мы установили критерии для переменных, а затем определили входные данные формулы.

Вам просто нужно указать 2001 год в этом формате, и формула даст вам 1 ^ й ^ понедельник года.

Теперь мы узнаем, как использовать UDF в файле Excel. Выполните следующие шаги: — * Перейти к таблице Excel.

  • Введите формулу в ячейку D7.

  • = ISOSTYR (C7) и нажмите Enter.

  • Функция вернет дату 1 ^ го ^ понедельника первой недели Нового года.

  • Чтобы вернуть дату 1 ^ го ^ понедельника первой недели Нового года, скопируйте ту же формулу и вставьте ее в диапазон.

image 10

Как узнать имя рабочего листа и книги с помощью VBA в Microsoft Excel? Следуйте приведенному ниже коду и шагам: — * Откройте страницу VBA и нажмите клавишу Alt + F11.

  • Вставьте модуль.

Напишите указанный ниже код *: —

Function Worksheetname()

Worksheetname = Range("A1").Parent.Name

End Function

Объяснение кода: — В приведенном выше коде мы упомянули имя функции, а затем определили, как узнать имя листа.

Чтобы использовать эту формулу, вам просто нужно ввести формулу в любую ячейку следующим образом: — = Имя рабочего листа (). Функция вернет имя листа.

image 11

Чтобы создать функцию для имени книги, выполните следующие шаги и код: — * Откройте страницу VBA, нажмите клавишу Alt + F11.

  • Вставьте модуль.

  • Напишите указанный ниже код: —

Function Workbookname()

Workbookname = ThisWorkbook.Name

End Function

Объяснение кода: -: — В приведенном выше коде мы упомянули имя функции, а затем мы определили, как узнать имя книги.

Чтобы использовать эту формулу, вам просто нужно ввести формулу в любую ячейку следующим образом: — = Имя книги (). Функция вернет имя листа.

image 12

Как извлечь первое и последнее слово из ячейки с помощью VBA в Microsoft Excel? У нас есть данные на листе 1, в которых есть несколько адресов, поэтому мы хотим извлечь последнее и первое слово из предложения, ячейки или диапазона, создав пользовательскую функцию через VBA в Excel.

image 13

Сначала мы напишем функцию для извлечения первого слова. Пожалуйста, выполните следующие шаги: — * Откройте страницу VBA, нажмите клавишу Alt + F11.

Вставить модуль. Введите указанный ниже код *: —

Function GETFW(Text As String, Optional Separator As Variant)

Dim FW As String

If IsMissing(Separator) Then

Separator = " "

End If

FW = Left(Text, InStr(1, Text, Separator, vbTextCompare))

GETFW = Replace(FW, Separator, "")

End Function

Пояснение кода: — В вышеупомянутом коде мы упомянули имя функции с переменными. Затем мы определили критерии для извлечения слова из предложения или ячейки.

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

  • Перейти к таблице Excel.

  • Используйте эту формулу в ячейке D9.

  • = GETFW (C9) и нажмите Enter.

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

image 14

  • Теперь напишем код для извлечения последнего слова из ячейки.

Следуйте приведенному ниже коду: — * Откройте страницу VBA, нажмите клавишу Alt + F11.

  • Вставьте модуль.

Напишите указанный ниже код *: —

Function GETLW(Text As String, Optional Separator As Variant)

Dim LW As String

If IsMissing(Separator) Then

Separator = " "

End If

LW = StrReverse(Text)

LW = Left(lastword, InStr(1, LW, Separator, vbTextCompare))

GETLW = StrReverse(Replace(LW, Separator, ""))

End Function

Теперь узнаем, как пользоваться этой формулой. Выполните следующие шаги: — * Перейти к таблице Excel.

  • Используйте эту формулу в ячейке D9.

  • = GETLW (C9) Нажмите Enter.

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

image 15

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

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

Excel