Writing Windows applications for MS Office environment

Forums › Desktop computing › Writing Windows applications for MS Office environment

SpookyAwol

594 posts

Ultimate Geek

#272312 19-Jun-2020 09:29

In my time, Ive created a few specific forms based applications through MS Word (vba) that interface databases, create custom documents etc.
They are quite complex but make it easier for the users to automate their tasks.

Im kind of at a crossroads with some applications, as Ive reached limitations with what can be achieved in word Visual Basic editor - eg program crashing once a certain number of forms, lines of code are maxed.

Would there be a more robust method of programming a windows application that would still play nicely with Word / Access and perhaps have lesser macro security concerns.

Does that make sense?

I (possibly) want to be able to create a stand alone program, yet one that suits a medium level of vba knowledge.

1 | 2

2028 posts

Uber Geek

Subscriber

#2507896 19-Jun-2020 09:41

Have you looked a PowerApps - it's free, and Microsoft promote it as 'low code / no code'. It has excellent compatibility with Office 365 (such as reading data from SharePoint lists) and is quite easy to pickup. It is a web based tool and the apps can run on mobile devices too.

And, if it helps, PowerApps can be surfaced inside PowerBI reports.

(EDIT) You could also move to Visual Studio Community and develop WinForms in VB.NET - it's close enough to VBA that you should be able to pick it up quickly, but the .Net libraries and the way .Net works are different enough from the 'old' VBA way that its more of a step up. The advantage is you get true desktop applications, unit testing, git integration, better performance, and it's WAY more stable than VBA. There are still WinForms project templates available in Visual Studio but the current desktop flavor is WPF, but that's yet another thing to learn.

Sharesies kids

Free kids accounts - trade shares and funds (NZ, US) with Sharesies (affiliate link).

SpookyAwol

594 posts

Ultimate Geek

#2507937 19-Jun-2020 11:01

Cheers for that, Ill check out Visual Studio Community - ultimately its a steep learning curve and a lot of code to convert.

timbosan

2028 posts

Uber Geek

Subscriber

#2507956 19-Jun-2020 11:23

SpookyAwol:

Cheers for that, Ill check out Visual Studio Community - ultimately its a steep learning curve and a lot of code to convert.

Yeah, there is no easy way around this - you COULD try copying all code verbatim into class modules and fixing it up, but the problem is the way .Net works is different and you would be mixing styles. Sometimes it's just better to start again, write some nice clean code, and take advantage of newer tools like unit testing, linting, etc. or even move to a new paradigm like TDD, and using Entity Framework for database work (although I prefer using stored procs where possible and doing everything that way). Plus with VS you can use Azure DevOps for tracking work, running tests, continuous deployment, etc.

Having recently worked on a VBA project with over 5,000 lines of code, the difference is night and day, even just the advantage of using a proper IDE (Visual Studio). Plus the support for project types such as SQL Databases means you can have all your database code in another project (under the same solution) and deploy database changes from inside VS.

Happy to try and answer any questions you may have.

SpookyAwol

594 posts

Ultimate Geek

#2507958 19-Jun-2020 11:29

Im guessing the code and design is well within my capabilities, but the big unknown is the deployment and version control.
It seems the way to go, so Ill just knuckle down and start reading

timbosan

2028 posts

Uber Geek

Subscriber

#2507980 19-Jun-2020 11:54

SpookyAwol:

Im guessing the code and design is well within my capabilities, but the big unknown is the deployment and version control.
It seems the way to go, so Ill just knuckle down and start reading

Version control - use Git, it's integrated into Visual Studio. Azure DevOps will host your git repo for you, and under 5 users it's free. If you want to keep it easy just use the Master branch (especially if you are developing on your own), but do check out concepts like GitFlow as it's important to understand, especially if you want to create release branches later on (although with CI / CD I have found this unnecessary)

Deployment - there is a project type for this you can download and it will create a deployment application for you, including all dependencies. It's pretty basic but there are other (paid) options for doing this, but I have found it works OK. You can also set up DevOps to build apps for you when you push to git, so the process is fully automated:

Write Code -> Push to Git -> Unit tests Run -> Project Built -> Project Deployed

You can do the above either in the cloud or on premise. Lots of options!

davidcole

5807 posts

Uber Geek

Trusted

#2508031 19-Jun-2020 12:12

VBA to VB.Net is closer than to any other language (java, python, C#.Net). And would be the next step up if you're reaching the limits of VBA. Dealing with office with COM iteropts isn't great, but doable. I think there are newer methods for office 365, but for office 2013 etc, I think that's all you've got available.

Unless the office bit isn't important, and was just used to be able to program something (don't laugh, it's how I started in an area that wasn't supposed to be development based - VBA in word was the only language I had available until I was able to prove we needed a "proper" language).

Previously known as psycik

OpenHAB: Gigabyte AMD A8 Brix, OpenHAB with Aeotech ZWave Controller, Raspberry PI, Wemos D1 Mini, Zwave, Xiaomi Humidity and Temperature sensors
Media:Chromecast v2, ATV4 4k, ATV4, HDHomeRun Dual
Windows 10 Host Plex Server 3x3TB, 4x4TB using DriveBender, Samsung 850 evo 512 GB SSD, Hyper-V Server with 1xW10, 2xUbuntu 20.04 LTS, Backblaze Backups, usenetprime.com

SpookyAwol

594 posts

Ultimate Geek

#2508035 19-Jun-2020 12:24

The office part is fairly important, as mainly its an application to interface with customer details, book and schedule travel, but output documents in word format.
The end user is still rather manual and paper based

davidcole

5807 posts

Uber Geek

Trusted

#2508040 19-Jun-2020 12:31

SpookyAwol:

The office part is fairly important, as mainly its an application to interface with customer details, book and schedule travel, but output documents in word format.
The end user is still rather manual and paper based

Well actually that sounds like the office bit isn't too important, unless you're adding stuff to a template. Like you might be able to use openoffice to generate a word compatible document if you did want to separate entirely from it. Not to solution for you. But just letting you know there might be options if you did want to look at the VB.Net path and didn't have as much luck with Office COM Interops.

The user interface capabilities for .Net are far superior

SpookyAwol

594 posts

Ultimate Geek

#2508043 19-Jun-2020 12:45

As far as the documents go, yes Im using a custom set of templates that the interface outputs to - but that doesnt mean its the best way to do it now of course.

elpenguino

2908 posts

Uber Geek

#2508092 19-Jun-2020 13:39

SpookyAwol:

As far as the documents go, yes Im using a custom set of templates that the interface outputs to - but that doesnt mean its the best way to do it now of course.

Word (and the other office apps) exposes all its' internal workings so you can use external code to make documents at runtime. Either start from a blank sheet or fill in a template with placeholders.

With VB.net and intellisense you can easily refer to the Word objects and methods but it will take some mucking around to format things exactly the way you want.

If it was me, I would be looking to drop Word as the input to the system and rather use a Windows form which is going to make it easier to control the user experience (gag).

Most of the posters in this thread are just like chimpanzees on MDMA, full of feelings of bonhomie, joy, and optimism. Fred99 8/4/21

davidcole

5807 posts

Uber Geek

Trusted

#2508235 19-Jun-2020 15:38

elpenguino:

SpookyAwol:

As far as the documents go, yes Im using a custom set of templates that the interface outputs to - but that doesnt mean its the best way to do it now of course.

Word (and the other office apps) exposes all its' internal workings so you can use external code to make documents at runtime. Either start from a blank sheet or fill in a template with placeholders.

With VB.net and intellisense you can easily refer to the Word objects and methods but it will take some mucking around to format things exactly the way you want.

If it was me, I would be looking to drop Word as the input to the system and rather use a Windows form which is going to make it easier to control the user experience (gag).

I think the input is a formish thing currently, but the output is word.

Which if you don't want pdf, will still have to create some sort of editable document. But again, to completely remove yourself from office openoffice would suffice, or work though the COM interopt.

SpookyAwol

594 posts

Ultimate Geek

#2508242 19-Jun-2020 15:47

Sorry, not sure if you are after clarification.
The user interface is a Word UserForm (.dot) and outputs word documents.

So in response to David, yes, planning on dropping word userform as the interface and look at the Windows form via visual studio.
I still need to output documents from that though. Not necessarily word (and excel) but editable.
Still googling for samples ;)

davidcole

5807 posts

Uber Geek

Trusted

#2508262 19-Jun-2020 16:29

Probably makes it a much bigger project: https://stackoverflow.com/questions/390006/openoffice-and-net But this might be a longer term goal.

elpenguino

2908 posts

Uber Geek

#2508263 19-Jun-2020 16:30

SpookyAwol:

Sorry, not sure if you are after clarification.
The user interface is a Word UserForm (.dot) and outputs word documents.

So in response to David, yes, planning on dropping word userform as the interface and look at the Windows form via visual studio.
I still need to output documents from that though. Not necessarily word (and excel) but editable.
Still googling for samples ;)

You'll be googling for the Word object hierarchy (on MSDN). You'll have to create a document, then create a section object in that document, then define the size of the section, create a paragraph in that section etc etc.

That's not actually the hierarchy but I hope it gives some idea of the structure you'll be dealing with.

Most of the posters in this thread are just like chimpanzees on MDMA, full of feelings of bonhomie, joy, and optimism. Fred99 8/4/21

mdf

3321 posts

Uber Geek

Trusted

#2508272 19-Jun-2020 16:48

On the O365 platform, you can do Microsoft Form to populate Microsoft Word easily via Flow / Power Automate.

Lots of other relatively low code "form builders".

If you are open source inclined, docassemble.org is an incredible tool for the cost of spinning up a docker instance.

1 | 2