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
  6. Как при выделении ячейки с формулой, автоматически "скопировать" эту формулу в буфер обмена ? 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

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