Geekzone: technology news, blogs, forums
Guest
Welcome Guest.
You haven't logged in yet. If you don't have an account you can register now.




516 posts

Ultimate Geek


#272312 19-Jun-2020 09:29
Send private message quote this post

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.


View this topic in a long page with up to 500 replies per page Create new topic
 1 | 2
1705 posts

Uber Geek


  #2507896 19-Jun-2020 09:41
Send private message quote this post

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.




516 posts

Ultimate Geek


  #2507937 19-Jun-2020 11:01
Send private message quote this post

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


 
 
 
 


1705 posts

Uber Geek


  #2507956 19-Jun-2020 11:23
Send private message quote this post

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.




516 posts

Ultimate Geek


  #2507958 19-Jun-2020 11:29
Send private message quote this post

 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


1705 posts

Uber Geek


  #2507980 19-Jun-2020 11:54
Send private message quote this post

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!


4918 posts

Uber Geek

Trusted

  #2508031 19-Jun-2020 12:12
Send private message quote this post

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 BrixOpenHAB with Aeotech ZWave Controller, Raspberry PI, Wemos D1 Mini, Zwave, Xiaomi Humidity and Temperature sensors and Bluetooth LE Sensors
Media:Chromecast v2, ATV4, Roku3, HDHomeRun Dual
Windows 10
Host (Plex Server/Crashplan): 2x2TB, 2x3TB, 1x4TB using DriveBender, Samsung 850 evo 512 GB SSD, Hyper-V Server with 1xW10, 1xW2k8, 2xUbuntu 16.04 LTS, Crashplan, NextPVR channel for Plex,NextPVR Metadata Agent and Scanner for Plex




516 posts

Ultimate Geek


  #2508035 19-Jun-2020 12:24
Send private message quote this post

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


 
 
 
 


4918 posts

Uber Geek

Trusted

  #2508040 19-Jun-2020 12:31
Send private message quote this post

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 





Previously known as psycik

OpenHAB: Gigabyte AMD A8 BrixOpenHAB with Aeotech ZWave Controller, Raspberry PI, Wemos D1 Mini, Zwave, Xiaomi Humidity and Temperature sensors and Bluetooth LE Sensors
Media:Chromecast v2, ATV4, Roku3, HDHomeRun Dual
Windows 10
Host (Plex Server/Crashplan): 2x2TB, 2x3TB, 1x4TB using DriveBender, Samsung 850 evo 512 GB SSD, Hyper-V Server with 1xW10, 1xW2k8, 2xUbuntu 16.04 LTS, Crashplan, NextPVR channel for Plex,NextPVR Metadata Agent and Scanner for Plex




516 posts

Ultimate Geek


  #2508043 19-Jun-2020 12:45
Send private message quote this post

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.


1375 posts

Uber Geek

Subscriber

  #2508092 19-Jun-2020 13:39
Send private message quote this post

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).

 

 


4918 posts

Uber Geek

Trusted

  #2508235 19-Jun-2020 15:38
Send private message quote this post

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.

 

 





Previously known as psycik

OpenHAB: Gigabyte AMD A8 BrixOpenHAB with Aeotech ZWave Controller, Raspberry PI, Wemos D1 Mini, Zwave, Xiaomi Humidity and Temperature sensors and Bluetooth LE Sensors
Media:Chromecast v2, ATV4, Roku3, HDHomeRun Dual
Windows 10
Host (Plex Server/Crashplan): 2x2TB, 2x3TB, 1x4TB using DriveBender, Samsung 850 evo 512 GB SSD, Hyper-V Server with 1xW10, 1xW2k8, 2xUbuntu 16.04 LTS, Crashplan, NextPVR channel for Plex,NextPVR Metadata Agent and Scanner for Plex




516 posts

Ultimate Geek


  #2508242 19-Jun-2020 15:47
Send private message quote this post

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 ;)
 


4918 posts

Uber Geek

Trusted

  #2508262 19-Jun-2020 16:29
Send private message quote this post

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

 

 





Previously known as psycik

OpenHAB: Gigabyte AMD A8 BrixOpenHAB with Aeotech ZWave Controller, Raspberry PI, Wemos D1 Mini, Zwave, Xiaomi Humidity and Temperature sensors and Bluetooth LE Sensors
Media:Chromecast v2, ATV4, Roku3, HDHomeRun Dual
Windows 10
Host (Plex Server/Crashplan): 2x2TB, 2x3TB, 1x4TB using DriveBender, Samsung 850 evo 512 GB SSD, Hyper-V Server with 1xW10, 1xW2k8, 2xUbuntu 16.04 LTS, Crashplan, NextPVR channel for Plex,NextPVR Metadata Agent and Scanner for Plex


1375 posts

Uber Geek

Subscriber

  #2508263 19-Jun-2020 16:30
Send private message quote this post

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.


mdf

2629 posts

Uber Geek

Trusted
Subscriber

  #2508272 19-Jun-2020 16:48
Send private message quote this post

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
View this topic in a long page with up to 500 replies per page Create new topic




News »

Pre-orders for Huawei MateBook 13 open now
Posted 14-Aug-2020 14:26


Freeview On Demand app launches on Sony Android TVs
Posted 6-Aug-2020 13:35


UFB hits more than one million connections
Posted 6-Aug-2020 09:42


D-Link A/NZ extends COVR Wi-Fi EasyMesh System series with new three-pack
Posted 4-Aug-2020 15:01


New Zealand software Rfider tracks coffee from Colombia all the way to New Zealand businesses
Posted 3-Aug-2020 10:35


Logitech G launches Pro X Wireless gaming headset
Posted 3-Aug-2020 10:21


Sony Alpha 7S III provides supreme imaging performance
Posted 3-Aug-2020 10:11


Sony introduces first CFexpress Type A memory card
Posted 3-Aug-2020 10:05


Marsello acquires Goody consolidating online and in-store marketing position
Posted 30-Jul-2020 16:26


Fonterra first major customer for Microsoft's New Zealand datacentre
Posted 30-Jul-2020 08:07


Everything we learnt at the IBM Cloud Forum 2020
Posted 29-Jul-2020 14:45


Dropbox launches native HelloSign workflow and data residency in Australia
Posted 29-Jul-2020 12:48


Spark launches 5G in Palmerston North
Posted 29-Jul-2020 09:50


Lenovo brings speed and smarter features to new 5G mobile gaming phone
Posted 28-Jul-2020 22:00


Withings raises $60 million to enable bridge between patients and healthcare
Posted 28-Jul-2020 21:51



Geekzone Live »

Try automatic live updates from Geekzone directly in your browser, without refreshing the page, with Geekzone Live now.


Support Geekzone »

Our community of supporters help make Geekzone possible. Click the button below to join them.

Support Geezone on PressPatron



Are you subscribed to our RSS feed? You can download the latest headlines and summaries from our stories directly to your computer or smartphone by using a feed reader.

Alternatively, you can receive a daily email with Geekzone updates.