Como criar um suplemento de automação do Visual Basic para funções de planilha do Excel

No Microsoft Excel 2000, você não pode chamar uma função diretamente um suplemento COM (Component Object Model) de uma fórmula da célula de planilha. Em vez disso, você deve criar um Visual Basic for Applications (VBA) wrapper para a função SUPLEMENTOS de modo que a função pode ser chamada indiretamente.

Integração de suplementos de COM (chamado de suplementos de automação) no Excel 2002 e posterior foi aprimorada, SUPLEMENTOS de funções agora podem ser chamadas diretamente por uma fórmula de planilha sem a necessidade de um wrapper do VBA. Este artigo ilustra como criar um suplemento de automação com o Visual Basic que expõe as funções que podem ser chamadas a partir do Excel 2002 e posteriores fórmulas da planilha.

Criar o suplemento de automação de exemplo

1. No Visual Basic, inicie um novo projeto AddIn.
2. Por padrão, um formulário denominado frmAddIn é adicionado para o projeto. Para fins desta demonstração, esse formulário pode ser removido do projeto. No Explorador de projeto, clique com o botão direito do formulário e, em seguida, clique em Remover frmAddIn no menu de atalho.
3. No menu Project , clique em Propriedades MyAddin . Alterar o Nome do projeto para "AutomationAddin" e clique em OK .
4. No Explorador de projeto, selecione o designer de conectar. Altere a propriedade Name para "XLFunctions".
5. No Explorador de projeto, clique duas vezes o designer XLFunctions . Na guia Geral , faça as seguintes alterações as configurações de designer:
   • Na lista de aplicativos , selecione Microsoft Excel .
   • Na lista a Versão do aplicativo , selecione Microsoft Excel 10.0 .
     Observação : quando você estiver usando o Microsoft Office Excel 2003, selecione Microsoft Excel 11.0 na lista de Versão do aplicativo .
   • Altere a configuração Comportamento de carregamento inicial carregar sob demanda .
6. Com o XLFunctions designer ainda aberta, selecione código no menu Exibir . Substitua o código no módulo com o seguinte:

   Option Explicit
   Private Declare Function GetTickCount Lib "kernel32" () As Long
   
   Dim oApp As Object  'The Excel Application object
   
   Private Sub AddinInstance_OnAddInsUpdate(custom() As Variant)
      Exit Sub
   End Sub
   
   Private Sub AddinInstance_OnConnection(ByVal Application As Object, ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, ByVal AddInInst As Object, custom() As Variant)
      Set oApp = Application
   End Sub
   
   Private Sub AddinInstance_OnDisconnection(ByVal RemoveMode As AddInDesignerObjects.ext_DisconnectMode, custom() As Variant)
      Set oApp = Nothing
   End Sub
   
   Private Sub AddinInstance_OnStartupComplete(custom() As Variant)
      Exit Sub
   End Sub
   
   Public Function TickCount() As Long
      '----------------------------------------------------------------------
      '** A volatile function that is called each time the sheet is calculated.
      '   Call with =TICKCOUNT().
      '----------------------------------------------------------------------
      oApp.Volatile
      TickCount = GetTickCount
   End Function
   
   Public Function Add1(Num1 As Variant, Num2 As Variant) As Variant
      '----------------------------------------------------------------------
       '** A function with two required arguments.
       '   Can be called with formulas such as =Add1(1,3) or =Add1(A1,A2).
      '----------------------------------------------------------------------
       On Error Resume Next
       Add1 = "The sum of " & Num1 & " and " & Num2 & " is " & _
           CDbl(Num1) + CDbl(Num2)
       If Err <> 0 Then Add1 = CVErr(2036)  'xlErrNum = 2036
   End Function
   
   Public Function Add2(Num1 As Variant, Num2 As Variant, Optional Num3 As Variant) As Variant
      '----------------------------------------------------------------------
      '** A function with two required arguments and a third optional argument.
      '   Can be called with formulas such as =Add2(1,2), =Add2(A1,A2,A3).
      '----------------------------------------------------------------------
       Dim Sum As Double, sMsg As String
       On Error GoTo Handler
       Sum = CDbl(Num1) + CDbl(Num2)
       If IsMissing(Num3) Then
           sMsg = "The sum of " & Num1 & " and " & Num2 & " is "
       Else
           Sum = Sum + CDbl(Num3)
           sMsg = "The sum of " & Num1 & ", " & Num2 & " and " & Num3 & " is "
       End If
       Add2 = sMsg & Sum
       Exit Function
   Handler:
       Add2 = CVErr(2036)  'xlErrNum = 2036
   End Function
   
   Public Function Add3(ParamArray Nums()) As Variant
      '----------------------------------------------------------------------
      '** Demonstrates a function with a variable number of arguments.
      '   Can be called with formulas like =Add3(1), =Add3(1,2,3,4),
      '   or =Add3(A1,A2).
      '----------------------------------------------------------------------
       Dim Sum As Double, i As Integer
       On Error GoTo Handler
       For i = 0 To UBound(Nums)
           Sum = Sum + CDbl(Nums(i))
       Next
       Add3 = "The sum is " & Sum
       Exit Function
   Handler:
       Add3 = CVErr(2036)  'xlErrNum = 2036
   End Function
   
   Public Function ReturnArray(nRows As Long, nCols As Long) As Variant
      '----------------------------------------------------------------------
      '** Demonstrates how to return an array of values (for use in Excel
      '   "array formulas").
      '   Can be called with a formula such as =ReturnArray(1,3).
      '----------------------------------------------------------------------
       On Error GoTo Handler
       ReDim a(0 To nRows, 0 To nCols) As Variant
       Dim r As Long, c As Long
       For r = 0 To nRows - 1
           For c = 0 To nCols - 1
               a(r, c) = "r" & r + 1 & "c" & c + 1
           Next c
       Next r
       ReturnArray = a
       Exit Function
   Handler:
       ReturnArray = CVErr(2015)  'xlErrValue = 2015
   End Function
   
   Public Function GetArray(Nums As Variant) As Variant
      '----------------------------------------------------------------------
      '** Demonstrates how to use an array(or range of multiple cells) as
      '   a function argument.
      '   Can be called with formulas such as =GetArray(A1:B5), GetArray(A1),
      '   or GetArray({1,2,3;4,5,6}).
      '----------------------------------------------------------------------
       Dim Sum As Double, v As Variant
       On Error GoTo Handler
       If IsArray(Nums) Then
           For Each v In Nums
               Sum = Sum + CDbl(v)
           Next
       Else
           Sum = CDbl(Nums)
       End If
       GetArray = "The sum is " & Sum
       Exit Function
   Handler:
       GetArray = CVErr(2036)  'xlErrNum = 2036
   End Function
   
   					

7. Crie o suplemento como AutomationAddin.dll.

Usando o exemplo de automação Add-in no Microsoft Excel 2002 ou Microsoft Office Excel 2003

1. Inicie o Microsoft Excel 2002, se você tiver selecionado Microsoft Excel 10.0 na lista de projeto Visual Basic Add-in Versão do aplicativo .
   Observação : iniciar o Microsoft Office Excel 2003 se você tiver selecionado Microsoft Excel 11.0 na lista de adicionar o Visual Basic Application versão - no projeto.
2. No menu Ferramentas , clique em Add-ins para exibir a caixa de diálogo Gerenciador de suplementos . Clique em automação , selecione AutomationAddin.XLFunctions na lista e, em seguida, clique em OK . Selecione AutomationAddin.XLFunctions e, em seguida, clique em OK para fechar a caixa de diálogo Add-in Gerenciador .
3. Na célula A1, digite a seguinte fórmula:
   =TickCount()
   A fórmula retorna um grande número que representa o número de milissegundos transcorridos desde que o sistema foi iniciado.
4. Nas células B1 e B2 da nova pasta de trabalho, digite os valores numéricos 2 e 5, respectivamente.
5. Na célula B3, digite a seguinte fórmula:
   = Add1(B1, B2)
   e pressione ENTER. A fórmula retorna "A soma de 2 e 5 é 7".
6. Na célula B4, digite a seguinte fórmula:
   = Add2(B1, B2)
   e pressione ENTER. A fórmula retorna "A soma de 2 e 5 é 7". Modificar a fórmula para usar o terceiro argumento opcional:
   = Add2(B1, B2, 10)
   A fórmula retorna "A soma de 2, 5 e 10 é 17".
7. Na célula B5, digite a seguinte fórmula:
   =Add3(1,2,3,4,5,6)
   e pressione ENTER. A fórmula retorna "A soma é 21". Porque o parâmetro para a função Add3 é declarado como ParamArray , você pode usar um número variável de argumentos para esta função.
   
   Observação Excel limita uma função de planilha único a 29 argumentos.
8. Na célula B6, digite a seguinte fórmula:
   =Add1("x","y")
   e pressione ENTER. A fórmula retorna # núm! ( xlErrNum ) porque a conversão de tipo com a função CDbl falhar converter cadeias de caracteres "x" e "y" para digitar dupla . O padrão de retorno quando uma função encontra um erro em tempo de execução de valor é # valor!. Se você desejar retornar um erro diferente, use manipulação de erros e você pode retornar qualquer dos valores de erro internas Excel ( xlErrDiv0 , xlErrNA , xlErrName , xlErrNull , xlErrNum , xlErrRef ou xlErrValue .)
9. Selecionar as células E1:G5. Digite a seguinte fórmula:
   =ReturnArray(5,3)
   e pressione CTRL + SHIFT + ENTER para inserir a fórmula como uma fórmula de matriz. A função retorna uma matriz 3 x 5 de valores exclusivos.
10. Insira os números em células I1:J3. Na célula I4, digite a seguinte fórmula:
    =GetArray(I1:J3)
    A fórmula retornará um resultado semelhante a "A soma é n" (onde n é a soma dos números da I1:J3). Na I5, digite a seguinte fórmula:
    =GetArray({1,2,3,4})
    e pressione ENTER. A fórmula retorna "A soma é 10".

Ligação de função

Ligação para funções em um suplemento de automação está no final de precedência de ligação a função. Se você tiver uma função em sua automação Add-in com o mesmo nome de uma função interna do Excel, a função interna do Excel terão precedência. Funções do VBA em pastas de trabalho e suplementos regulares (.xla) também têm precedência sobre automação Add - in funções. Quando você cria suas próprias funções para uso no Excel, é recomendável que você não fornecer seus nomes de funções já estão em uso por funções internas do Excel.

Para chamar uma função especificamente em um suplemento de automação, pode aprofundar em função em uma fórmula usando uma sintaxe como NomeDoServidor . ClassName . nomedafunção (...). Por exemplo, para chamar a função Add1 no exemplo, você pode usar o seguinte: Você só pode chamar métodos de nível superior e propriedades do seu suplemento de automação; você não pode orientá-lo para baixo o modelo de objeto do seu suplemento para chamar funções que não estão no nível superior.

Funções voláteis

Uma função volátil é aquele que é recalculada sempre que uma alteração é feita para qualquer célula em uma planilha, independentemente de estarem ou não a célula alterada é uma dependência para a função. Um exemplo de uma função volátil é interno para o Excel é a função de funções . Funções voláteis também recalcular quando você pressionar F9.

Para fazer uma função em um suplemento de automação volátil, chame o método volátil do objeto Application do Excel. Como o exemplo de código acima demonstra, uma referência ao objeto de Excel aplicativo pode ser recuperada durante o evento OnConnection do Add-in. A função TickCount no exemplo de automação Add-in é volátil. Observe que se você fizer alterações para qualquer célula na planilha ou pressionar F9, células que contêm a fórmula a seguir: Recalcular o será.

Suplementos de automação e o Gerenciador de suplemento

No Gerenciador de suplemento, o valor padrão da chave do Registro <progid> HKEY_CLASSES_ROOT\ para o suplemento de automação é usado para nome do suplemento. Observe que não há nenhuma propriedade na interface de usuário (UI) do Visual Basic que definirá o valor padrão dessa chave; no entanto, essa chave pode ser modificada manualmente no editor do registro ou durante uma instalação para o suplemento.

A descrição para o Add-in de automação na adicionar - no Gerenciador de sempre será ProgID do suplemento; não há nenhuma maneira de alterar esse comportamento.

Suplementos de automação e o Assistente de função

Cada suplemento de automação tem sua própria categoria na função do Excel Assistente. O nome da categoria é o ProgID para o suplemento; você não pode especificar um nome de categoria diferente para funções de suplemento de automação. Além disso, não há nenhuma maneira de especificar descrições de função, descrições de argumento ou Ajuda para o suplemento de automação funções no Assistente de função.

Para obter informações adicionais, clique no número abaixo para ler o artigo na Base de dados de Conhecimento da Microsoft: Para obter mais informações, consulte o seguinte site:

(c) Microsoft Corporation 2001, todos os direitos reservados. Contribuições de Lori B. Turner, Microsoft Corporation.