Use vsto or VBA to develop EXCEL reports?

VBA is Visual Basic for Applications
Vsto is Visual Studio Tools for office

Currently, the company wants to use Excel to implement the report function. I thought vsto was a replacement of VBA. In the vs.net environment, we should use vsto to develop excel.CodeConvert from VBA to Visual Basic. netArticle, More mistakenly thought it was an alternative trend (articles are added to the http://www.cnblogs.com/cowbird/articles/20433.aspx). But later I found it was not the case, VBA 6.0 was not terminated.

For details, see the following very good article, the article named "compare Microsoft Visual Basic for Applications 6.0 and Microsoft Visual Studio Tools for the Microsoft Office System", recommended to everyone (original in http://www.microsoft.com/china/msdn/library/office/office/odc_ofCOMpareVBA6andvsto.mspx)

In view of this article, we decided to use VBA to develop Excel later.
There are three reasons for VBA:
1. VBA can be adapted to multiple versions of office
2 VBA does not depend on. NET Framework
3. Microsoft has not abandoned it (at least there are no signs of eliminating it)

There are two questions about vsto deployment.
1. vsto deploys excel on the server and allows customers to call it in a unified manner. So I still don't understand the Excel report that was originally created on the client using VBA dynamically, and use vsto to implement it, what is put on the server?
2 can the Excel files on the server itself be stored?

I am not very familiar with it. Thank you for your advice.

The full text is as follows:

Comparison of Microsoft Visual Basic for Applications 6.0 and Microsoft Visual Studio Tools for the Microsoft Office System released on: | Updated on: 6/3/2004

Allison Balter

Infotechnology partners, Inc.

Applicable:
Microsoft? Visual Studio? Tools for the Microsoft Office System
Microsoft Visual Basic? For applications 6.3
Microsoft Office Word 2003
Microsoft Office Excel 2003

Abstract:Create a Microsoft Visual Studio Tools for the Microsoft Office System project or create a Microsoft Visual Basic for Applications 6.0 Project: Which of the following is a better solution? After reading this article, you will learn about the similarities and differences between the two environments and how to work together.

Content on this page

	Introduction
	Differences between Visual Studio Tools for office and VBA with. NET Framework
	Protection Code
	Deploy applications
	Is the mission of VBA 6.0 terminated?
	Summary

Introduction

Microsoft? Visual Studio? Tools for the Microsoft Office system is a new technology provided with Microsoft Office System, including Microsoft Office Access 2003 developer extensions. Visual Studio Tools for office allows you to use. Net-compatible languages (such as Microsoft Visual Basic? . NET and Microsoft Visual C #?) For Microsoft Office Word 2003 and Microsoft Office Excel 2003 applicationsProgramWrite managed code. You can not only use these languages to write code in Word and Excel, but also get more benefits from the powerful features and high productivity provided by. NET Framework. These benefits include:

•	Access Visual Studio. net ide (Interactive Development Environment)
•	Access Multiple debugging tools provided by Visual Studio. NET
•	Make full use of all. Net object models (such as ADO. Net)
•	Use server Resource Manager
•	Powerful functions for accessing the Microsoft. NET Framework class through Microsoft Office System
•	Select higher security options for the code you created
•	Implement fully object-oriented programming to compile more effective code
•	Can Microsoft Windows be embedded in Excel and Word applications? Form, so that the form contains richer controls (compared with Microsoft Visual Basic for Applications [VBA)

Fortunately, you can not only make full use of all these features, but also retain full access to Word and Excel object models. When programming in the Visual Studio Tools for office environment, write managed code. When writing managed code, it uses a language for the public Language Runtime Library, such as Visual Basic. net, Visual C #, and managed extensions for C ++. The code written in Word documents or Excel workbooks is not managed.

Managed code has many advantages. The Common Language Runtime Library provided with the managed code verifies the code to avoid illegal operations, such as accessing memory that does not belong to it. Managed Code also allows you to access Microsoft. NET Framework and its base class libraries.

So why do we need to use unmanaged code? There are several reasons for choosing to continue writing the unmanaged (VBA) code:

•	for some types of applications, the use of unmanaged code is easier to automate Excel or word operations, and requires less code. Other office programs are not supported by Visual Studio Tools for office, so VBA is required for automation (unless the conventional com InterOP technology is used ).
•	hosted code is not always suitable for creating com external programs.
•	if you make minor improvements to the existing VBA solution, you do not need to rewrite the VBA code because it can continue to run.
•	you do not need to deploy. NET Framework to write VBA code, and not all organizations have the. NET framework conditions.
•	VBA creates embedded code in the Microsoft Office System document so that the code can be packaged with the document. Visual Studio Tools for office code is stored outside the document or workbook, so you need to have a more comprehensive understanding of the deployment technology.

Visual Studio Tools for office not only provides powerful functions and high productivity of Visual Studio. NET and. NET Framework, but also provides scalability and programming capabilities of Word and Excel.

Back to Top

Differences between Visual Studio Tools for office and VBA with. NET Framework

For Microsoft Office system development, it is important to understand the specific differences between them when you evaluate whether to use a VBA project, Visual Studio Tools for office project, or both. Table 1 compares the two items.

Table1:VBAProject andVisual Studio Tools for officeProject comparison

Purpose	VBA Project	Visual Studio Tools for office project
Continuous Microsoft support	Yes	Yes
Microsoft Office System	No	Yes
Integrated Development Environment	Yes	Yes
Application Security	Integrates security features of Microsoft Office System	Integrates some security functions of Microsoft Office System and. NET Framework.
Ease of Security Configuration	Easy	It may be difficult
You need to change your Local Security Policy	No	Yes
Large programming frameworks are required to support development	No	Yes
. NET framework needs to be deployed	No	Yes
Full access to the object model of Microsoft Office System	Yes	Basically (there will be some data type conversion problems)
Language Support	VBA (based on Visual Basic 6.0)	Visual Basic. NET and Microsoft Visual C #
Object-Oriented Programming Environment	No	Yes
Difficulty in using Win32 APIs	High	Low. You can use com InterOP to obtain full support for Win32 APIs.
Available to all applications in Microsoft Office System	Yes	No (only available for Word and Excel)
Can I use Web Services?	Yes (via attachment Toolkit)	Yes (compile to IDE)
Microsoft Windows SharePoint? Services Integration	Yes	Yes
Level of difficulty	Easy (especially when macro recorder is used)	It is not difficult, but you need to understand the basic knowledge of. NET Framework.
Code and documentation Integration	Yes (only one deployment model is available)	No (the code is actually an assembly that provides more flexible deployment models)
Deployment Support (automatically update code)	No	Yes
Supports XML operations	Limited	Extensive
Automatically installed with Microsoft Office System	Yes	No
The. NET master InterOP assembly (PIA) is required)	No	Yes (but Microsoft Office System can automatically include these sets)
Namespace support	No	Yes
Degree of perfection	Low	Very high

Some of the content in the Table above deserves special attention. First, note that both of these projects can be continuously supported by Microsoft. In addition, the VBA project can exist in multiple Office versions, while the Visual Studio Tools for office project is only supported in Microsoft Office System.

The language support for these two environments is also quite different. The VBA project supports the VBA language based on Microsoft Visual Basic 6.0. Visual Studio Tools for Office supports Microsoft Visual Basic. NET and Microsoft Visual C. Because Visual Studio Tools for office has the object-oriented feature, the programming modes between these two environments are quite different.

The deployment between the two environments is also very different. Although this article also discussed deployment at the end, it emphasizes the core differences between the two types of projects in deployment. To deploy the Visual Studio Tools for office project, you must also install the. NET Framework on the target computer. In a VBA project, the code is usually part of a Word document or an Excel spreadsheet. In a Visual Studio Tools for Office Project, the code is included in the dataset generated by the project. Visual Studio Tools for office provides NTD support, so you can update the document code without calling or re-deploying the document. Visual Studio Tools for office is not automatically installed with Microsoft Office System. A project created using Visual Studio Tools for office must have a. NET Framework master InterOP assembly (PIA) to run. When you run the Visual Studio Tools for office project for the first time on a client computer, the PIA is automatically installed as needed. All of these forms a slightly more complex but flexible and powerful initial deployment than normal VBA applications.

Security is also a major difference between a VBA project and a Visual Studio Tools for office project. Visual Studio Tools for office Assembly running in word or Excel documents fully utilizes the security features of. NET Framework. This means that the solid security foundation for generating other applications is now available in office solutions. To ensure that you can take advantage of this advantage, you also need to have a better understanding of the assembly, trust, security policies, and other content of the. NET Framework, but these efforts are very worthwhile.

In addition, pay attention to the following points. Web services are integrated and compiled into Visual Studio Tools for office IDE, allowing you to easily integrate Web services into Visual Studio Tools for office projects. The Visual Studio Tools for office project provides extensive XML support. Of course, the two types of projects are not mutually exclusive. An application or solution can contain both VBA and Visual Studio Tools for office projects. However, when using hybrid code to create a solution, the debugging process may be a bit difficult, because it is difficult to determine which part of the Code has a problem. In addition, events (VBA or managed code) cannot be triggered as expected ).

Back to Top

Protection Code

Visual Studio Tools for office does not add security features, but you can use the security features included in. NET Framework and existing security features in Microsoft Office system projects. You can use all these security features compiled into the. NET Framework to control whether applications are allowed to run. For example, an administrator can grant fulltrust permission to all code deployed on a specific Intranet server so that it can run in word or Excel on a local computer. If a user receives a document or spreadsheet containing the managed code from this location, the code execution will not encounter any problems. If you receive a document containing the hosted code from another location, you can open the document without executing the code.

Each user's computer has a set of rules that specify the code that can be executed and the operations that can be performed. The Common Language Runtime Library collects creden。 before executing the code. The credential is based on the Code source (server name) and whether the Assembly has been signed. The credential is mapped to a specific policy. There are four types of policies: Computer, user, enterprise, and host. A policy can contain no code groups or multiple code groups. The code group provides a ing from the credential to the permission set. A permission set includes one or more permissions. Permission refers to the right to perform an operation. The Common Language Runtime uses the obtained creden。 to map the assembly to the code group, and then the code group maps the assembly to the cross section of the four policies. This ing determines whether the code can be executed and, if so, what operations can be performed.

Back to Top

Deploy applications

You can place the DLL created when compiling the Visual Studio Tools for office project in one of the following three locations: if the document associated with the code is used by a single user on a single computer, you can put the code and the document together on the user's computer. If the documents associated with the code are accessible to multiple users, you can store the code in a shared network location so that each client computer can access the latest code each time. The third option is to store the code on an enterprise intranet or a secure Internet site. The three options have their own advantages and disadvantages.

In the first option, documents and assembly are stored on a single user's computer. It has the advantage that you can use your solution without connecting to the network or the Internet. The main disadvantage is that you need to deploy application updates.

In the second option, users store documents on their computers and the Assembly is stored in a remote shared location. This allows each user to retain a dedicated copy of "data" (in this example, word or Excel document) and access the latest Assembly version. The biggest drawback is that you need to connect to the network or the Internet to use the application .. Net Framework provides the "no-touch deployment" function, allowing you to easily modify the code and deploy it to users. After modifying the code, you can redeploy the DLL to its original location. When a user opens a word or Excel file next time, the computer automatically downloads the latest version to the user's computer after detecting the DLL update and runs the latest version. You do not need to participate in this process.

In the third option, users store documents and assembly in remote network sharing locations. This allows you to make full use of the document collaboration functions provided by Word and Excel. The main drawback is that you must always be connected to the network.

Back to Top

Is the mission of VBA 6.0 terminated?

The next version of Microsoft Office system still uses VBA 6.0. If VBA is no longer used, Microsoft provides a migration policy. VBA 6.0 contains a lot of old code. In many cases, there may be no need to rewrite the existing code. However, the significant advantages and functions provided by. NET Framework may cause you to reconsider whether to improve some solutions. In Microsoft Office System, no language improvements were made to VBA 6.0.

Of course, for junior developers, there are too many things to grasp when using Visual Studio Tools for office for the first time. For beginners, macro recording is still a good method to learn how to create an office solution. This function can only be implemented in VBA. Junior developers can still focus some of their development efforts on VBA 6.0, while experienced developers can generate a part of the solution into managed code in the Visual Studio Tools for office environment. These two technologies can not only coexist, but also complement each other and work together in the same solution.

Back to Top

Summary

Visual Studio Tools for office is undoubtedly a revolutionary leap for Microsoft Office system development. It not only provides powerful. NET Framework functions and high productivity, but also provides scalability and programming capabilities for Microsoft Office System. Visual Studio Tools for office enables Visual Studio.. NET developers can generate Microsoft Office System-based applications, and continue to use the familiar programming environment, making full use of the reliable security, easy-to-deploy features and support functions of Web Services.

In the foreseeable future, VBA 6.0 and Visual Studio Tools for office will coexist in some form. Developers can continue to enjoy the Rapid Application Development Benefits of VBA and macro recorder for the office environment. With the development of Visual Studio Tools for office and Microsoft Office system becoming increasingly mature,. NET Framework brings more and more powerful functions and advantages to the office environment. For the above reasons, we believe that the combination of these two technologies will be just around the corner.