Cara membuat otomatisasi Visual Basic Add-in untuk Excel Worksheet fungsi

Di Microsoft Excel 2000, Anda tidak dapat memanggil fungsi langsung dalam Component Object Model (COM) Add-in dari worksheet sel Formula. Sebaliknya, Anda harus membuat Visual Basic for Applications (VBA) pembungkus untuk Tambah-in COM fungsi sehingga fungsi dapat disebut secara tidak langsung.

Integrasi dari COM Add-in (disebut otomatisasi Add-ins) di Excel 2002 dan kemudian telah ditingkatkan sehingga COM Tambah-in fungsi mungkin sekarang dipanggil secara langsung dari formula lembar kerja tanpa memerlukan pembungkus VBA. Artikel ini menggambarkan cara membuat otomatisasi Add-in yang dengan Visual Basic yang memperlihatkan fungsi yang dapat disebut dari Excel 2002 dan kemudian lembar kerja rumus.

Membuat sampel otomatisasi Add-In

1. Dalam Visual Basic, memulai sebuah proyek AddIn baru.
2. Secara default, bentuk yang bernama frmAddIn ditambahkan ke proyek. Untuk keperluan demonstrasi ini, yang dapat dihapus dari proyek. Dalam proyek Explorer, klik kanan bentuk, dan kemudian klik Menghapus frmAddIn pada menu jalan pintas.
3. Pada Project menu, klik MyAddin properti. Perubahan Nama proyek "AutomationAddin" dan kemudian klik Oke.
4. Dalam proyek Explorer, pilih Connect desainer. Perubahan yang Nama properti untuk "XLFunctions".
5. Dalam proyek Explorer, klik dua kali XLFunctions desainer. Pada General tab, membuat perubahan berikut ke pengaturan desainer:
   • Dari Aplikasi Daftar, pilih Microsoft Excel.
   • Dari Versi aplikasi Daftar, pilih Microsoft Excel 10,0.
     Catatan: Ketika Anda menggunakan Microsoft Office Excel 2003, pilih Microsoft Excel 11,0 dari Versi aplikasi daftar.
   • Perubahan Awal beban perilaku pengaturan untuk Beban pada permintaan.
6. Dengan desainer XLFunctions masih terbuka, pilih Kode dari Lihat menu. Ganti kode dalam modul dengan berikut:

   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. Membangun Add-in sebagai AutomationAddin.dll.

Menggunakan contoh otomatisasi Add-in di Microsoft Excel 2002 atau Microsoft Office Excel 2003

1. Mulai Microsoft Excel 2002 jika Anda Microsoft Excel 10,0 dalam Versi aplikasi Daftar Visual Basic Add-dalam proyek.
   Catatan: Memulai Microsoft Office Excel 2003 jika Anda Microsoft Excel 11,0 dalam Versi aplikasi Daftar Visual Basic Add-dalam proyek.
2. Pada Alat menu, klik Add-in untuk menampilkan Add-In Manager kotak dialog. Klik Otomatisasi, pilih AutomationAddin.XLFunctions dalam daftar, dan kemudian klik Oke. Pilih AutomationAddin.XLFunctions, lalu klik Oke untuk menutup Add-in Manager kotak dialog.
3. Pada sel A1, ketik rumus berikut:
   =TickCount()
   Rumus kembali sejumlah besar yang mewakili jumlah milidetik yang berlalu sejak sistem Anda dimulai.
4. Dalam sel B1 dan B2 buku kerja baru, ketik angka nilai-nilai 2 dan 5, masing-masing.
5. Dalam sel B3, ketik rumus berikut:
   = Add1(B1, B2)
   dan tekan ENTER. Rumus kembali "jumlah dari 2 dan 5 adalah 7 ".
6. Dalam sel B4, ketik rumus berikut:
   = Add2(B1, B2)
   dan tekan ENTER. Rumus kembali "jumlah dari 2 dan 5 adalah 7". Mengubah formula untuk menggunakan argumen opsional ketiga:
   = Add2(B1, B2, 10)
   Rumus kembali "jumlah dari 2, 5, dan 10 adalah 17."
7. Dalam sel B5, ketik rumus berikut:
   =Add3(1,2,3,4,5,6)
   dan tekan ENTER. Rumus kembali "jumlah adalah 21". Karena parameter Add3 fungsi dinyatakan sebagai ParamArray, Anda dapat menggunakan sejumlah variabel argumen untuk fungsi ini.
   
   Catatan Excel batas fungsi satu lembar kerja 29 argumen.
8. Dalam sel B6, ketik rumus berikut:
   =Add1("x","y")
   dan tekan ENTER. Rumus kembali #NUM! (xlErrNum) karena konversi tipe dengan CDbl fungsi gagal mengkonversi string "x" dan "y" untuk mengetik ganda. Default mengembalikan nilai ketika fungsi pertemuan run-time kesalahan adalah #VALUE!. Jika Anda ingin kembali kesalahan yang berbeda, gunakan penanganan kesalahan dan Anda dapat kembali salah satu (nilai built-in kesalahan ExcelxlErrDiv0, xlErrNA, xlErrName, xlErrNull, xlErrNum, xlErrRef, atau xlErrValue.)
9. Pilih sel-sel E1:G5. Ketik rumus berikut:
   =ReturnArray(5,3)
   dan tekan CTRL + SHIFT + ENTER untuk masukkan rumus sebagai array Formula. Fungsi mengembalikan array 5 x 3 nilai-nilai yang unik.
10. Masukkan nomor ke sel-sel I1:J3. Dalam sel I4, ketik rumus berikut:
    =GetArray(I1:J3)
    Rumus akan mengembalikan hasil yang mirip dengan "jumlah yang n"(di mana n adalah jumlah dari angka-angka di I1:J3). Dalam I5, ketik berikut rumus:
    =GetArray({1,2,3,4})
    dan tekan ENTER. Rumus kembali "jumlah adalah 10".

Fungsi mengikat

Mengikat untuk fungsi-fungsi dalam otomatisasi Add-in adalah pada akhir fungsi mengikat didahulukan. Jika Anda memiliki fungsi dalam otomatisasi Anda Add-in dengan nama yang sama sebagai fungsi built-in Excel, Excel built-in fungsi akan diutamakan. Fungsi VBA di buku kerja dan biasa Add-ins (.xla) juga diutamakan otomatisasi Add-dalam fungsi. Bila Anda membuat fungsi Anda sendiri untuk digunakan dalam Excel, dianjurkan bahwa Anda tidak memberikan nama fungsi yang sudah digunakan oleh Excel built-in fungsi.

Khusus memanggil fungsi dalam otomatisasi Add-in, Anda dapat menelusuri ke fungsi dalam formula menggunakan sintaks seperti ServerName.ClassName.FunctionName(...). Sebagai contoh, untuk memanggil Add1 fungsi dalam contoh, Anda bisa menggunakan berikut: Anda hanya dapat top-level metode dan properti dari Anda Otomatisasi Add-in; Anda tidak bisa berjalan menyusuri object model Anda Add-in untuk memanggil fungsi yang tidak berada di tingkat atas.

Fungsi volatil

Fungsi volatil adalah salah satu yang recalculates setiap kali perubahan dibuat untuk setiap sel pada lembar kerja, terlepas dari apakah atau tidak berubah sel adalah sebuah dependensi untuk fungsi. Salah satu contoh fungsi volatil yang internal untuk Excel adalah RAND() fungsi. Menghitung stabil fungsi juga ulang ketika Anda menekan F9.

Untuk membuat fungsi dalam otomatisasi Add-in yang mudah menguap, panggilan Stabil metode Excel Aplikasi objek. Sebagai contoh kode di atas menunjukkan, referensi untuk Excel Aplikasi objek mungkin diperoleh selama peristiwa OnConnection Menambahkan-masuk TickCount fungsi dalam sampel otomatisasi Tambah-in volatil. Perhatikan bahwa Jika Anda membuat perubahan ke setiap sel pada lembar kerja atau Anda tekan F9, sel-sel yang berisi rumus berikut: akan menghitung ulang.

Otomatisasi tambahan dan Add-in Manager

Dalam tambahan manajer, nilai default HKEY_CLASSES_ROOT\<progid> kunci registri untuk Add-in otomatisasi digunakan untuk Add-in's name. Perhatikan bahwa tidak ada properti dalam pengguna Visual Basic Interface (UI) yang akan menetapkan nilai default kunci ini; Namun, kunci ini dapat diubah secara manual di registry editor atau selama instalasi untuk Add-in<b00> </b00> </progid>

Deskripsi untuk otomatisasi Add-in di tambahan Manajer akan selalu menjadi Add-in's ProgID; tidak ada cara untuk mengubah hal ini perilaku.

Otomatisasi tambahan dan fungsi Wizard

Setiap otomatisasi Tambah-in memiliki kategori sendiri dalam fungsi Excel Wizard. Nama kategori ini ProgID untuk Add-in; Anda tidak dapat menentukan nama kategori yang berbeda untuk otomatisasi Add-dalam fungsi. Selain itu, ada tidak ada cara untuk menentukan fungsi deskripsi, deskripsi argumen atau bantuan untuk Otomatisasi tambahan fungsi dalam fungsi Wizard.

Untuk informasi tambahan, klik nomor artikel di bawah ini untuk melihat artikel pada Basis Pengetahuan Microsoft: Untuk selengkapnya, lihat Microsoft berikut Situs web:

(c) Microsoft Corporation 2001, All Rights Reserved. Kontribusi oleh Lori B. Turner, Microsoft Corporation.