Table of Content
I wish to create a C# COM object dll which I can call from Excel VBA code.
I wish to create a C# COM object dll which I can call from Excel VBA code.
1、新建一个类库工程
2、删除默认的类.vb
3、新建一个类-COM类
4、编辑类的代码并保存
5、注册DLL
新建一个bat文件：
set p=XXX.dll
set w=C:\Windows\
copy %p%.dll %w%%p%.dll
C:\Windows\Microsoft.NET\Framework\v4.0.30319\RegAsm.exe %w%%p%.dll /tlb:%w%%p%.tlb /codebase
pause
6、注册完成后调用
假如我的（工程名：=阿亮）（类名：myDLL）
（1）引用法
Dim a as new myDLL
（2）后期绑定
Set a =createobject("阿亮.myDLL")
step 1：Office installer
Office PIA: Microsoft.Office.Interop.Excel.dll，还有~access.dll,~word.dll。Microsoft.Office.Core等。
PIA是在安装Office的时候选择安装的，而且必须有.NET Framework才能使用
step 2:如果要操作word和excel，需要引入word和excel的库.
右键点击工程名，选择“属性”，点击“引用”，打开如下界面：
step 3:需要导入 Office的Interop命名空间，因为word和excel的类库分别在Microsoft.Office.Interop命名空间下。
Office程序是非托管语言编写的，C#之类的托管语言要去操作非托管语言编写的程序资源就要解决数据交换的格式和结构问题，微软采用Interop(互操作程序集)来解决这个问题
本文适用任何VS系列工具。
什么是Com对象
ActiveX DLL just means COM DLL - marketing people called COM "ActiveX" in the late 1990s.COM即组件对象模型，是Component ObjectModel 取前三个字母的缩写。
Windows使用DLLs在二进制级共享代码。但DLLs是针对C接口而写的，它们只能被C或理解C调用规范的语言使用。由编程语言来负责实现共享代码，而不是由DLLs本身。这样的话DLLs的使用受到限制。
MFC引入了另外一种MFC扩展DLLs二进制共享机制。但它的使用仍受限制――只能在MFC程序中使用。
C++ ATL只能用于C++。
COM通过定义二进制标准解决了这些问题，即COM明确指出二进制模块（DLLs和EXEs）必须被编译成与指定的结构匹配。这个标准也确切规定了在内存中如何组织COM对象。COM定义的二进制标准还必须独立于任何编程语言（如C++中的命名修饰）。一旦满足了这些条件，就可以轻松地从任何编程语言中存取这些模块。由编译器负责所产生的二进制代码与标准兼容。这样使后来的人就能更容易地使用这些二进制代码。
COM对象在内存中表现为组件对象类（coclasss）的一个实例。COM服务器是包含了一个或多个coclass的二进制（DLL或EXE）
COM注册（Registration）是创建注册表入口的一个过程，告诉Windows 操作系统COM服务器放在什么位置。取消注册（Unregistration）则相反――从注册表删除这些注册入口。
GUID（谐音为“fluid”，意思是全球唯一标示符――globally unique identifier）是个128位的数字。它是一种独立于COM编程语言的标示方法。每一个接口和coclass有一个GUID。因为每一个GUID都是全球唯一的，所以避免了名字冲突（只要你用COM API创建它们）。有时你还会碰到另一个术语UUID（意思也是全球唯一标示符――universally unique identifier）。UUIDs和GUIDs在实际使用时的用途是一样的。
Com是微软提出来的在组件程序之间进行交互的标准，以Excel为例：application，workbook，workbooks，sheet，sheets，range等等都是Com对象。
在用C#创建COM组件时，一定要记住以下几点：
1。所要导出的类必须为公有；
2。所有属性、方法也必须为公有；
3。要导出的属性、方法必须用接口方式；如果没有在接口中声明，即使该方法（属性）为公有，也不能正常导出到COM。但他们可以被别的.NET程序所使用；
4。所有的事件也必须用接口方式；
detail:vs2008 vba DLL good
https://www.cnblogs.com/crhdyl/p/5288063.html
5.释放Com对象
Office程序是非托管语言编写的，C#之类的托管语言要去操作非托管语言编写的程序资源就要解决数据交换的格式和结构问题，微软采用Interop(互操作程序集)来解决这个问题。当托管语言访问非托管语言组件的时候一般通过Com方式进行，CLR会给每个COM对象按每进程生成一个RCW（Runtime Callable Wrappers运行时可调用包装器），并用计数器记录Com对象被引用的次数，每引用一次，计数器加1；每释放一次，计数器减1。这种RCW包装会带来额外的资源开销，当计数器为0的时候，RCW资源才会被释放。所以，如果不释放Com对象，RCW（与此类似的还有CCW,COM Callable Wrapper）会造成相关内存始终被占用，即使Com对象消失，内存也不会被释放，这就是所谓的内存泄漏。
detail：https://www.cnblogs.com/Charltsing/p/RealeaseComObject.html
Difference between COM DLL and ActiveX control
.dll,activeX,atl,com之间是什么关系？
Few similarities
Both cannot execute by themself. It needs some program to instantiate it.
Difference
1）Standard dll是动态链接库，是文件，是组件的载体。
2）COM just means Component Object Model.COM is a system-level standard and provides model services to facilitate the construction of higher-level functionality.
com只是指是组件对象模型。COM是一个系统级标准，它提供模型服务来促进更高级功能的构建。
ActiveX DLL
An ActiveX DLL’s code is executed within the main program’s address space. It behaves as if the class was created within the main program’s code. Because the code lies inside the program’s address space, calling methods is very fast.
ActiveX EXE
An ActiveX EXE’s code is run in a separate process. When the main program calls an ActiveX EXE’s method, the.
see:https://rdtk.net/software/how-you-can-convert-dll-to-activex/
3）OLE and ActiveX are high-level services built on top of the COM foundation.
COM+、OLE和DCOM OLE、ActiveX是在COM基础之上构建的高级服务。
很多com是做在.dll文件内的，activeX一定是com，.ocx内有几个函数可以象.dll的函数（一样调用，其实包含com的.dll也有这几个函数，如DllGetClassObject,DllCanUnloadNow,DllRegisterServer等
atl:ATL is the Microsoft C++ library which primarily implements COM base.
Active Template Library,alt模板库。vs2019 c++用来开发com的一套模板。
activex是能用于不同应用程序的封装类。
COM Add-in
A COM Add-in uses the IDTExtensibility2 interface to communicate with an Office application.
Classic Office add-ins are based on the IDTExtensibility2 interface (COM). They can't be run on any device or web browser. Office COM add-ins are for the desktop editions of MS Office (x86 and x64). COM add-ins can be implemented using a wide range of programming languages: from unmanaged C++ or Delphi to managed ones (C#, VB.NET and etc.).
COM Add-ins present the developer with a way to extend the functionality of Office applications for custom tasks. COM Add-ins are typically used to automate Excel in response to a click of a CommandBar button, a form or dialog box, or some other event specific to Excel such as opening or closing workbooks or entering data on worksheets. COM Add-in functions cannot be directly called from cell formulas in worksheets.
A COM Add-in is an in-process COM server (an ActiveX DLL) that must implement the IDTExensibility2 interface. All COM Add-ins must implement each of the five methods of this interface: OnConnection, OnStartupComplete, OnAddinsUpdate, OnBeginShutDown, and OnDisconnection.
When a COM Add-in is installed on a user's system, registry entries are created for the Add-in. In addition to normal COM registration, a COM Add-in is registered for each Office application in which it runs. COM Add-ins used by Excel are registered in the following registry key:
Automation Add-ins
Excel supports Automation Add-ins. Automation Add-ins build on COM Add-ins in that functions in Automation Add-ins can be called from formulas in Excel worksheets.
COM Add-ins must be in-process COM servers that support the IDTExtensibility2 interface; however, Automation Add-ins can be in-process or out-of-process COM servers and implementation of IDTExtensibility2 is optional.
VSTO Addin
any other Office Add-in library such as Add-in Express bases on and extends this interface.
For example, VSTO
.Net 5.x
Unlike ASP.NET Core 2.0,
ASP.NET Core 3.0 and over removed the option to run on .NET Full Framework 4.x. It only runs on .NET Core, meaning you can no longer fall back to .NET Full Framework 4.x for COM Interop support.
Visual Studio 2019
1.Windows 10 and Office 365 / 2016 using VS2019.
Hello, I have a massive amount of office automation that works but have just upgraded to a newly staged Windows 10 and Office 365 / 2016 using VS2019. and get the following error: ActiveChart = 'DirectCast(MyExcel, Microsoft.Office.Interop.Excel.ApplicationClass).ActiveChart' threw an exception of type 'System.Runtime.InteropServices.COMException' on the open of second file.
this below code works on Windows 10 and Office 2016 and VS2019 what am I missing?
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel
Public Class Form1
Dim LoadDir As String
Dim MyExcel As New Excel.Application
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
LoadDir = Environment.CurrentDirectory() & "\"
End Sub
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
If System.IO.File.Exists(LoadDir & "test.xls") = False Then
MsgBox("Could Not find, test.xls", vbExclamation, "Oops!")
Exit Sub
End If
MyExcel.Workbooks.Open(LoadDir & "test.xls")
MyExcel.Sheets("Sheet1").activate()
MyExcel.Visible = False
MyExcel.Range("A1").Activate()
MyExcel.Range("A1").NumberFormat = "0"
MyExcel.ActiveCell.Formula = "=A2"
TextBox1.Text = MyExcel.Range("A1").Text
MyExcel.ActiveWorkbook.Close(SaveChanges:=False)
MyExcel.Quit()
If System.IO.File.Exists(LoadDir & "test2.xls") = False Then
MsgBox("Could Not find, test2.xls", vbExclamation, "Oops!")
Exit Sub
End If
MyExcel.Workbooks.Open(LoadDir & "test2.xls")
MyExcel.Sheets("Sheet1").activate()
MyExcel.Visible = False
MyExcel.Range("A1").Activate()
MyExcel.Range("A1").NumberFormat = "0"
MyExcel.ActiveCell.Formula = "=A2"
TextBox2.Text = MyExcel.Range("A1").Text
MyExcel.ActiveWorkbook.Close(SaveChanges:=False)
MyExcel.Quit()
If System.IO.File.Exists(LoadDir & "test3.xls") = False Then
MsgBox("Could Not find, test3.xls", vbExclamation, "Oops!")
Exit Sub
End If
MyExcel.Workbooks.Open(LoadDir & "test3.xls")
MyExcel.Sheets("Sheet1").activate()
MyExcel.Visible = False
MyExcel.Range("A1").Activate()
MyExcel.Range("A1").NumberFormat = "0"
MyExcel.ActiveCell.Formula = "=A2"
TextBox3.Text = MyExcel.Range("A1").Text
MyExcel.ActiveWorkbook.Close(SaveChanges:=False)
MyExcel.Quit()
End Sub
Runtime.InteropServices.Marshal.ReleaseComObject(MyExcel.Workbooks)
End Class
I think I have this fixed. add the below line after the MyExcel.quit()
Runtime.InteropServices.Marshal.ReleaseComObject(MyExcel.Workbooks)
Visual Studio 2015
In the Win 7/VS2015/Excel 2013 environment everything works fine.
Using Win 7/Visual Studio 2015 the following very simple code compiles as a C# class library and creates a COM object that I can call successfully from Excel 2013 VBA:
using System.Runtime.InteropServices;
namespace TestLib {
[ComVisible(true)]
[ClassInterface(ClassInterfaceType.AutoDual)]
public class C_Hello {
public string Hello() {
return "Hello";
}
}
}In the Assembly Info I have ticked the checkbox "Make the Assembly COM Visible" and in the Build info I have ticked the checkbox "Register for COM interop"
The Excel 2013 VBA code is equally simple:
Sub Test()
Dim x As TestLib.C_Hello
Set x = New TestLib.C_Hello
ActiveSheet.Range("C3").Value = x.Hello
End Subwhere TestLib is the C# COM module created in Visual Studio.
Step by step (chinese)
http://club.excelhome.net/thread-1544291-1-1.html (OK)
Create a DLL by CSharp or VB.Net for VBA Step by step (English)
good
Visual Studio 2010
用Microsoft Visual Studio 2010封装EXCEL VBA为DLL_COM组件
Visual Studio 2008
https://www.cnblogs.com/crhdyl/p/5288063.html
vs2008 vba DLL good
Microsoft Visual Basic 6.0
使用程序：
1、Microsoft Office Excel 2003
2、Microsoft Visual Basic 6.0
https://blog.csdn.net/qwlovedzm/article/details/44261101
案列
考场编排系统下载地址 vbe+dll+excel
Where can I download VB6?
Download Visual Basic 6
https://drive.google.com/uc?export=download&id=12j0QR6pRjNx0XCHwLRQJGaxyWfO02ZAo
Download the VB6 file
Extract the zip file
Run ALL msi files in the extracted folder
To launch vb6 go to C:\Program Files (x86)\Microsoft Visual Studio\VB98\VB6.EXE
Useful links
用VB.NET(Visual Basic 2010)封装EXCEL VBA为DLL_COM组件