Microsoft Excel:

  Таблицы и VBA. Справочник.
  Вопросы и Ответы. Советы. Примеры.
Меню FAQ | Макросы | Функции & Формулы


Rambler's Top100


Counter CO.KZ


    [1] [2]

  1. Как в Excel 97 воспользоваться некоторыми функциями VB, которые появились в Excel 2000 ? 09.03.2009
  2. Как в Excel 2000, XP, 2003 воспользоваться функциями из надстройки "Пакет анализа", причём, без подключения этой надстройки ? 04.04.2011
  3. Как загрузить XLL и зарегистрировать все функции и команды, содержащиеся в этом файле, а также получить список всех зарегистрированных функций ? 08.04.2011
  4. Как в формуле преобразовать все ссылки в абсолютные/относительные ? 26.04.2014
  5. Как в определённом диапазоне заменить все ссылки на относительные ? 27.07.2016
    [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]
    Воспроизведение любых опубликованных здесь материалов возможно только с письменного разрешения автора : Microsoft Excel 95, 97, 2000, XP

    © 2004-2016 Климов П.Ю. Все права защищены. WebDesign & Error's Klimoff