Office Development – Installing and Running Yeoman

In the previous two posts around creating your first Word add-in we installed Node JS in the first post Office Development – Installing Node.js and installed Git in the second post Office Development – Installing Git. If you haven’t done this already move along and do that first.

In this post, third in the series of building your first Word Add-in (based on the JavaScript API for Office) we will install and use another tool called Yeoman.

Yeoman

Yeoman used to be a member of the social class in late medieval to early modern England (source wiki: https://en.wikipedia.org/wiki/Yeoman) but these days it is the name for an open source scaffolding tool, released at Google I/O 2012. Yeoman runs as a command-line tool and will provide you with a boiler plate project for several different solutions. Because the software is open sourced you can find it on GitHub: https://github.com/yeoman

Over time there have been created many generators for Yeoman to do all sorts of things such as creating website starters and more. The reason why we discuss it here is because they (the Office Dev Team) also created a generator that allows you to quickly build a Microsoft Office Add-in project using the Yeoman scaffolding tool.

You can use this if you simply want to start creating an add-in with a basic text editor other than Visual Studio. Of course, you can also use more advanced text editors including Visual Studio of Visual Studio Code but just to show you that you don’t need much to create your first add-in you can use this option.

Installation is easy (provided you installed the tools from the other two blogposts):

Open a Command window (Windows Flag, Command) and run:

npm install -g yo generator-office

This will globally (-g) install the Yeoman (yo) package and the Office Generator (generator-office) on your machine. The routine ends in an automatically engaged final check by yodoctor:

You can also check it yourself by running the command:

npm list -g -depth=0

If all went well you should see, as you can see in the picture above, at least yo@<version> and generator-office@<version>.

Now will be the time to generate our first Word Add-in … hooray!

Enter the command line command:

yo office

This will kick off a series of questions needed to create your add-in based on your input:

Except for the name I opted to keep the defaults. You can select other than the defaults but for now keeping the defaults is ok.

Yeoman starts generating the add-in for you and in the meantime a website is opened with instructions. The instructions take you by the hand and will guide you through the process when the add-in finished generating.

At this time generating your add-in is probably finished, so follow the instructions from the webpage by clicking Next

According to this page the next command should be:

npm start

This will start the local server hosting the add-in at:

https://localhost:3000

As you can see it will be hosted at a ‘secure’ site (https) on port 3000. This ‘secure’ site could give you some headache however. Initially the site needs the firewall to be opened:

I’d recommend unchecking the Public Networks checkbox and only to allow it on your Private Networks for now.

Continuing the process will result in another security issue:

Another no-go? Well, maybe … but let’s click “Details” first and find out what this is all about. So far not a real smooth experience for the novice office add-in developer don’t you think? Go ahead, click “Details”:

Ah! What it says is that your PC doesn’t trust the website’s security certificate … and to be more specific it found that the name of the hostname on the security certificate is different than the one you are trying to visit.

This is quite logical because you are visiting your localhost and the website’s security certificate, as you will find out a bit later, is signed to by “Internet Widgits Pty Ltd” so against all recommendations continue and click the “Go on to the webpage” link.

And there you have it! Your first Word Add-in!

“Well, hel-lo … there is no Word here, what do you mean” you might say … and you are right (again).

Although I explained in my earlier blogposts that Add-ins built by using the JavaScript for Office APIs basically are websites hosted in the Office client such as Word you’ve seen here that your really are building a website.

The security issues will however throw a big issue loading this website in the Word client. It will simply be rejected as it appeared to be unsafe to run.

Solving the certificate issue is a story on itself so I will put this in a separate blogpost.

What you’ve seen so far is that you built your new add-in by simply using some web development tools. No Visual Studio installations, nothing of this all. Obviously, you can use Visual Studio to create Office Add-ins, and I will show you that in another series of blogposts. For now we still need to finalize our first journey of creating this Word Add-in but we need to overcome our security issues first.

And therefor, to be continued …

August 2, 2017

Office Development – Installing Git

In our previous step to build our first Word Add-in with JavaScript for Office we installed Nodejs. Today we continue with installing Git. Over time there will not be a need to install these tools again, by then you will be a professional web developer living in the world of these tools and probably have these installed already before jumping into another project.

For now I assume you didn’t have the tools installed, but you can check on the command line with the command:

git –version

If you already installed Git you probably see something like this:

You can find the Git installer just by GoogleBinging for “Git” and this will lead you to:

https://git-scm.com

Now, on the right you see a nice downloader button that selects your installer based on the machine that you are on. Click this, and hit Run (usually you wouldn’t be so dumb and save the downloader first on your machine in order to have the anti-virus checker check your download but for now we just let it run #yolo … )

As usual the first dialog you’ll see are the GNU License terms, your only option is to agree by clicking next or abandon the installer and start reading a good book and continue living your life without the software. Today we’ll decide to go on however …

Now select a few components. You can add some icons to the desktop and I opt for the settings to add Windows Explorer Integration and the other defaults.

I also have it install the icons on the desktop for quick access, you can always throw ‘em in the bin if you think they are in your way.

The next dialog above is for adjusting the PATH environment. My option is to “Use Git from the Windows Command Prompt”. Note that with this option you’ll be able to use both Git Bash

Bash what? …

Ok, ok, I know, if you are native from Windows you probably won’t even know what Bash is. For now, you don’t need to know other than that this is some sort of a Linux command line option.

Don’t worry, you get the hang of it once you are moved over to the ‘other side’.

Choosing the HTTPS transport backend I opted to “Use the native Windows Secure Channel library” (just because we are still in the mood of Windows, you can change this later if you like).

Configuring the line ends I leave the default as it was the recommended setting on Windows “Checkout Windows-style, commit Unix-style line endings”

You already noticed it, there is a lot of Unix influencing here … Bash, LineFeeds … it takes me back to the time I studied Computer Science. #throwbackwednesday

The next dialog is no different. Configure the terminal emulator to use with Git Bash

The option selected allows you to use both MinTTY as terminal emulator as the common Windows Console (“cmd.exe”).

Again, select the defaults to enable file system caching and enabling the Git Credential manager. Hitting Install will kick off the Git Installer to install all the selected options you entered on the dialogs above:

Done, if you want to have a quick view at what Bash is tick the option to Launch Git Bash. Otherwise it will just lead you to the Release Notes (if you didn’t remove the check before “View Release Notes” that is). You now have Git on your machine, check the version again by running git –version again.

You should see this (or a newer version if you read this later than the day of the blogpost):

Ready to go into the next step to install Yeoman.

Yeoman whaaaaaaaaaaaaaat?

Yes, Yeoman … more in my next blog post.

Hang in there and see you tomorrow! You get used to it … let it all get into your system, GoogleBing it a bit for some extra context, perform all the steps from above doing it yourself and be ready for the next step.

August 1, 2017

Office Development – Installing Node.js

It’s time to try to build our first Word Add-in using the JavaScript for Office APIs. We won’t look at the specifics but we will just start building and see where we’ll end.

I will go into detail a lot because this is what you often won’t see in the tutorials. By going into the small detailed steps, I hope you (and I for myself) won’t get lost. Also, I think it is just good to document it in detail so if you want to look back later all steps are easy to recover. The risk is of course that by then everything changed .

For this first Word Add-in, we will be using Yeoman and not to make things too complicated we start with Windows, the platform most existing Office developers come from. Yeoman is a Web Scaffolding Tool for Modern Webapps. That’s right webapps … welcome to the world of Web Developers. A world of weird named tools such as Grunt or Gulp and nothing like the .NET world.

With Yeoman, you can generate a project from scratch that inserts all you need to start quickly. To install Yeoman, you need to install Node.js and Git. For Node.js we go to nodejs.org and on the start page of this site you see the downloads:

What you see is two options: LTS (Long Term Support) and Current. The LTS version is the most stable version and this is the version that they committed to support for the long term. With the Current version, you are on your own. For now, it is ok to install the LTS release.

To check if you already have installed Node open a command window (cmd) and type:

node -v <enter>

If it isn’t installed you obviously won’t get a version returned, otherwise you’ll see something like:

V6.11.0

We’ll install v6.11.2 in this case so here we go:

Click Next to continue …

Check the checkbox to accept the License Agreement and click Next

Select the Destination Folder (just leave as it is, or if you want it somewhere else change the path) and click Next to continue.

As you can see here is also the option to install the npm package manager. You’ll see later on that this tool will be used a lot to keep your installed packages up to date or just to install new packages. I just install all options here and click Next to continue.

Ready to install Node.js … Click Install!

Accept the security dialog to install in elevated mode and wait for the installer to finish up.

Done!

Click Finish to close the Node.js Setup Wizard and check the current installed version by opening a Command window (Windows Flag, Run, Cmd) and type

node -v <enter>

Result:

V6.11.2

In my next post, we’ll continue by installing Git and continue our endeavor to build our first JavaScript for Office Word Add-in.

In the meantime try to read some more about Node.js on their website: https://nodejs.org

To be continued …

July 31, 2017

Office Development – Word

In my previous post I mentioned the confusion for the JavaScript APIs for Office in general. Today we’ll go deeper in and focus on the API support for the various Word clients. Let’s see what APIs are available for each of the Word clients in detail and finish off with the observations once we identified what is in each client for Word:

Word Online

Extension Points

Taskpane
Add-in Commands

APIs

Word API 1.1
Word API 1.2
Word API 1.3
Dialog API 1.1

Shared APIs

BindingEvents
CustomXmlParts
DocumentEvents
ImageCoercion
MatrixBindings
MatrixCoercion
Settings
TableBindings
TableCoercion
Text File
TextBindings
TextCoercion

Word 2013 for Windows

Extension Points

Taskpane

APIs

Dialog API 1.1

Shared APIs

BindingEvents
CompressedFile
CustomXmlParts
DocumentEvents
File
HtmlCoercion
ImageCoercion
MatrixBinding
MatrixCoercion
OoxmlCoercion
Settings
TableBindings
TableCoercion
Text File
TextBindings
TextCoercion

Word 2016 for Windows

Extension Points

Taskpane
Add-in Commands

APIs

Word API 1.1
Word API 1.2
Word API 1.3
Dialog API 1.1

Shared APIs

BindingEvents
CompressedFile
CustomXmlParts
DocumentEvents
File
HtmlCoercion
ImageCoercion
Matrix Bindings
MatrixCoercion
OoxmlCoercion
Settings
TableBindings
TableCoercion
Text File
TextBindings
TextCoercion

Note that there is a difference between the MSI installed version or the Office365 version. The MSI version only contains WordApi 1.1. according to the specifications.

Word for iPad

Extension Points

Taskpane

APIs

Word API 1.1
Word API 1.2
Word API 1.3
Dialog API 1.1

Shared APIs

Word 2016 for Mac

Extension Points

Taskpane
Add-in Commands

APIs

Word API 1.1
Word API 1.2
Word API 1.3
Dialog API 1.1

Shared APIs

Word for iPhone

–

Word for Android

–

Word Mobile for Windows 10

–

Observations

So, what do we see here? First observation is that cross platform now means that besides the good old Windows system, additional clients are limited to: “Office Online”, “Office for iPad” and “Office 2016 for Mac”. None of the phone clients have support for add-ins.

A second observation is that all other clients are more or less in par with each other. Only “Office 2013 for Windows” is limited to the DialogApi 1.1 and also none of the WordApis. It also does not support Add-in Commands. This makes you think if you should build your add-ins for Office 2013 or not at all. My guess is that the limitations on this platform are so significant that it would be better to only focus on Office 2016 and up.

Third observation is the missing support for Add-in Commands on Office for iPad. I’m curious to know why this is the case. I thought the iPad also had something like the right click (press and hold for menu) but maybe not. I don’t own an iPad -darn, do I really need to buy one now- so I can’t check this at this time.

Fourth observation is the missing Shared APIs on “Office Online”:

CompressedFile
File
HtmlCoercion
OoxmlCoercion

Maybe these four APIs are just not logical to have in an “Office Online” environment, but that too is a wild guess as I don’t know yet what it is used for.

I’m not sure yet where to go next. We now know what APIs are available for each of the Word Office clients, so I think the next step would be building a starter Add-in for Word 2016 for Windows. This client has the biggest set of APIs supported making me think that this is the best candidate to experiment on and later on see how this fans out to the other clients such as Word Online or the Apple options.

To be continued …

July 30, 2017

Office Development – API Confusion

I started to dive in to the JavaScript API for Office. To get a starting point I started with Word as I’m very familiar with what is there already for VBA and VSTO and will allow me to give me a good starting point to compare.

The first thing you find when you start looking at the JavaScript API for Office is that there are two JavaScript Object models:

· Common APIs

· Host-specific

The Common APIs were introduced with Office 2013 and allows you to connect your add-in application with the Office client application. The object model has Office client specific APIs and APIs that target multiple Office host applications.

This is part of the Shared API and with this API you can interact with the content in Office documents (documents as in Documents, Worksheets, Presentations, Mail Items or Projects). With this API you can target Office 2013 and later. The Shared API is using the common API syntax and the object model used in this API uses callbacks.

Then there are the Host-specific APIs. These APIs were introduced with Office 2016. These APIs provide object models that are host-specific (duh-uh) but more important for stable software development: strongly typed objects that allow you to get some Intellisense in your development toolset.

The different API sets are a bit confusing and the documentation is not all that clear for you to find what is used by what host-application or that is using the Common API. There is a nice overview page that allows you to click on each host application and immediately showing you what API is supported:

Source: https://dev.office.com/add-in-availability

If you click on the details for Outlook for instance there are however Mailbox 1.0 to Mailbox 1.5 in the list but unlike the details for instance for Word or Excel these links are no hyperlinks so you are on your own with this (or maybe there just are no online references?)

Also, the references for Project or Access are not in the list at all. These are linked back to the Office common API requirement sets

I think you agree with me that this doesn’t make it all to clear, especially if you come from VBA or even VSTO. I’m not sure how you could document this better, I need more insights first to think about that. Next target will be to try to understand more about what is used where and when. One thing is relatively clear so far: You need to determine first what your target audience is. The host-specific APIs only target Office 2016 (and up?) to name one … decisions, decisions, decisions.

To be continued …

July 28, 2017

Office Development – Add-in Types, what did we have before?

To research the ‘new’ Office JS Add-ins we need to know what types of add-ins there are. If you come from VSTO there is some similarity, add-ins can be divided in different types. Let’s see what we had before.

In VSTO you had the option to create Document Customizations aka Document-level customizations where the add-in was attached to the document or spreadsheet, Application-level customizations, here the add-in lives in the context of the host application like Word or Excel and Outlook Form Regions for customizations in Outlook

Project Type	Feature	Application
Document Level Customization	Actions Pane	Word Excel
	Custom Ribbon UI	Excel InfoPath Outlook PowerPoint Project Word Visio
	Backstage View	Excel InfoPath Outlook PowerPoint Project Word Visio
	Controls on Documents	Excel Word
	Shortcut Menus	Excel InfoPath Outlook PowerPoint Project Word Visio
Application Level Customization	Custom Task Pane	Excel InfoPath Outlook PowerPoint Word
	Custom Ribbon UI	Excel InfoPath Outlook PowerPoint Project Word Visio
	Backstage View	Excel InfoPath Outlook PowerPoint Project Word Visio
	Outlook Form Regions	Outlook
	Controls on Documents	Excel Word
	Shortcut Menus	Excel InfoPath Outlook PowerPoint Project Word Visio

So, in general there are these options explained a bit:

Actions Pane/Task Pane (Depending Application or Document Level)
This is the pane on the right of a document that allows you to interact with the document. In case of an Actions Pane this is attached to the document, while the Task Pane can be there independent of the document. They can both do arbitrary things, but the Actions Pane only appears with the document.
Ribbon UI adjustments
Adding your own Ribbon with controls on it. VSTO can add almost every Ribbon Control available.
Backstage View
Backstage View is the view that you see when you select the File Tab. This allows you to add a special tab in there for whatever you want to show there. Some limitations apply
Outlook Form Regions
Outlook Form Regions extend the way you work with your mail. There are options to just add a small part on top of the mail item or take over the full real estate. You can specify when this happens, will it be only in compose or read mode? You decide.
Controls on Documents
With this feature you can enhance the document with extra controls on top of the document surface. The controls are not always persistent and you may need to re-create the controls each time you open the document if you want them to appear again.
Shortcut Menus
These are the context menus. Right click on the document and add extra function call options within the right-click-menu to allow the user to do specific tasks related to the position of the caret (insertion point where the cursor resides).

This is quite a lot of functionality and also keep in mind that from most of these features you can do the craziest things because you can extend the Task Panes/Action Panes with either Windows Forms but also XAML interfaces. The other features can use the full .NET Framework stack so you can do everything you want.

The challenge is now to see if we can get the same things with Office JS Add-ins. Obviously you don’t want to fall back into a limited feature set. On the other hand, there is the fact that it needs to run on multiple platforms. That can be a limiting factor.

Given this summary I now have a goal to what to look for in the ‘new’ world.

To be continued …

July 27, 2017

Office Development–The Good

So after a small introduction to the “Ugly” VBA, the “Bad” VSTO, we end with the “Good” … well end … I guess the end is just the start of a new experience of an Office Development platform not familiar to most people. It is not included in the box like VBA, not using the languages most Microsoft developers are familiar with.

So what is it? Office Add-ins, yes … this time with the capital A, indicating that we discuss the –new- way of developing Office customizations, are built using well known web development technologies like HTML, CSS and JavaScript.

By doing so Office Add-ins run across multiple versions of Office:

Office for Windows Desktop
Office Online
Office for the Mac
Office for the iPad

This is most likely not all, with Office emerging on other platforms like Android and Linux distributions these options will be on the shortlist as well. We will research these options on a later time.

So how does this work?

The architecture is a little bit different than the other technologies, although you can debate that the Office Add-ins mechanism looks like the way it was done with VSTO.

This is how it looks like with the JavaScript Office Add-ins:

There is an Add-in manifest in the XML format and a web solution hosted on an arbitrary web server. The image shows HTML + JavaScript but it can be more than that as long as valid web technologies are in place. The manifest contains all sorts of settings (we’ll dive in to see what is in there also later on) and it defines what Office clients are used and what it needs to run.

The Office JavaScript API first implemented some basic Excel features and even less Word features but today many more clients are supported and the Excel and Word features are getting more mature over time. The JavaScript API for Office contains objects and members that you need to build your add-ins and interact with content in your Office documents and web services.

If you look at the API you will see that you recognize a lot of your ‘old’ Office Object Model. Some are close to what they were, others needed to be adjusted and there are also new options.

Ok, the starter is here … in my next posts I will pick each of the items and look at the details. As I said earlier, this for me is probably like you a learning experience. I’ve been looking at it for years now in a helicopter/management overview but I decided I need to learn the ins and outs of the new platform. While doing so I try to teach you as well by blogging about it. Trying to keep it simple and getting more complex over time.

Let’s see if Office Add-ins are really the “Good” as advertised and can they do the same as we can do with VSTO add-ins….

July 25, 2017

Office Development–The Bad

Visual Studio Tools for Office (VSTO) is a set of development tools available in the form of a Visual Studio add-in (project templates) and a runtime that allows Microsoft Office 2003 and later versions of Office applications to host the .NET Framework Common Language Runtime (CLR) to expose their functionality via .NET.

Quote: https://en.wikipedia.org/wiki/Visual_Studio_Tools_for_Office

As we have seen in my previous blog post Office Development – The Ugly the first option to build your Office extensions using Visual Basic for Applications (VBA) did not win the beauty contest. A very powerful toolset to create beautiful and especially productive customizations. I’m sure that VBA, the tools to set Office to your hand, allowing you to tailor Office in a way that it perfectly fits the needs of your company, is one of the main reasons that made Office such a popular box of applications.

It was however risky – a potential security risk and code management is a pain. In the real world it happened that a ‘new’ version was rolled into production where all the sudden existing features ‘disappeared’ as a result of the developer picking the ‘wrong file’ containing an older version of the solution and continued developing features using the incomplete version.

This needed to change … in the meantime managed code -.NET- appeared at the horizon and the next option at least had to be a) secure and b) needed an improved source control option.

The solution Microsoft put on the table were the:

Microsoft Visual Studio Tools for the Microsoft Office System.

The product name was most likely the longest name available at that time. The Visual Studio Tools for the Microsoft Office System (VSTO) was architected as a bridge between Visual Studio and The Microsoft Office System. On one side the languages team with Visual Studio, on the other side the Office team and in the middle the VSTO team – code named Trinity …

So how is VSTO different compared to VBA?

Applications, add-ins or document customizations, are built using Visual Studio. Initially in a separate Visual Studio .NET 2003 VSTO SKU and after a few version inserted in the Visual Studio 2008 box. The code no longer lived in the documents or other Office files, but from now on lived in an external assembly that would be triggered by the Office host application if the right properties were available in the document.

Loading the assembly was done in a relative complex manner and it was made sure that security wise the pain seen with VBA would not exist with VSTO add-ins.

Here is a schematic overview of the load pattern:

As you can see the Office Application is looking at the Registry to see what add-ins are installed, if found any the Deployment manifest (pointed to by the Registry) is read and following that the Application manifest is read and the assembly loaded.

A real extension to the Office Application … to make this all secure the security is enforced at installation time. There was a whole slew of prereqs that must be covered before the add-in would be installed:

This part made VSTO the “Bad” in The Good, The Bad and The Ugly … it appeared to be a real pain for the IT Pros at the time. By default, VSTO used Click-Once to install the extension on the machine and a lot went wrong … certificates expired, not added to the Trusted Publisher lists, etc. etc. There was another option, to install an add-in using an .msi installer but the average VBA developer had a hard time getting around all of this, jumping all the hoops to make it work.

Was it all Bad?

Not at all! By using managed code, C# or VB.NET – whatever your preference was, your world of Office completely opened. Whatever you could do with .NET (and that is basically everything) you could add to your solution. Calling third party libraries, UI components, later even XAML UI interfaces, sky is the limit.

For me personally this is still my fav option to build Office customizations. I wasn’t awarded nine-year Visual Studio Tools for the Microsoft Office System MVP – I guess the longest MVP title as well, without reason. VSTO controlled my life for the large part of these years (hopefully my wife is not reading this ) did a lot of forum support in this area and built VSTO applications for large enterprise companies. At the time, we still had on Microsoft campus Software Design Review meetings to share feedback and we gave the team a hard time to make sure they released the features that we really needed.

Where to go next

But … the world changed, Windows is no longer the only platform that needs to be supported. Because of that a new mechanism was proposed. By using the common web standards and JavaScript a platform independent system was developed by providing OfficeJS APIs to access the Office object model from web based add-ins.

And this is where we will dive in soon. It’s a relative new world, although development already started years ago, so there is a lot to learn there. Let’s see if we can do the same with OfficeJS as we can do with VSTO – but now on multiple platforms and several clients.

Maybe I will return to VBA and VSTO in separate blog posts just for fun. There is just a ton of information to share on all off these areas.

Book

Oh, and if you really want to know the ins and outs of VSTO I really recommend you reading the VSTO bibles by Eric Carter and Eric Lippert: Visual Studio Tools for Office 2007: VSTO for Excel, Word and Outlook. It is a huge and heavy piece of reference with over a thousand pages of inside information.

July 24, 2017

Office Development – The Ugly

In the short introduction in my previous post “Office Development–The Good, The Bad and The Ugly?” I was showing three of the main options to build Office add-ins (again I will use Office Add-ins with the capital A to indicate talking about the ‘new’ Office JS Add-ins as opposed to add-ins in general). In this post, I’m now going into the first of each of the options a bit: how to start and where to look for things.

The first option is Visual Basic for Applications (VBA). If you go back a long time, like I do, this was about your first option to customize your Office environment. None of the other options were around at the time. VBA is hosted within the individual host applications such as Word, Excel, Outlook, PowerPoint, Excel or even some other applications not limited Microsoft to but licensed by Microsoft to these other vendors.

Disabled by default

Straight out of the box you need to activate the option to be able to use VBA from the host application. In this case Word, you need to go into the File menu option, select Customize Ribbon and tick the option on the right under Main Tabs called “Developer”.

After activating the option you will notice that the “Developer” Tab becomes visible:

By selecting the tab, you’ll see options like “Visual Basic”, “Macros”, “Record Macro” and more.

Entering your first code

Ok, now it’s time to enter your first VBA code … we won’t be creating the good old “Hello World” but it sure comes close. Click the “Visual Basic” button to open the VBA Integrated Development Environment (IDE) the environment where you build and (test)run your application.

Let’s dive in now … open the VBA IDE as explained above, and enter the following lines:

Sub test()

Dim DefinedVariable As String
UndefinedVariable = “test”
MsgBox DefinedVariable

End Sub

Like this:

Running the code with function key F5 or stepping into the code with F8 until End Sub (the yellow line) you will see that Word is showing an empty dialog box where you expected the word “test” to appear if all variables were used correctly:

The Ugly I

You will notice that I defined the variable “DefinedVariable” but I initialized the variable “UndefinedVariable” with the value of “test”. I did this to show you the first “Ugly” in VBA … Although you won’t get an error your application will show an unexpected result (well … if the mistake was made in real life)

What happened here is that VBA didn’t throw an error as you are perfectly allowed to initialize an undefined variable (the one called UndefinedVariable in the code) but you’ll find that the variable was displayed that you did not initialize, called DefinedVariable.

This, especially in larger applications will cause extremely unexpected results and if the variables just look alike it will take you hours to notice that you made a typo somewhere.

To avoid these issues “Option Explicit” was invented. By adding the line, VBA requires you to define all variables used in your application. So, if you use this and runs the code your will now see that an exception will be thrown to point out that you missed something here:

Where is your code stored?

You built your first application, but you wonder … where is it stored. Well that is both the beauty and evil thing about VBA, it travels with the document (template, workbook, presentation … depending on your host application). The beauty because it is very easy to distribute, you just share the document, if it is about Word, to another person in your organization and they can run it. That is also -the risk- of being able to just share it, shady virus writers realized this and used it as a vehicle to spread their viruses in a fairly easy manner. Microsoft tried to solve this issue a little bit by differentiating between file naming. Documents containing VBA code are using extensions with an m like document.docm while ‘plain’ no code documents are named document.docx. It is good to know though, that the old extension .doc still exists.

The Ugly II

Lack of security and the risk of writing faulty code makes VBA the “Ugly” one of the three options. You are perfectly capable of creating the best and most intelligent tools using VBA and there have been some great examples that boosted productivity but it is just not “Enterprise” ready. It is hard to control and can cause a large impact on your support division within your company.

So, should you use it? Of course, you can use it whenever you feel it will help you being productive! I still use it myself … if I need to create some quick ‘n dirty solutions to avoid repetition in my “task for the day” it is really easy to record a few lines of code, edit it to fit your needs and run it.

I deliberately didn’t go into details of the VBA language. The reason for that is that I just wanted to provide a primer showing the three development options and my goal will be to dive into the “Good” (as considered today) option. If your want to know how to proceed with VBA I suggest you dive into the documentation behind the links below or find some introduction sites. The benefit of a toolset that has been around for such a long time is that there is a boatload of information available on the interwebs to get you started. This post is merely an introduction as part of the big picture called “Office Development” or as I called it “Office Development 2017”.

Documentation

https://msdn.microsoft.com/VBA/VBA-Language-Reference

Wikipedia

https://en.wikipedia.org/wiki/Visual_Basic_for_Applications

July 22, 2017

Office Development–The Good, The Bad and The Ugly?

In my last post we’ve seen that there are just a whole list of options covered under Office Development. Just to get a starting point we start to focus on Office add-ins –without the capital A– first. With Office add-ins I mean Office add-ins in general, that is … from the early start to where we are today.

I know that these days the name Office Add-ins is considered to be the part where Office Add-ins are built using the OfficeJS APIs but in the early years you had Office add-ins in many forms and technologies.

The Ugly

Initially the only option to customize Office was by adding code using Microsoft’s Visual Basic for Applications (VBA). This was, or should I say is – it still exists and you can still use it, the embedded code engine running a subset of the full Visual Basic command set capable in addressing the Office Object Model to quickly build some Office automation functions to make life as an Office Worker easier. The language appeared to be very powerful and in the real world people built some crazy complex stuff with it.

The Bad

To add more structure to building Office add-ins a new development paradigm was developed. Still using the Office Object Model but this time using the managed languages such as VB.NET or C Sharp (C#).

Managing code with VBA could be a pain in the … Code traveled with the documents and when the document was copied another ‘branch’ was created and you would never know you were working on the latest version unless you managed your distributions very strictly. Also the declaration of variables wasn’t always enforced causing all sorts of runtime errors to surface just after releasing the production code.

By using this new way to build your Office add-ins or customizations (I will return on customizations vs. add-ins later in a separate blog post, that’s a story on itself) with the new tools: Microsoft’s “Visual Studio Tools for the Microsoft Office System” (VSTO) at least you had a better control over your source code and you could even use Source Control to keep track of your code versions.

With this, the first versions of VSTO, seemed to be very hard when it came to installing the add-ins. Security was improved a lot, but that came at the high price of tough installation issues.

The Good

In these days, deploying add-ins can’t be limited to the Windows Platform so another change was rising … these are the add-ins built using the combination of well known web technologies and by including a Manifest you are now able to run your Office Add-ins (with the capital A) on all platforms in all supported and still growing number of client applications. Currently the client applications are Excel, Outlook, PowerPoint, Word and recently added Project, Access and OneNote.

Really?

Are these three technologies really The Good, The Bad and The Ugly? Well no, obviously not, you can still build add-ins in all three of these technologies but VBA for instance always had a very bad name, also caused by virus developers using this technology to harm innocent computer users by exploiting the technology to take over the computer and in worst case disable it.

VSTO was considered to be a real improvement but this technology also had his downsides. Deployment could be really hard, but this improved in later versions. Still going multi platform was no option for VSTO.

So is all good with the OfficeJS add-ins? Again, not at all … for starters these technologies used here are in general very hard to understand when you come from the VBA or managed code languages. Also, the APIs are not fully completed.

It still isn’t possible to do all the things that you could do with VSTO. With VSTO there is not really a limit. Whatever you can do with managed code you can do with VSTO as it just is interfacing between Office and .NET. This of course in itself could be very dangerous and should be managed to the max. Also VSTO is used by shady guys building malicious code.

We will get more in detail (we are still diving in, going deeper and deeper at this time) in my following blog posts so subscribe and join me in this adventure called Office Development!

Sharing is caring:

Like this:

Sharing is caring:

Like this:

Sharing is caring:

Like this:

Word Online

Extension Points

APIs

Shared APIs

Word 2013 for Windows

Extension Points

APIs

Shared APIs

Word 2016 for Windows

Extension Points

APIs

Shared APIs

Word for iPad

Extension Points

APIs

Shared APIs

Word 2016 for Mac

Extension Points

APIs

Shared APIs

Word for iPhone

Word for Android

Word Mobile for Windows 10

Observations

Sharing is caring:

Like this:

Sharing is caring:

Like this:

Sharing is caring:

Like this:

Sharing is caring:

Like this:

Sharing is caring:

Like this:

Sharing is caring:

Like this:

Sharing is caring:

Like this: