Excel | FAQ | Макросы (VBA)| Функции, Формулы [2]

Microsoft Excel:

Таблицы и VBA. Справочник.
Вопросы и Ответы. Советы. Примеры.

Меню FAQ | Макросы | Функции & Формулы

[1] [2]

Как в Excel 97 воспользоваться некоторыми функциями VB, которые появились в Excel 2000 ? 09.03.2009
Как в Excel 2000, XP, 2003 воспользоваться функциями из надстройки "Пакет анализа", причём, без подключения этой надстройки ? 04.04.2011
Как загрузить XLL и зарегистрировать все функции и команды, содержащиеся в этом файле, а также получить список всех зарегистрированных функций ? 08.04.2011
Как в формуле преобразовать все ссылки в абсолютные/относительные ? 26.04.2014
Как в определённом диапазоне заменить все ссылки на относительные ? 27.07.2016
Как при выделении ячейки с формулой, автоматически "скопировать" эту формулу в буфер обмена ? ^NEW 26.05.2018
[1] [2]

Ответ : ^{Актуально только для MS Excel 97}

Если в процессе работы с MS Excel 97 (или более ранней версией) возникла необходимость в использовании некоторых VB функций, а именно Split, Replace, Join, Reverse, InStrRev которые впервые появились только в MS Excel 2000, то Вы можете воспользоваться их аналогами, которые опубликованы на сайте Microsoft.

How To Simulate Visual Basic 6.0 String Functions in VB5

Ответ : ^{Актуально только для MS Excel 2000, XP, 2003}

Если у Вас возникла необходимость в программном использовании функций, которые, как правило, доступны, только после подключения надстройки "Пакет анализа", но, при этом, Вы не хотите подключать эту надстройку или открывать файл ATPVBAEN.XLA, то начиная с Excel 2000 для решения этой задачи можно использовать OWC - Office Web Components

Несколько примеров использования :

1) Получение случайного числа от -100 до 100 =СЛУЧМЕЖДУ(-100; 100)

iResult = CreateObject("OCATP.OCATP.1").RANDBETWEEN(-100, 100)

2) Получение даты, отстоящей от текущей даты, на один месяц =ДАТАМЕС(СЕГОДНЯ(); 1)
3) И получение последнего дня текущего месяца =КОНМЕСЯЦА(СЕГОДНЯ(); 0)

With CreateObject("OCATP.OCATP.1") 
     iResult1 = .RANDBETWEEN(-100, 100) 
     iResult2 = CDate(.EDATE(Date, 1)) 
     iResult3 = CDate(.EOMONTH(Date, 0)) 
End With

Если же Вам необходимы подсказки к функциям (количество аргументов, их тип и обязательность), то используйте раннее связывание. Для этого, в редакторе VBA в меню Tools выберите команду References и подключите Microsoft Office Web Components Function Library (MSOWCF.DLL)

Dim iOWCATP As New MSOWCFLib.OCATP 
Dim iDate As Date, iResult As Date 
iDate = Now 'Date 
iResult = iOWCATP.EOMONTH(iDate, 0) 
MsgBox "Последний день этого месяца : " & iResult

Dim iDate As Date, iResult1#, iResult2#, iResult3# 

With New MSOWCFLib.OCATP 
     iDate = #1/31/2007# '39113 
     iResult1 = .EDATE(CDbl(iDate), 1) 
     iResult2 = .CONVERT(451, "F", "C") 
     iResult3 = .WEEKNUM(CDbl(Date), 2) 
     MsgBox "Дата, отстоящая на один месяц от " & iDate & " это " & CDate(iResult1) 
     MsgBox "451 по Фаренгейту, а в цельсиях, это " & iResult2 
     MsgBox "Сейчас идёт неделя# " & iResult3 
End With

Комментарий : Если найти OWC не удаётся, а применение первых советов, приводит к возникновению ошибки, то, по всей видимости, на Вашем компьютере просто нет указанного компонента. Однако, его можно скачать с официального сайта Microsoft.

Download Office XP Tool: Web Components

Download Office 2003 Add-in: Office Web Components

Ответ :

Для того, чтобы загрузить XLL и автоматически зарегистрировать все функции и команды, содержащиеся в этом ресурсе, можно использовать метод RegisterXLL об'екта Application, например :

Application.RegisterXLL Application.LibraryPath & "\Analysis\ANALYS32.XLL"

Application.RegisterXLL FileName:=Application.LibraryPath & "\Analysis\ANALYS32.XLL"

Получить же список всех зарегистрированных функций можно с помощью свойства RegisteredFunctions об'екта Application

Application.RegisterXLL Application.LibraryPath & "\Analysis\ANALYS32.XLL" 

iArray = Application.RegisteredFunctions 
If IsArray(iArray) = True Then 
   Range("A1").Resize(UBound(iArray), 3).Value = iArray 
   Range("A:C").EntireColumn.AutoFit 'Columns("A:C").AutoFit 
Else 
   MsgBox "Странно ... но зарегистрированных функций, нет" 
End If

iFileName$ = Application.LibraryPath & "\Analysis\ANALYS32.XLL" 
Application.RegisterXLL FileName:=iFileName$ 

iArray = Application.RegisteredFunctions 
If Not IsNull(iArray) Then 
   Cells(1, 1).Resize(UBound(iArray), 3).Value = iArray 
Else 
   MsgBox "Зарегистрированных функций, нет" 
End If

iFileName$ = Application.LibraryPath & "\Analysis\ANALYS32.XLL" 

If Application.RegisterXLL(iFileName$) = True Then 
   iArray = Application.RegisteredFunctions 
   Cells(1).Resize(UBound(iArray), 3) = iArray 
Else 
   MsgBox "Загрузки XLL и регистрации функций, не произошло" 
End If

Комментарий : Если стандартная надстройка "Пакет анализа" или "Analysis ToolPak" подключена(загружена), то, фактически, в повторном использовании Application.RegisterXLL "ANALYS32.XLL" нет особого смысла.

Ответ :

Для того, чтобы в формуле преобразовать все абсолютные ссылки в относительные или наоборот, можно использовать метод ConvertFormula об'екта Application, например :

iFormula$ = "=SUM($A$2:$A$100)"

MsgBox Application.ConvertFormula(Formula:=iFormula$, FromReferenceStyle:=xlA1, ToReferenceStyle:=xlA1, ToAbsolute:=xlRelative)

MsgBox Application.ConvertFormula(Formula:=iFormula$, FromReferenceStyle:=xlA1, ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsolute)

MsgBox Application.ConvertFormula(Formula:=iFormula$, FromReferenceStyle:=xlA1, ToReferenceStyle:=xlA1, ToAbsolute:=xlRelRowAbsColumn)

MsgBox Application.ConvertFormula(Formula:=iFormula$, FromReferenceStyle:=xlA1, ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsRowRelColumn)

Разумеется этот способ применим и для формул в стиле R1C1, т.е.

iFormula$ = "=SUM(R2C1:R100C1)"

MsgBox Application.ConvertFormula(Formula:=iFormula$, FromReferenceStyle:=xlR1C1, ToReferenceStyle:=xlR1C1, ToAbsolute:=xlRelative)

MsgBox Application.ConvertFormula(Formula:=iFormula$, FromReferenceStyle:=xlR1C1, ToReferenceStyle:=xlR1C1, ToAbsolute:=xlRelative, RelativeTo:=Range("B2"))

Кроме того, данный метод может конвертировать адреса ячеек из стиля A1 в R1C1 и наоборот [FAQ677]

Ответ :

Для того, чтобы во всех формулах определённого диапазона, заменить все ссылки на относительные, можно просто удалить $

Range("A1:C100").Replace "$", "", xlPart

ActiveSheet.UsedRange.Replace "$", "", xlPart

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

Range("A1:C100").SpecialCells(xlFormulas).Replace "$", "", xlPart

ActiveSheet.UsedRange.SpecialCells(xlFormulas).Replace "$", "", xlPart

Если же Вы предпочитаете стиль ссылок R1C1, то :

iRefStyle = Application.ReferenceStyle 
Application.ReferenceStyle = xlA1 
ActiveSheet.UsedRange.SpecialCells(xlFormulas).Replace "$", "", xlPart 
Application.ReferenceStyle = iRefStyle

With Application 
     iRefStyle = .ReferenceStyle 
     .ReferenceStyle = xlA1 
     .Range("A1:C100").SpecialCells(xlFormulas).Replace "$", "", xlPart 
     .ReferenceStyle = iRefStyle 
End With

Если же Вам нужна более сложная конвертация, то обратите внимание на вышеопубликованный [FAQ679]

Ответ :

Если Вам необходимо, сразу после выделения одной ячейки, содержащей формулу, автоматически поместить текст этой формулы в буфер обмена, то скопируйте весь нижеприведённый код в модуль книги ThisWorkbook(ЭтаКнига) и сохраните изменения.

'Необходима ссылка Microsoft Forms 2.0 Object Library 

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) 
    If Target.Count > 1 Then Exit Sub 
    If Not Target.HasFormula Then Exit Sub 

    With New DataObject 
         .SetText Target.FormulaLocal 
         .PutInClipboard 
    End With 
End Sub

или

'Необходима следующая ссылка Microsoft Forms 2.0 Object Library 

Private iClipboard As New MSForms.DataObject 

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) 
    If Target.Count > 1 Then Exit Sub 
    If Not Target.HasFormula Then Exit Sub 

    iClipboard.SetText Target.FormulaLocal 
    iClipboard.PutInClipboard 
End Sub

Комментарий : Если формула окажется скрыта, а рабочий лист Sh защищён, то Вы, разумеется, получите ошибку, которой можно избежать, если добавить соответствующую проверку.

Воспроизведение любых опубликованных здесь материалов возможно только с письменного разрешения автора : Microsoft Excel 95, 97, 2000, XP

WebDesign & Error's Klimoff

Microsoft Excel:

Таблицы и VBA. Справочник. Вопросы и Ответы. Советы. Примеры.

Таблицы и VBA. Справочник.
Вопросы и Ответы. Советы. Примеры.