Como criar um suplemento do Visual Basic Automation para funções de folha de cálculo do Excel

No Microsoft Excel 2000, não pode chamar uma função directamente num suplemento COM (Component Object Model) a partir de uma fórmula da célula de folha de cálculo. Em vez disso, tem de criar do Visual Basic for wrapper Applications (VBA) para a função suplemento para que a função pode ser chamada indirectamente.

Integração de suplementos COM (denominado suplementos de automatização) no Excel 2002 e versões posteriores foi melhorada que funções suplemento agora podem ser chamadas directamente a partir de uma fórmula de folha de cálculo sem a necessidade de um wrapper VBA. Este artigo mostra como criar um suplemento de automatização com Visual Basic que expõe funções que podem ser chamadas a partir do Excel 2002 e posteriores fórmulas de folha de cálculo.

Criar o suplemento de automatização de exemplo

1. No Visual Basic, inicie um novo projecto AddIn.
2. Por predefinição, um formulário com o nome frmAddIn é adicionado ao projecto. Por motivos desta demonstração, esse formulário pode ser removido do projecto. No Explorador de projecto, clique com o botão direito do rato no formulário e, em seguida, clique em Remover frmAddIn no menu de atalho.
3. No menu projecto , clique em Propriedades de MyAddin . Alterar o Nome do projecto para "AutomationAddin" e clique em OK .
4. No Explorador de projecto, seleccione o estruturador de ligar. Altere a propriedade de nome para "XLFunctions".
5. No Explorador de projecto, faça duplo clique no Estruturador XLFunctions . No separador Geral , efectue as seguintes alterações às definições do estruturador:
   • Na lista de aplicações , seleccione Microsoft Excel .
   • Na lista de Versão da aplicação , seleccione Microsoft Excel 10.0 .
     Nota : quando estiver a utilizar o Microsoft Office Excel 2003, seleccione Microsoft Excel 11.0 na lista de Versão da aplicação .
   • Altere a definição Inicial O comportamento para carregar a pedido .
6. Com o XLFunctions estruturador ainda abrir, seleccione o código no menu Ver . 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.

Utilizando o exemplo Automation Add-in no Microsoft Excel 2002 ou Microsoft Office Excel 2003

1. Inicie o Microsoft Excel 2002, se seleccionou o Microsoft Excel 10.0 na lista de Versão da aplicação do projecto do Visual Basic Add-in.
   Nota : Iniciar Microsoft Office Excel 2003 se tiver seleccionado o Microsoft Excel 11.0 na lista de adicionar o Visual Basic versão da aplicação - no projecto.
2. No menu Ferramentas , clique em Add-ins para apresentar a caixa de diálogo Gestor de suplementos . Clique em automatização , seleccione AutomationAddin.XLFunctions na lista e, em seguida, clique em OK . Seleccione AutomationAddin.XLFunctions e, em seguida, clique em OK para fechar a caixa de diálogo Add-in Gestor .
3. Na célula A1, escreva a seguinte fórmula:
   =TickCount()
   A fórmula devolve um grande número que representa o número de milissegundos decorridos desde que o sistema foi iniciado.
4. Nas células B1 e B2 do novo livro, escreva os valores numéricos 2 e 5, respectivamente.
5. Na célula B3, escreva a seguinte fórmula:
   = Add1(B1, B2)
   e prima ENTER. A fórmula devolve "A soma de 2 e 5 é 7".
6. Na célula B4, escreva a seguinte fórmula:
   = Add2(B1, B2)
   e prima ENTER. A fórmula devolve "A soma de 2 e 5 é 7". Modificar a fórmula para utilizar o terceiro argumento opcional:
   = Add2(B1, B2, 10)
   A fórmula devolve "A soma de 2, 5 e 10 é 17."
7. Na célula B5, escreva a seguinte fórmula:
   =Add3(1,2,3,4,5,6)
   e prima ENTER. A fórmula devolve "A soma é 21". Uma vez que o parâmetro para a função Add3 é declarado como ParamArray , é possível utilizar um número variável de argumentos para esta função.
   
   Nota O Excel limita uma função de folha de cálculo único a 29 argumentos.
8. Na célula B6, escreva a seguinte fórmula:
   =Add1("x","y")
   e prima ENTER. A fórmula devolve # núm! ( xlErrNum ) porque a conversão de tipo com a função CDup falha converter as cadeias "x" e "y" escrever duplo . A predefinição devolver o valor quando uma função encontra um erro de tempo de execução é o valor de erro # valor!. Se pretender devolver um erro diferente, processamento de erro de utilização e pode devolver qualquer dos valores de erro incorporadas Excel ( xlErrDiv0 , xlErrNA , xlErrName , xlErrNull , xlErrNum , xlErrRef ou xlErrValue .)
9. Seleccione células E1:G5. Escreva a seguinte fórmula:
   =ReturnArray(5,3)
   e prima CTRL + SHIFT + ENTER para introduzir a fórmula como uma fórmula de matriz. A função devolve uma matriz 5 x 3 de valores exclusivos.
10. Introduza os números em células I1:J3. Na célula I4, escreva a seguinte fórmula:
    =GetArray(I1:J3)
    A fórmula irá devolver um resultado semelhante a "A soma é n" (em que n é a soma de números em I1:J3). Na I5, escreva a seguinte fórmula:
    =GetArray({1,2,3,4})
    e prima ENTER. A fórmula devolve "A soma é 10".

Função de ligação

A associação de funções de um suplemento de automatização é no fim da precedência de enlace de função. Se tiver uma função a automatização do suplemento com o mesmo nome que uma função incorporada do Excel, a função incorporada do Excel terão precedência. Funções VBA em livros e suplementos normais (.xla) também têm precedência sobre Adicionar automatização - nas funções. Quando criar as suas próprias funções para utilização no Excel, recomenda-se que não atribua os nomes de funções já em utilização por funções incorporadas do Excel.

Para chamar especificamente uma função in um suplemento de automatização, poderá pesquisar para baixo para a função numa fórmula, utilizando uma sintaxe como nomeservidor . nome da classe . nomedafunção (...). Por exemplo, para chamar a função Add1 no exemplo, pode utilizar o seguinte: Só pode chamar métodos de nível superior e propriedades do suplemento de automatização; não é possível ajudá-lo para baixo o modelo de objecto do Add-in para chamar funções que não estão no nível superior.

Funções voláteis

Uma função volátil é aquele que volta a calcular sempre que for feita uma alteração a qualquer célula numa folha de cálculo, independentemente da 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 funções RAND() . Funções voláteis recalcular também quando prime F9.

Para tornar volátil uma função num suplemento de automatização, chame o método do objecto do Excel aplicação voláteis . Como o exemplo demonstra a código acima, uma referência ao objecto do Excel aplicações pode ser obtida durante o evento OnConnection de Add-in. A função TickCount exemplo Automation Add-in é volátil. Note que se efectuar alterações a qualquer célula na folha de cálculo ou premir F9, células que contêm a seguinte fórmula: recalcula.

Suplementos de automatização e o Gestor Add-in

No Gestor Add-in, o valor predefinido da chave de registo <progid> HKEY_CLASSES_ROOT\ para o suplemento de automatização é utilizado para nome do suplemento. Note que não existe nenhuma propriedade do Visual Basic interface de utilizador (UI, User Interface) que definirá o valor predefinido desta chave; no entanto, esta chave pode ser modificada manualmente no editor de registo ou durante uma instalação para o suplemento.

A descrição para o Add-in automatização na adicionar - in ' Gestor de ' será sempre ProgID do suplemento; não existe nenhuma forma de alterar este comportamento.

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

Cada suplemento de automatização tem as suas próprias categorias na função de Excel assistente. O nome de categoria é o ID de programa para o adicionar; não é possível especificar um nome de categoria diferente para funções Automation Add-in. Além disso, não é possível especificar descrições de função, descrições de argumento ou ajuda para funções de Automation Add-in no Assistente de função.

Para obter informações adicionais, clique no número de artigo que se segue para visualizar o artigo na Microsoft Knowledge Base: Para mais informações, consulte o seguinte Web site da Microsoft:

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