Let us summarize some of the big advantages VSTO has over VBA:
- Works with your favorite language: VB, c++et,c .
- Uses more powerful forms with expanded potential.
- Improves access to data residing on a server (SQL and ADO).
- Enhances communication with Web Servers.
- Protects users with better security.
- Protects code by hiding it from view and preventing inadvertent, inept changes.
- Improves the way you deploy new code and future updates to other users.
The code you create for Excel in VSTO is not located inside the document (as it is with VBA), but rather it is a separate DLL file (Dynamic-Link-Library)
These four core objects are:
- Excel. Application
- Excel. Workbook
- Excel. Worksheet
- Excel. Range
-
NarnedRange is an extension of the Range object
-
Listobject is a range that extends multiple columns and rows.
-
Chart is an extension of the Chart object.
Ribbon XML
Defining Callback Methods
Define your callback methods in the Ribbon class in the Ribbon code file. A callback method has several requirements:
-
It must be declared as public.
-
Its name must match the name of a callback method that you assigned to a control in the Ribbon XML file.
-
Its signature must match the signature of a type of callback method that is available for the associated Ribbon control.
Ribbon tab ControlType: Custom, Office
How to: Change the Position of a Tab on the Ribbon
http://msdn.microsoft.com/en-us/library/bb608600.aspx
Value and Value2
The only difference between the Value and the Value2 property is that the Value2 property doesn't use the Currency and Date data types.
E.g.:
this.Range["A1"].Value2 = DateTime.Now;
this.Range["A2"].Value = DateTime.Now;
=>Result:
41083.94759
23/6/2012 22:44
VSTO.NET addresses the safety and security issues in a couple of ways. Firstly, the new document/view
architecture allows code to be separated from presentation. The code that runs behind the Office document
can have security restrictions applied to it so that it is guaranteed safe and its authenticity can be
verified before being run. Contrast this to the macro approach where embedded macros are permitted to
execute on the user system unbounded.
Primary Interop Assemblies (PIA) are .NET wrappers built around existing COM components that allow
.NET code to communicate with the COM component
Microsoft .NET run-time cannot communicate directly with COM components. When communication
with COM components is required, the .NET Common Language Runtime (CLR) requires an adapter or
proxy to sit between the .NET assembly and the COM component. This adapter translates .NET requests
into COM requests making the automation possible. It’s important to understand that the proxy or
Runtime Callable Wrapper (RCW) does not re-implement functionality in the COM component. It simply
makes the type information inside the COM object available to the calling assembly so communication
or marshalling can take place.
VSTO vs others
VBA:
1. one major drawback was that security was an afterthought in the VBA approach
2.VBA is also a procedural programming language. It cannot take advantage of some of the niceties such
as inheritance, interfaces, and polymorphism.
VBA does provide certain advantages that VSTO cannot provide. For instance, VBA allows code to be
executed in worksheet functions. VSTO is unable to perform this task.
Disadvantages of VSTO
One major drawback to VSTO is that it requires the .NET framework to execute.