Para evoluir como profissional eu assino o site Pluralsight (www.pluralsight.com). Venho estudando e me aprimorando cada vez mais com o conteúdo de primeira linha que eles oferecem em inglês. Mesmo que você não seja fluente em inglês, é possível você entender as coisas já com um nível intermediário de inglês. Eles colocam closed-caption nos cursos e é possível controlar a velocidade do player, fazendo com que nós tenhamos como acelerar ou diminuir a velocidade do texto.

Acredito que a maioria de nós que estamos há alguns na área de desenvolvimento Web tenha um bom conhecimento de Javascript. Entretanto, acredito que muitas vezes, nosso conhecimento não seja tão profundo quanto a maioria das aplicações mais modernas exige. Em .NET, só com o advento do ASP.NET MVC é que temos sentido a real necessidade de nos profissionalizar no uso de Javascript em si.

Acho que o site ainda vai adicionar a feature de “trilhas” para organizar melhor o conteúdo deles. Hoje em dia, temos quase mil cursos jogados numa página, o que acaba deixando o usuário confuso quando precisa decidir qual curso fazer primeiro. Para isso, vou tentar contribuir com uma pequena trilha de Javascript!

Javascript from Scratch – Este curso é bom para quem realmente é newbie na área. Como é um curso de 2 horas, acho que vale a pena revisar os conceitos básicos – sempre há oportunidade de relembrar ou aprender alguma coisa.

Javascript Fundamentals – Este curso tem muito conteúdo em comum com o curso anterior mas tem umas dicas a mais sobre como debugar e testar.

Javascript for C# Developers – Este curso deve ser feito por todos. É imprescindível você entender conceitos relativamente tensos como closures, this, modulos, essa loucura toda de funções dentro de funções e etc. O Shawn explica de forma simples e brilhante como tudo funciona. Veja e reveja este curso algumas vezes. Não tente ver outros cursos mais avançados sem ver este antes.

JQuery Fundamentals – Este curso é um dos mais importantes no seu caminho. Com o conhecimento básico adquirido nos cursos anteriores, você agora irá aprender, neste curso de 5 horas, grande parte do que você precisará para fazer desenvolvimento Web do dia-a-dia. O curso é do Dan Wahlin, que explica com toda a calma do mundo, tem inglês muito claro e seus cursos geralmente tem 4,5 a 5 estrelas no site.

Structuring Javascript Code – Este padrões descritos aqui são os mais utilizados em projetos maiores em JS. Você saberá como organizar código em módulos, como não poluir o global namespace, como expor apenas código público, etc… é uma conhecimento importante que ajuda a evitar código JS muito poluído e bagunçado.  Outro curso bom do Dan.

JavaScript Design Patterns – Este curso é opcional para a sua formação, mas é útil conhecer pelo menos o design pattern “Promise”, que está sendo utilizado em vários lugares como Windows 8 e Angular.js. Ele também leva a discussão adiante sobre o “module pattern”. Vale a pena também porque muitas ferramentas open-source aplicam esses padrões mais complexos.

A partir deste ponto, você já terá o suficiente para conseguir trabalhar com segurança nos seus projetos.  Os próximos módulos são menos específicos em JS . Não se fala mais apenas na linguagem Javascript, mas sim, em como ela trabalha com as diversas tecnologias e situações do desenvolvimento Web, focando na tecnologia .NET. Os cursos aqui são opcionais e não mais sequenciais – você pode assistir aos cursos abaixo em qualquer ordem. Mas antes de ir a eles, é necessário que você conheça os padrões de projeto mais importantes e suas diferenças, como MVC, MVVM e MVP. Esta cultura geral é importantíssima, pois com o tempo, você vai notar, por exemplo, que além de trabalhar com MVC em .NET, você consegue trabalhar em MVVM no client, simultaneamente. Surgirão vários frameworks js em suas pesquisas e as situações que eles mais se aplicam variam de acordo com cada projeto. Este é um problema moderno, onde o excesso de opções às vezes nos assusta. Mas acredito que com conceitos básicos e conhecimentos fundamentais é possível decidir quais tecnologias aplicar em cada projeto.

Building ASP.NET MVC Apps with EF Code First, HTML5, and jQuery – Este curso mostra como fazer uma aplicação do começo ao fim usando exemplos reais. Bem bacana. Curso do Dan também! Para quem quer se atualizar em .NET, este curso é uma boa pedida, já que mostra as tecnologia que já são mainstream lá fora e aqui estão também se tornando mainstream.

Building a Site with Bootstrap, AngularJS, ASP.NET, EF and Azure – Este curso é épico. Um dos melhores da Pluralsight focados para desenvolvimento Web. O Shawn simplesmente arrebenta, passando por vários conceitos e tecnologias para fazer um site bacana. Este curso, embora seja opcional nesta trilha, é de sumária importância para quem precisa ver como as coisas realmente funcionam. Neste caso, ele puxa a sardinha para o Angular e para o Azure, mas sinceramente, é o que a maioria dos desenvolvedores .NET tende a usar hoje em dia. E mais: os conhecimentos expostos neste curso estão no job description de todas as vagas atuais dos EUA e UK.

Acho que é isso! Fica aí a dica. Por USD 29,90 mensais você tem acesso a todo este conteúdo.

A apresentação abaixo é uma introdução de como pensar em sistemas utilizando a platafoma .NET.

Veja mais detalhes em:

, ,

Olá,

Eu estou ministrando alguns treinamentos para ajudar a disseminar o conhecimento em empresas de desenvolvimento de software.

Esta apresentação é o material que foi apresentado em duas aproximadamente 10 horas de curso, feitos em 2 dias. As aulas foram muito pesadas, cheias de demos e exercícios mas acredito que valeu a pena – o pessoal conseguiu ver formas mais atuais de se construir software, focando em um design de classes limpo, simples e com classes com responsabilidade única.

A apresentação está aqui:

Espero que gostem!

Sds,
Mário

I’ve have been working on my personal project for some time. I needed a small and somewhat useful personal finance solution and I thought it would be good to exercise my .NET and VSTO skills using this project.

The project is on an early stage.

The initial screenshot is shown below:

VSTO Cash Flow

I’m trying to keep the natural look and feel of Excel as much as possible. The objective is to slightly automate some tasks and also, to keep the same sense of freedom the user feels using Excel.

The Visual Studio 2010 solution is shown below:

VSTO Cash Flow 2

The solution has become much bigger than I excepted. I had to consider various features/technologies/methodologies to develop this solution. Let me point some of them and share my current experiences in this project:

Code First Development

I must have the power to change, redesign and rethink my model without worrying about database or storare issues. I still don’t know how I’ll handle issues like categorization of expenses, for example.

Globalization

When I started the development of the project, I didn’t think about exposing the code on GitHub, creating a blog and showing the project to someone. But plans have changed and now I think this project might good for me to find more people around to world who are also VSTO programmers. Why not expose the code and see what happens? So the globalization became and urgent issue and the code had to be translated to English. There are stiil plenty of comments and code in Portuguese. But I’m fixing this and sooner or later the code will be better readable by the community. Another issue is to handle the data itselt in a globalized way – the column names on the worksheet must mean the same in any language.

Test-Driven Design

This concept is mainstream in the community. But Excel developers probably don’t have this knowledge and practice as VBA tools are very outdated and VBA projects don’t support the idea of testability and testing frameworks. The cashflow and balance algoritms were developed using NUnit and the spirit of TDD, which is the scenarion creation, writing of code to solve the businessproblem, testing and evolution of the code until it works without breaking other parts of the system.

WPF

The idea of this VSTO application is to be really helpful. I’m spending great time designing some cool site panels and eventually, popup windows in WPF to let those panels fully explain what’s happening to the user. Excel sometimes is very intimidating and I think that the average likes colors, aninations and instructions to use the application.

I think that this is an small introduction and I’ll look forward to evolve and show more features soon.

Kind Regards,

Mário

The most basic task of any Excel customization is to work with data. But sometimes, the performance of these operations can be affected by the size of the data involved.

The best way to work with data in Excel is using the ListObject control. When we are automating a business process, it’s likely that the data we’ll work come from external data sources, such as databases, web services, ERPs and so on.

In order to work with structured data in Excel, it’s recommended to use Excel Tables. An Excel Table is a special region of the workbook where the columns represent data in a uniform and organized way.

For more information about Excel Tables, take a look at: http://office.microsoft.com/en-us/excel-help/overview-of-excel-tables-HA010048546.aspx.

In this example, I’ll use a relatively huge table with 121317 records. This data can be found in the AdventureWorks database. The table is Sales.SalesOrderDetais and this is the larger table available there. The AdventureWorks database is a SQL Server example database, which can be downloaded at: http://msftdbprodsamples.codeplex.com/.

The ListObject control in VSTO

The ListObject control is the .NET representation of Excel’s ListObject.  With this representation in .NET, the greatest advantage is that you can respond to various events easily with default .NET event handling system. And also, you can use its methods and properties to manipulate sorting, column ordering, edition capabilities just to mention a few functionalities.

While you get a lot of helpful functionalities and organization using the ListObject, sometimes the read and write performance for larger datasets become unacceptable. In the following tests I’ll explore some ways work with massive thousands of records.

Test 1: Reading data in a straightforward way

This first test is the most obvious way to read all data of a ListObject is to enumerate the collection of rows and get the values stored in a class that usually represents the table to be read.

The class that will store each row is defined below:


    public class SalesOrderDetail
    {
        public int SalesOrderID { get; set; }
        public int SalesOrderDetailID { get; set; }
        public string CarrierTrackingNumber { get; set; }
        public int OrderQty { get; set; }
        public int ProductID { get; set; }
        public int SpecialOfferID { get; set; }
        public double UnitPrice { get; set; }
        public double UnitPriceDiscount { get; set; }
        public double LineTotal { get; set; }
        public DateTime ModifiedDate { get; set; }
    }

The sales SalesOrderDetail class represents exactly the table data:

Adventure Works - Sales Order Details Table - 121317 records

Please note on the image above that the table name, tblSalesOrderDetails, is represented with the same name and properties in Visual Studio – please note the property grid at the right. You must make sure that the table is named correctly – selecting the whole ListObject is a good way to do this.

You can configure all the ListObject properties from the grid and can listen to events like SelectionChange and BeforeRightClick, for example. The latter is very useful when you want to create a context menu application only to this instance of ListObject.

The code needed to read the data is:


            for (var i = 1; i <= Globals.Sheet1.tblSalesOrderDetails.ListRows.Count; i++)
            {
                var salesDetail = new SalesOrderDetail();

                salesDetail.SalesOrderID = (int)Globals.Sheet1.tblSalesOrderDetails.ListRows[i].Range[1, 1].Value2;
                salesDetail.SalesOrderDetailID = (int)Globals.Sheet1.tblSalesOrderDetails.ListRows[i].Range[1, 2].Value2;
                salesDetail.CarrierTrackingNumber = Globals.Sheet1.tblSalesOrderDetails.ListRows[i].Range[1, 3].Value2;
                salesDetail.OrderQty = (int)Globals.Sheet1.tblSalesOrderDetails.ListRows[i].Range[1, 4].Value2;
                salesDetail.ProductID = (int)Globals.Sheet1.tblSalesOrderDetails.ListRows[i].Range[1, 5].Value2;
                salesDetail.SpecialOfferID = (int)Globals.Sheet1.tblSalesOrderDetails.ListRows[i].Range[1, 6].Value2;
                salesDetail.UnitPrice = (double)Globals.Sheet1.tblSalesOrderDetails.ListRows[i].Range[1, 7].Value2;
                salesDetail.UnitPriceDiscount = (double)Globals.Sheet1.tblSalesOrderDetails.ListRows[i].Range[1, 8].Value2;
                salesDetail.LineTotal = (double)Globals.Sheet1.tblSalesOrderDetails.ListRows[i].Range[1, 9].Value2;
                salesDetail.ModifiedDate = DateTime.FromOADate(Globals.Sheet1.tblSalesOrderDetails.ListRows[i].Range[1, 10].Value2);

                _salesDetails.Add(salesDetail);
            }

You can notice the following things reading the code above:

Always read data using Range.Value2. This is the only guaranteed way to read data from Excel. There are only strings, integers and doubles in Excel. The dates are converted to a number in double format. To convert from double to a .NET DateTime, you can use DateTime.FromOADate, where “OA” stands for OLE Automation Date, a date format used in Excel.

For more information on Excel Interop Dates:

http://stackoverflow.com/questions/3724355/net-datetime-different-resolution-when-converting-to-and-from-oadate

http://www.dotnetperls.com/fromoadate

You might be thinking why I used the for loop instead of the foreach loop. The reason is if you try to enumerate directly the collection all at once, you’ll get an OutOfMemoryException and won’t be able to continue. I tested this scenario using 4GB and 6GB boxes. This situation is shown below.

OutOfMemoryException enumerating the ListRows

Therefore, you must avoid the early enumeration of ListRows caused by the foreach loop. You can achieve this at a cost of calling Excel Interop 121317 rows x 10 columns  = 1213170 times. This is very slow and using a core i5 the duration on my tests vary from 220 to 290 seconds, depending on box configurations. It’s quite clear that for the average user this method of reading data would be unacceptable.

Test 2: Reading data in a faster way.

A lot of performance optimizations found in VBA still apply to VSTO. In fact, the golden rule for any VBA customization is to avoid excessive I/O and always work with chunks of data. It’s always much better to read a whole range at once. This strategy is shown below:


            Globals.ThisWorkbook.ThisApplication.EnableEvents = false;

            object[,] rawData = Globals.Sheet1.tblSalesOrderDetails.Range.Value2;

            for (var row = 2; row <= rawData.GetLength(0); row++)
            {
                var salesDetail = new SalesOrderDetail();

                salesDetail.SalesOrderID = Convert.ToInt32(rawData[row, 1]);
                salesDetail.SalesOrderDetailID =  Convert.ToInt32(rawData[row, 2]);
                salesDetail.CarrierTrackingNumber =  Convert.ToString(rawData[row, 3]);
                salesDetail.OrderQty = Convert.ToInt32(rawData[row, 4]);
                salesDetail.ProductID = Convert.ToInt32(rawData[row, 5]);
                salesDetail.SpecialOfferID = Convert.ToInt32(rawData[row, 6]);
                salesDetail.UnitPrice = Convert.ToDouble(rawData[row, 7]);
                salesDetail.UnitPriceDiscount = Convert.ToDouble(rawData[row, 8]);
                salesDetail.LineTotal = Convert.ToDouble(rawData[row, 9]);
                salesDetail.ModifiedDate = DateTime.FromOADate((double)rawData[row, 10]);

                _salesDetails.Add(salesDetail);
            }

            Globals.ThisWorkbook.ThisApplication.EnableEvents = true;

This code really runs much faster. The trick is to use the property Value2 of the Range corresponding to the whole table with the data. The entire range is represented by object[,] – the dimensions are the number of rows and number of columns. The use of Value2 is necessary because you must get the “real” values that are stored in the workbook, without formatting or multilingual issues.

The code above is measuring the time elapsed on the data extraction and data processing in .NET. It’s clear that all the time is spent with I/O and Interop. But the results are very good. The whole process of reading took from 13 to 16 seconds, depending again on box configurations. This performance is acceptable, given the fact that all the cells must be read and this is a very large dataset.

Other thing that you might have noticed is that you can suspend Excel events during long running operations. This improves performance and is considered a good practice.

Test 3: Writing data in a straightforward way

The same methodology applies for writing data. The ListObject VSTO control exposes a convenient method named SetDataBinding(), where you can use common .NET data sources in data binding operations. The code is shown below:


            Globals.ThisWorkbook.ThisApplication.EnableEvents = false;
            Globals.Sheet1.tblSalesOrderDetails.SetDataBinding(_salesDetails, "", "SalesOrderID", "SalesOrderDetailID",
                                                               "CarrierTrackingNumber", "OrderQty", "ProductID",
                                                               "SpecialOfferID", "UnitPrice", "UnitPriceDiscount",
                                                               "LineTotal", "ModifiedDate");

            Globals.Sheet1.tblSalesOrderDetails.Disconnect();
            Globals.ThisWorkbook.ThisApplication.EnableEvents = true;

Using SetDataBinding(), you can configure the order of columns and that’s it. It’s very convenient and with good performance for smaller datasets.  But the performance penalty of this way is more evident with this dataset. It took about 75 to 100 seconds to execute the operation. In this specific case,  the next way to write data is recommended.

Test 4: Write data in a faster way.

In order to achieve great performance, you must use the same strategy used above: work with the whole range of data. In this case, you must build an object[,] array with the data you want to bind. This means you need to write some code to fill the 2×2 array with data. But the write process is much faster and the test took about 12 seconds. Take a look:


            Globals.ThisWorkbook.ThisApplication.ScreenUpdating = false;
            Globals.ThisWorkbook.ThisApplication.EnableEvents = false;

            //two-dimensional array to be bound to a listobject range.
            var arrayOfSales = new object[_salesDetails.Count, 10];

            for (int i = 0; i < _salesDetails.Count; i++)
            {
                arrayOfSales[i, 0] = _salesDetails[i].SalesOrderDetailID;
                arrayOfSales[i, 1] = _salesDetails[i].SalesOrderID;
                arrayOfSales[i, 2] = _salesDetails[i].CarrierTrackingNumber;
                arrayOfSales[i, 3] = _salesDetails[i].OrderQty;
                arrayOfSales[i, 4] = _salesDetails[i].ProductID;
                arrayOfSales[i, 5] = _salesDetails[i].SpecialOfferID;
                arrayOfSales[i, 6] = _salesDetails[i].UnitPrice;
                arrayOfSales[i, 7] = _salesDetails[i].UnitPriceDiscount;
                arrayOfSales[i, 8] = _salesDetails[i].LineTotal;
                arrayOfSales[i, 9] = _salesDetails[i].ModifiedDate; //no need to worry about OADate!
            }

            //adjust the range of listobject to accomodate the new rows - remember that range arrays are not zero-based.
            table.Resize(table.Range.Resize[_salesDetails.Count + 1]);
            table.DataBodyRange.Value2 = arrayOfSales;

Conclusion

If your customization works with small pieces of data, you can use ListObject methods and properties do retrieve and write data to Excel. If your data will increase over time, the best option is to use Range methods to execute I/O operations. And also, always use the Value2 property and only consider simpler data types like, ints, strings and doubles. All data formatting available in Excel is pure presentation layer magic – raw data is always stored in very simple data types. And finally, pay attention to Dates – they are always saved as a double value. The decimal data type can be used to work with financial values and is the recommended way to do it in .NET, but keep in mind that all worksheet numbers are stored as doubles.

As a final notice, when you run the customization, I created a simple Ribbon with 4 buttons to test each scenario. This ribbon is shown automatically when the cursor is inside the ListObject control I am using. In Excel 2010 is easier to show a given ribbon in an specific situation.

The code for this project is on Github: https://github.com/mariomeyrelles/VSTO-Examples/tree/master/src/ListObject

Kind Regards,

Mario

,

Introduction

As everybody knows, the Office package is, perhaps, the most important tool for the vast majority of information workers’ daily job. From operational to senior management, Office documents are used to transform business data into workable actions, ideally in the most optimized way. Many tasks, however, can and should be automated to optimize staff time and reduce the operational cost of companies.

Office programs, especially Excel and Word, are very customizable. You can make these programs become more than just document editors. We can use Office as a powerful platform for small and large companies to enable them to act more quickly, with lower costs and using cutting edge technologies. Although all programs are customizable in Office suite, Excel is commonly the most customized Office application. Therefore, most of the posts on this blog will focus on Excel.

The tool most often used to customize Excel is VBA. However, there is a more modern way of working with Excel, which is using VSTO. I’ll explain more calmly. And I’ll go through a simple but complete example showing the same customization process using both ways.

Let’s go!

Programming in Excel?

Yes, you already program in Excel. A friend of mine likes to say that Excel is the most widely used programming tool. And it makes sense. Excel lets you tell the computer what to do, with a large degree of freedom. That’s why many people find it difficult to work with Excel. People often blame Excel itself instead blaming the difficulty of the problem to be solved.
Some features are noticeably more complicated to understand and deserve a post just for that. But, in order to have technological background to program in Excel, I believe that concepts are essential such as:

  • Tables and Data Validation
  • PivotTables
  • Analysis and Data Consolidation
  • Programming logic

Assuming you already feel comfortable with such features, your next logical step is to create macros to assist you in day-to-day work. Some types of problems are very complicated to solve with simple formulas or are just impossible to solve without programming. Some interesting scenarios can include:

  • Automate recurring or very laborious tasks
  • Create a custom formula in Excel
  • Send an email when a situation occurs
  • Access a Web Service and update the spreadsheet with new information
  • Process a downloaded file and highlight the errors using a given set of business rules
  • Customization of Excel’s UI, such as context menus, ribbons and so on
  • Integration with other Office programs to perform a more complex task (for example: Create a spreadsheet in Excel, set up some fields and get a draft business proposal in Word, with the materials and other information on the proposal)

There are many possibilities! And to be able to complete this goal, we have actually two tools: VBA and VSTO.

VBA (Visual Basic for Applications)

VBA initial look and feel

VBA is still the main development tool for Office. Although this subject may be very extensive, briefly VBA is a tool that exposes to the user / developer all the objects of a particular Office program and uses Visual Basic programming language to manipulate these objects. In practice, in the case of Excel, we can access elements such as cells, active selections, whole documents and even the Excel Application to help automate a particular process. Also we can respond to events triggered by the user, such as changing the value of a cell. For example, we can intercept a click on a particular cell and write VBA code to perform some custom action.

To find out more about how Excel works and its object model you need to invest some time to understand what actions are equivalent to which lines of programming. Over time, you will understand that almost everything in any Office app is represented by an object and over time, you will also understand easily sentences like:

Range("F9").Select
Selection.AutoFill Destination:=Range("F9:F11")

The above lines were generated automatically, with the help of Macro Recorder. In the example above, I selected the cell F9 and pulled the selection to cell F11, which caused the auto-complete action in Excel. The macro recorder is by far the easiest way to find out how to automate a task in Excel. With the code it generates, you can quickly understand what are Ranges, Selections, Offsets, and so on. Also you can discover more complex commands. Using the macro recorder, you can discover the “secrets” behind almost any Excel command. There are several tutorials on the web showing how to work with VBA. But before focusing on VBA, it is really necessary to have some programming logic in order to understand conditions, control loops, functions, etc.

Even if you end up choosing not to use VBA and stick with VSTO, it is still absolutely necessary you know how to create macros with VBA – one good reason is that VBA and VSTO can talk to each other. And here’s one more very important fact: the help and documentation of VBA and Excel objects are fantastic. Many topics come with multiple examples that greatly facilitate understanding the Excel object. Many times you’ll find yourself looking for help and find VBA solutions that are applicable to VSTO solutions as well.

VSTO (Visual Studio Tools for Office)

VSTO initial look and feel

While VBA is the “traditional” way to automate Office, VSTO is the “modern” way. VSTO uses programming languages from the NET Framework, most notably, C# and VB.NET. With .NET we can go much further than just programming for Office. We can create all types of applications such as Web sites, Windows Forms, WPF, WebServices, WCF, Windows Services and other types. This alphabet soup means that .NET framework and Microsoft products are probably the most powerful platform to build serious and complex enterprise systems, which typically require large software engineering efforts. Using just VSTO companies can capitalize on the installed base of Excel to make the simplest spreadsheet become part of a complex business process.

While VBA is a feature “internal” to Office, VSTO uses a development model that is “external” to Office. So, VBA code that manipulates the host (i.e. the Office app itself) is much faster than code that does the same thing, but only written using VSTO. On the other hand, when the processing task is more focused on other activities outside the host, processing speeds tend to be equivalent. So when we are developing in VSTO, it is highly recommended to reduce the number of reads, writes and manipulations of the host.

Choosing between VBA and VSTO

The truth is that there is no general way to answer this question. Each case is different. But let me point a few facts:

  • It’s much simpler and faster to start working with VBA. Just press ALT + F11 to start programming. And to learn how to manipulate host, simply turn the macro recorder and voila! You can quickly learn the main commands to automate your task.
  • Both methods require you to know the Office application in order to create deep customizations.
  • Both methods require you to have programming logic.
  • Visual Basic language is still used, but is in full decline. No more new projects starting in VB6.
  • Earlier versions of VSTO were very bad to work with Office. The main programming language in .NET is C#. With Visual Studio 2010 and. NET Framework 4.0, you can use C# to program almost identically to the equivalent VBA/VB.NET code, i.e., using optional parameters and named arguments in the methods, working with simple dynamic types and using late binding, features that VBA and VB.NET has always had.
  • Although many people say it’s impossible, with VBA you can access the Internet, external databases, file system, networking and so on. The .NET and VSTO also allows that, but the big difference is that everything is ready for use.
  • Visual Studio is much more productive than the Macro Editor. However, for small tasks (e.g., a single simple function or macro), it pays to stay in Macro Editor.
  • Nowadays, C# is one of the most acclaimed programming language in the market. Almost all new projects .NET start using C#. The VB.NET language is used almost exclusively on projects that began in VB and were migrated to VB.NET, usually projects that are legacy and are still in production. Personally, I don’t see much projects starting with VB.NET. What I see in practice is that Visual Studio is commonly installed without VB.NET support.
  • Teamwork is much easier achieved with Visual Studio. Macros are usually created by a Power User of the company and this is usually a solitary development task. However, with Visual Studio and some version control system, entire teams can work in an Office customization project.
  • A spreadsheet with VBA macros always carries the code and possibly intellectual property. Although, nowadays, it doesn’t make much sense to worry about protecting the code itself, many companies do not like the idea of spreading worksheets with proprietary logic. With VSTO you can hide the code from the end user. Another serious problem is code updates, since users often create new worksheet based on copies of existing spreadsheets… In practice, developers end up sending the new code via email to the end user, who in turn needs to update the customization code using macro editor.
  • VSTO is relatively bureaucratic. But you can choose what to customize, whether it’s just a document or Excel application. The latter is commonly known as an Excel Add-in. In the vast majority of times, we just want to customize the document. After development, you can publish your customization using ClickOnce technology. It is necessary that each end user install the VSTO runtime and the. NET Framework. Both can be included in the same installation package.
  • In my opinion, the main advantage of VSTO is the use of C# and more precisely, a feature called LINQ. With LINQ and its related technologies, you can work with data in a way unthinkable in VBA or even other current programming languages. This productivity gain makes C# unbeatable for use in larger size projects, where the use of VSTO already pays itself.
  • Finally, the person who knows C# does not only know a tool to automate Excel or Word. This person gains access to a much larger world of possibilities where the Office programming is on just one of multiple options. The .NET developer gets good jobs with average salaries much higher than a VBA developer. Although this is wrong, VBA developers are much less respected developers than .NET or Java developers are. With .NET knowledge, the common Power User becomes a person who’s capable to improve company’s reality and capable to reduce operational costs. And also, the knowledge of object oriented programming can help you produce VBA code generally better, cleaner and more organized.

My goal is to help you build a stronger background to help you choose the best option to solve your business problem. But the idea is to go beyond. I want you to be able to see Office and Excel another cheap and still good option to company’s applications’ front-end. I want you to think about Office Business Applications. The OBA is a concept that Microsoft introduced to describe enterprise architectures that capitalize upon technologies such as Office, SharePoint, Databases, Web Services and enterprise data cubes. And to achieve OBA, it’s not enough to work with VBA. It’s necessary to use with VSTO.

Let’s start with a simple example.

A simple example: total checks to receive per month

Say you own a small shop. Over time you receive multiple postdated checks. As part of your financial planning, you need to know how much you should receive in the coming months.

Postdated checks to sum

The most obvious way to do this is to write a formula for each month:

The most obvious solution: calculate the sum for each month by hand.

This solution is simple and works if there are few customers, few checks and you don’t want to waste time with that task. Things can become more complicated when the data volume increase. To automate this process, it is possible to use the SUMIF() formula. With this formula you can get the monthly sums properly and avoid large manual work.

A more sophisticated solution: the use of SUMIF() function

In this example, we’ll write an imitation of the SUMIF to understand how to go through a set of data in cells to perform our calculations. We’ll do this example both in VBA and C#. I will put two buttons on the body of the worksheet. The first will perform the task in VBA. The second, will use VSTO to call C# code to perform exactly the same task.

At the end of the article there’s a link to download the complete solution and also some links to further study.

Calculating the sum of checks with VBA

The simplest way to solve this problem is to perform the following steps:

  1. Cycle through the list line by line and put each check in memory. The checks list begin in cell C5.
  2. Browse the list of months and for every month, calculate the sum of all checks that fall in the month of each list item. Place the result of this sum next to the desired month. The desired months start at I9.

The code to be able to make it work basically is shown below:

Sub Intro1()

  'First Step: read all checks in the worksheet.
  Dim initialRange As Range
  Set initialRange = Range("C5")

  'A collection is like a resizable array
  Dim checks As New Collection
  Dim row As Long
  row = 0

  'read row by row and store every check in the collection for further processing
  While initialRange.Offset(row, 1) <> ""

    Dim item As Check
    Set item = New Check

    'The Offset property is starts with zero and not with 1 as Ranges
    'fill the Check class with data from the worksheet
    item.CheckNumber = initialRange.Offset(row, 0).Value
    item.CustomerName = initialRange.Offset(row, 1).Value
    item.Amount = initialRange.Offset(row, 2).Value
    item.DueDate = initialRange.Offset(row, 3).Value

    'add each check found to the collection above
    checks.Add item

    'read the next row, e.g., move from C5 to C6
    row = row + 1

  Wend

  'iterate through the list of months and calculate the sum for each month
  Set initialRange = Range("I9")

  'reset the "offset" variable to use it again from I9
  row = 0

  While initialRange.Offset(row, 0) <> ""

    Dim thisMonth As Integer
    Dim monthlyAmount As Currency
    'find what's the desired month
    thisMonth = month(initialRange.Offset(row, 0).Value)
    'start a new sum
    monthlyAmount = 0

    For Each item In checks

        'if the month equals the month of the given check, add the check value
        If month(item.DueDate) = thisMonth Then
            monthlyAmount = monthlyAmount + item.Amount
        End If

    Next

    'set the total of each month next to the column containing the month
    initialRange.Offset(row, 1).Value = monthlyAmount

    'go to the next row
    row = row + 1
  Wend

End Sub

Now I will explain in detail what each piece of code does:

Dim initialRange As Range
Set initialRange = Range("C5")

Probably the most important object of Excel’s Object Model is the Range object. A range corresponds to a cell or multiple cells. Right now I’m pointing to the start of the Range containing the checks, the C5 cell. I’m skipping the header columns. To learn about the Visual Basic keywords and syntax, see the references at the end of this article.

Dim checks As New Collection
Dim row As Long
row = 0

A Collection is a simple set of items, where I can add and remove items as needed. It makes sense to create a resizable array for this kind of work. I’ll keep the checks in memory using this collection. I also declare a variable that will store the current row number I’m reading. In the next code snippet, I’m reading the check data and putting it in memory.

'read row by row and store every check in the collection for further processing
  While initialRange.Offset(row, 1) <> ""

    Dim item As Check
    Set item = New Check

    'The Offset property is starts with zero and not with 1 as Ranges
    'fill the Check class with data from the worksheet
    item.CheckNumber = initialRange.Offset(row, 0).Value
    item.CustomerName = initialRange.Offset(row, 1).Value
    item.Amount = initialRange.Offset(row, 2).Value
    item.DueDate = initialRange.Offset(row, 3).Value

    'add each check found to the collection above
    checks.Add item

    'read the next row, e.g., move from C5 to C6
    row = row + 1

  Wend

To navigate and read the existing data, I need a few things:

  • The reference to starting cell where the data starts.
  • Working with Offset. The Offset is a function of the Range object in the object model of Excel. This property is very useful to read data from neighboring cells. For example, the Offset returns another Range, responding to criteria such as “give me the cell that is two rows and three columns below me.”
  • Range.Value – is the most fundamental way to read data from a range.
  • I need a place to store the values read for future use. In the above code, we can clearly see that each line becomes a check object, which is then stored in the collection.

To save the value of check, I created a class named Check. A class is meant to organize the data of your application and treat them as one single thing. Without the class, I would have to write some logic that is much more dependent of cells’ absolute addresses. The idea is to avoid such dependencies because a lot of rework would be necessary if design of the worksheet changes over time. The Check class was defined with the four public fields corresponding to each column in the worksheet:

'Class Module
Public CheckNumber As String
Public CustomerName As String
Public Amount As Currency
Public DueDate As Date

These properties are marked as public as it’s necessary to change their respective values – the name and date of deposit of each check found. And I change these values at each row found in the While loop above – they start empty and need to have their values modified with each new check read from the spreadsheet. Instead of using fields, I could have used formal properties in the VBA code. But as this example is simple, the property syntax of VB is quite verbose for this situation.

After filling the collection with the 22 checks from the example, we can inspect the data inside the collection. To add a Watch just place the cursor above the name of the collection, in this case, “checks” and explore its contents as seen in the image below:

The break point stops code execution at any desired line.

Knowing how to debug is essential programming with efficiency. In the picture above, I placed a “breakpoint” in the line immediately after the end of the While. With that I can inspect my code and see what is wrong in the logic. Using the “Immediate Window”, you can run commands in VBA that have immediate effect on the worksheet. For example, with execution stopped at some point, I can open the Immediate Window and run simple commands from Excel. In the image below, I changed the value of cell B1 to “test.” This gives greater power to the person who is learning to work with Excel, it’s possible to navigate and execute code and already feel its effects.

The effect of a command typed inside Immediate Window

Back to the problem to be solved, we must now calculate the sum of the checks to be deposited each month.

As I already have the list of checks, I’m going to iterate through the list of months, starting at cell I9 in this example.

  'iterate through the list of months and calculate the sum for each month
  Set initialRange = Range("I9")

  'reset the "offset" variable to use it again from I9
  row = 0

  While initialRange.Offset(row, 0) <> ""

    Dim thisMonth As Integer
    Dim monthlyAmount As Currency
    'find what's the desired month
    thisMonth = month(initialRange.Offset(row, 0).Value)
    'start a new sum
    monthlyAmount = 0

    For Each item In checks

        'if the month equals the month of the given check, add the check value
        If month(item.DueDate) = thisMonth Then
            monthlyAmount = monthlyAmount + item.Amount
        End If

    Next

    'set the total of each month next to the column containing the month
    initialRange.Offset(row, 1).Value = monthlyAmount

    'go to the next row
    row = row + 1
  Wend

I set the range variable to I9 and define some variables of interest such as month and sum of the month. For each month I must traverse the collection of checks using a For Each, which is a For loop meant to be used with collections. For each check read, I get its month (using the Month function in VB). If the month is the same of the month I’m summing, I add the check value to this month sum. After finding the sum, I set the sum value next to the cell of the desired month.

To test the code, I created a button inside the spreadsheet. To add visual controls inside the spreadsheet, you must activate the toolbar called “Developer”. This makes it possible to draw the button on the screen and use the command “Assign Macro” to select which macro will be executed by clicking the button. Check the images below.

The Developer toolbar and Form Controls

Assign an existing macro to control

You can choose and edit the macros available.

Calculating the sum of checks with VSTO

Preparing the environment

First of all we must remember that Visual Studio is not a free tool. However, for our studies it’s possible to install a trial version that will work up to 90 days after installation. It is also mandatory to use Visual Studio 2010. The free versions of Visual Studio don’t support VSTO. Let’s now create a new project – Excel 2010 Workbook. In my examples I use Excel 2010 but for Excel 2007 the steps are very similar. When you open Visual Studio, you get the following screen:

Visual Studio 2010 initial screen

You can quickly access the latest projects, and also see read news. You also have the option to create a new project. Choose “New Project”. The following screen will appear:

Creating a new project: Excel 2010 Workbook

Choose Excel 2010 Workbook. You can only create a customization for the version of Office you have installed on your box. In my case, I have Excel 2010. The Excel 2010 Workbook template was chosen because I want to customize only a specific workbook, not the application itself. This kind of customization disappears when you close the document. The next screen asks if I want to create a document or customize an existing document. Create a new document. By choosing this option, after a while, you end with this first look:

Create a new Excel Project from a new or existing document

Successfully creating a new Excel VSTO project

The solution that you see in the picture above corresponds to a set of classes that represent the high-level objects in the document, i.e. the document and spreadsheets. We can inspect the code of each item by right-clicking on top of each item of the solution.

Inspecting the code-behind files of each project element

Generated code-behind for Sheet 1

Generated code-behind for workbook itself

If everything went as expected, you already have the environment set up to work with Excel and VSTO. If you hit F5, the project will compile and then you can successfully run your Excel customization. You can use breakpoints and other tools in the same way as you would in VBA editor. Visual Studio connects to the Excel executable and listens to events raised upon user interaction. With this tooling you can easily know what is happening at any time within your customization.

Solving the same checks problem in C#

To solve the problem I used the same logic found in the VBA code above:

        public void Intro1()
        {
            var initialRange = Range["C5"];
            var checks = new List<Check>();
            var row = 0;

            while (initialRange.Offset[row, 0].Value != null)
            {
                var item = new Check();
                item.CheckNumber = (long) initialRange.Offset[row, 0].Value;
                item.CustomerName = initialRange.Offset[row, 1].Value;
                item.Amount= initialRange.Offset[row, 2].Value;
                item.DueDate = initialRange.Offset[row, 3].Value;

                checks.Add(item);

                row++;
            }

            initialRange = Range["I9"];
            row = 0;

            while (initialRange.Offset[row, 0].Value != null)
            {
                var date = initialRange.Offset[row, 0].Value;
                var month = date.Month;
                double monthlyAmount = 0;

                foreach (var cheque in checks)
                {
                    if (cheque.DueDate.Month == month)
                        monthlyAmount += cheque.Amount;
                }

                initialRange.Offset[row, 1].Value = monthlyAmount;

                row++;
            }

        }

Check Class:

    public class Check
    {
        public long CheckNumber { get; set; }
        public string CustomerName { get; set; }
        public double Amount { get; set; }
        public DateTime DueDate { get; set; }
    }

The method Intro1() does exactly the same as its VBA counterpart. It scans the rows containing checks until an empty row is found, puts the checks in a list in-memory and processes the lines with the desired month to calculate each corresponding sum. The logic is very simple, but it is worth noting some comments.

  • The current default in C# is to declare variables using the keyword “var”. The compiler can discover whether I am declaring an integer or date. This makes the code much easier to read.
  • A collection of items is usually declared as List. In order to let the compiler know that I am creating a list of checks, I created a List of Checks, or a List .
  • The greatest fear of the people who work with VBA and want migrate to C# is the presence of brackets and semicolons. This is a silly fear because the compiler already tells you what is wrong before you even build the solution.
  • The debugging tools of Visual Studio are much, much better than those present in VBA.
  • The syntax of C# to access the Range object and other Excel object is very similar to the syntax VB/VB.NET provides. However, please note this facility exists only since version 4.0 of .NET Framework and its evolution of C#. Before that, using VSTO with C# and Visual Studio was very painful for the developer. C# 4.0 makes working with VBA much cleaner and easier.

To test the code I created another button on the worksheet. This button has a Click event. This event is then raised to VSTO which in turn runs the test code of this example.

Adding a button using VSTO

With the button created, you need to hook the Intro1() method for its Click event.

        private void button1_Click(object sender, EventArgs e)
        {
            Intro1();
        }

When following these examples, you’ll notice that the both ways, VBA and VSTO, produce the same results. As I have a file with VBA and VSTO code at the same time (I created a file with xlsm extension – Macro Enabled Workbook), I can test both simultaneously. For this simple test, you must open the solution in Visual Studio, compile, run and click both buttons. You can view the VBA code when Excel is open, hitting ALT + F11. One final note: if you change anything when running the customization from Visual Studio, your changes will not appear on the project after you close Excel and stop debugging. This is because the VSTO copies everything to \bin subdirectory to compile and run the code and spreadsheet.
This concludes this introductory example.

Conclusion

After this long post, I wish you remember the following:

  • VBA uses VB6 programming language. VSTO uses C# or VB.NET.
  • VBA and VSTO are technologies that can help automate a business process. VBA is very good at simple local solutions to moderately complex. VSTO is very good in more complex solutions that require many interactions with systems and resources outside the context of the Excel spreadsheet.
  • VSTO customizations are more common in Excel and Word.
  • VSTO can be a way to make Excel and other Office programs a front-end for enterprise systems. Most VSTO customization aims to capitalize on the Office programs installed by the corporation to give more power to the end user.
  • You need a license for Visual Studio to create solutions with VSTO. VBA is already installed by default in any default Office installation.
  • Excel object model is almost the same in VBA and C#. To get help, you can look for examples in both languages.

Resources

The complete code is available at: https://github.com/mariomeyrelles/VSTO-Examples/tree/master/src/IntroVSTO

References and interesting links

Please find below some interesting links.

Visual Studio 2010 (versão Trial):

http://www.microsoft.com/visualstudio/en-us/try

Programming logic for newcomers:

http://msdn.microsoft.com/en-us/library/aa288436.aspx – A great C# and .NET tutorial from Microsoft.
http://learnpythonthehardway.org/ – This the best free resource to help you learn how to program. Based on Python, it will slowly move to the concepts of today’s programming languages. Highly recommended!

VBA e VSTO

http://www.cpearson.com/Excel/Topic.aspx – An excellent collection from basic to advanced topics about using VBA at its best.
http://msdn.microsoft.com/en-us/library/ff986246%28Office.14%29.aspx – A small introduction showing how to work with VBA in Office 2010.
http://easyvsto.wordpress.com/2010/01/01/vba-vs-vsto-an-interesting-study/ – A good text illustrating how to choose between VBA and VSTO.

http://msdn.microsoft.com/en-us/vsto/default – Microsoft’s VSTO home page.

http://faculty.virginia.edu/ribando/modules/xls/VBAPrimer.pdf – This PDF file shows how you would use VBA in interesting engineering problems.

Sequential posts showing how to work with VSTO inside various Office applications, not only Excel:

http://msdn.microsoft.com/en-us/vsto/ee620548.aspx – Part 1

http://msdn.microsoft.com/en-us/vsto/ee861194.aspx – Part 2

http://msdn.microsoft.com/en-us/vsto/ff395841.aspx – Part 3

http://msdn.microsoft.com/en-us/vsto/ff452055.aspx – Part 4

http://blogs.msdn.com/b/vsto/archive/2010/08/19/deploying-microsoft-office-solutions-by-using-visual-studio-2010-and-windows-installer.aspx – Some useful links showing the deployment process of a VSTO solution.

Kind Regards,
Mario Meyrelles

, ,

I noticed that this blog would be useful for everyone and not just for Portuguese-speaking folks. So I decided to try to start writing in English. There’s a lack of resources when you look for VSTO. I’m developing with this technology for a while and I think my experience can help others in this field.

Currently, I’m developing a simple cash flow application to illustrate some challenges using VSTO and how you could address them. Since this project is used to test a lot of things, the code quality is very poor and not ready to be used yet as reference.  But I’ll keep working to clean, organize and build a usable product in the near future.

On the other hand, I’ll translate the other posts to English in order to maintain consistency between the next and old posts.

O Waldyr Felix postou na lista um tutorial bem simples para ensinar os primeiros passos com o Git.

http://waldyrfelix.net/blog/2011/05/21/intervalo-tcnico-sobre-git/

Eu gostei e recomendo a leitura.

Eu acompanho o Flávio Steffens há uns 3 anos já e desde então eu vi como foram as experiências dele com o empreendedorismo. Antes disso ele era gerente de projetos e acabou sendo um grande advogado das metodologias ágeis. Eu conheci o Agile em 2008 e tomei alguns dos posts deles como guia para a minha carreira na época.

Hoje, 4 de novembro, ele publicou alguns dos seus sentimentos que fazem lembrar muito a minha época de empreendedor.

O primeiro post é um desabafo da vida real: http://www.agileway.com.br/2011/11/04/empreender-e-se-frustrar/

E no blog da startup dele, ele colocou em detalhes como as coisas têm andado nos últimos tempos: http://www.woompa.blog.br/2011/11/04/como-foram-as-ultimas-semanas/

É galera. Tenso. Ele é um dos poucos caras na blogosfera que jogam a real na mesa. É a primeira vez que alguém realmente explicou alguma estratégia de approach comercial. Vale a pena ler e principalmente, vale a pena notar que a vida de empreendedorismo não é exatamente maravilhosa. Ela te leva a sentimento de euforia e tristeza em poucos minutos. Eu já tive essa vida e meu amigo ainda continua nela, sentindo o vento na cara e experimentando a dor e a delícia de correr riscos que não se devem correr.

* * *

(isso aqui só vale para empreendedorismo na área de TI, Web e etc…!)

Empreender sem dinheiro não dá certo. Empreender sem sorte também não dá certo. Empreender sem produto hoje em dia é quase suicídio. Tentar ganhar a vida abrindo uma consultoria ou agência é uma perfeita ilusão. Não tem como concorrer com as empresas de hoje no mercado, a não ser, é claro, que você tenha encontrado um nicho extremamente específico/exclusivo.

Acredito que para entrar no mercado hoje em dia é preciso antes de mais nada, de um bom produto. Algo palpável. Visível. De preferência, pronto para ser usado. Integrado em diversas plataformas.  Facilmente ajustável às novas necessidades do cliente, que provavelmente ninguém conhece ainda!

Para conseguir este produto, é preciso de algumas coisas como:

  • Tempo de desenvolvimento e capacidade de usar seu tempo livre para gastar neste projeto-sonho
  • Pessoas de extrema confiança para se trabalhar
  • Um time integrado
  • Dinheiro para custeio de infraestrutura básica, como controle de versão, licenças de software, notebooks/computadores decentes, internet e algumas horas de teste em algum cloud
  • Skill para lidar com diferentes plataformas e tecnologias (muito além do .NET, Java, etc…)
  • Capacidade de terceirizar algumas tarefas
  • Capacidade de eventualmente, aumentar seu time
  • Novamente, dinheiro para suportar um eventual ramp up do seu projeto, custeando servidores, banda de rede, etc…
  • Capacidade de eventualmente conseguir investimento de algum angel investor
  • Não descartar a oportunidade de empreendedorismo interno. Hoje as empresas estão cada vez mais abertas para colaborar com projetos bacanas dos funcionários, muitas vezes entrando no risco do projeto fornecendo pessoas e tempo para tocar a ideia

Mas antes de mais nada, é preciso também de uma ideia legal! Tudo bem que a ideia pode não ser importante se o produto for bem feito. Mas o que vemos nos posts acima, é que a ideia não é tão boa assim, mas a execução é muito bem feita. Então TUDO conta. Você precisa de uma boa ideia e de uma boa execução desta ideia. Esta boa ideia não necessariamente precisa ser uma ideia nova no mundo. Esta boa ideia pode ser simplesmente copiar o que existe e fazer bem feito. Se eu fosse capaz de copiar o Google e ter uma busca ainda melhor eu teria um possível sucesso com uma ideia velha e uma execução perfeita.

Uma forma de ter ideias úteis, no meu humilde ponto de vista, é responder a questões como:

  • Qual necessidade básica do ser humano vou conseguir atender?
  • Qual é a utilidade prática do meu produto para os meus clientes?
  • Por quê as pessoas pagariam pelo meu produto/serviço na prática?
  • Os clientes deixariam de usar o produto do concorrente para usar o meu produto?

Um produto de maior sucesso só rola se você usar os critérios de originalidade, execução e utilidade nas suas decisões. Confesso que estou trabalhando nas minhas horas vagas em alguma coisa ainda sozinho.  A minha ideia é muito incipiente mas como o produto é pequeno (em número de funcionalidades e pontos de função), acredito que conseguirei moldar a existência do mesmo até o ponto de conseguir deixá-lo em uma versão inicial “mostrável”.

Eu desejo sorte ao Flávio e à Woompa, e espero sinceramente dias melhores para a equipe deles.

Recentemente descobri um lugar onde as pessoas estão colocando os posts recentes dos seus blogs no LinkedIn.

O link para a discussão é: http://lnkd.in/Qg3YhU

Eu tenho visto mais posts de infra do que de desenvolvimento mas a grande vantagem é que dá para encontrar ótimo conteúdo feito por brasileiros e em português. Vale a pena a leitura!