View Full Version : Designing applications in visual basic??

08-15-2013, 06:57 AM

First of all, I'll apologise in advance for my complete inexperience in anything other than excel :whistle:

Essentially, I've created a handfull of fairly sucessful (In my workplace) userform based applications using excel. Id really like to move more towards stand-alone applications.
From what i can gether, that would mean using Visual basic. With a bit of "youtubing", I see that its very similar, just without excel in the background, holding all the data/formulas etc.

Unless i want to design a calculator, youtube isnt too useful for my query, so I thought I'd use what has been my most valuable tool with my excel queries, which is obviously you loveley people.

Where does the background work happen with applications created in visual basic.
Where are the best resources (tutorials/example projects etc).
Am i barking up the wrong tree - is visual basic, the right tool for the job. I'm basically wanting to create applications that give users the ability to track (amongst other things), real time stats for kpi's etc.
I could go on for ever

08-16-2013, 02:49 PM

08-17-2013, 10:18 AM
userform based applications using excel I assume you mean that you are using a worksheet as a UserForm, because MS UserForms are VBA based Forms. Right click on a tab and select "View Code" to open the VBA Editor. Typ in the Codepage "UserForm," place the cursor inside that word and press F1. Browse around the Help file that opens looking at "Objects" in the Contents section.

To answer your questions:

In VBA Code Pages
Nope. Right Tree

Besides browsing this forum, "Project Assistance," You might pick up some ideas from

Finding a date without a search (http://www.vbaexpress.com/forum/showthread.php?47161-Finding-a-date-without-a-search)

help with complicated formula (http://www.vbaexpress.com/forum/showthread.php?47000-help-with-complicated-formula)

Trying to copy paste and delete a row (http://www.vbaexpress.com/forum/showthread.php?46847-Trying-to-copy-paste-and-delete-a-row&p=293554#post293554)

Pulling custom footer from another sheet, to only appear on *final* page (http://www.vbaexpress.com/forum/showthread.php?46768-Pulling-custom-footer-from-another-sheet-to-only-appear-on-*final*-page&p=293097#post293097)

The basic steps are to gather every form, paper and electronic, now in use and make a list of all Data Points on them. Then:

Organize your Databases
Design your Reports
Design your DataEntry Forms.
Write the Database code
Write the Userforms code
Write the Reports code

Note that as the project progresses, you will be modifying the results of previous steps, but if you follow the (badly stated) rules in the links, you will minimize those modifications.

08-17-2013, 12:39 PM
SamT, firstly, based purely on the effort you put in to your assistance, I doth my cap to you:beerchug:

I think an example of what I've done previously, will better explain.

I've had to re-jig it to take away company stuff etc, but should work fine and be resonably self explanitory.

I Love excel and what magic it can perform, but it scares the people I need to be able to use it for me to progress further.

What I'd really like, is to be able to design "applications" so, that, the "excel" stuff is hidden, so all thats left is the Userform.

The example I've included is a fairly basic example of some of the projects, that I've not been able to finish off, due to the fear that users ( and users managers) have with excel and therefore the very slow progress of my projects that are designed with the idea of improving effinciency etc.

Did I mean Visual Studio??:whistle::whistle:

08-17-2013, 01:30 PM
That's a wonderful App. Password protected so nobody can see what's happening. :doh:

08-17-2013, 01:40 PM
Thats mostly to hide my shoddy vba...

I suppose I cant really expect to upload something thats partly (largely..) thanks to people like your good self and protect the vba..

Heres an unprotected version

08-17-2013, 03:25 PM
There are things you can do to hide Excel. However, a better approach is probably to look at writing your own "applications" using VB.Net.

Take a look at: http://www.microsoft.com/visualstudio/eng#products/visual-studio-express-for-windows-desktop

It's free and fully operational for creating windows apps.

VB.Net is different to VBA but miles better. Once you've used .Net, VBA will feel clumsy.

A few examples:

- No need to use set
- Fully object oriented
- You can initialise variables when you declare them
- Auto-indent
- Auto complete of e.g. End If
- Incredible intellisense

It will take a little getting used to but will be worth it once you do.


08-17-2013, 03:58 PM
Thanks Mark007, that sounds interesting.

Am I being silly though, as the link takes me to this, which is a 30 day trial


08-17-2013, 07:01 PM
I mostly looked at UserForm2 and I think yu would be better served by putting the phone scripts in Worksheets.

Set the cdButtons Tag to the worksheet name, then when the commandbutton is clicked Activate the sheet, set the UF.Visible = False, Call a Sub that embeds a button on the sheet in Cell A1 that shows the UF and calls another a sub that removes the embedded button. Just keep passing the Sheets name (cmdButton Tag) to each sub,

Using TextBoxes to hold the phone scripts makes it impossible to edit the scripts and impossible to even see most of them in Design mode.

Unfortunately, between editing your workbook and converting it to xls format, something vital was lost, so I don't know if the forms and code will be avbailable in this attachment. The Phone Script sheets I made are there. I did format the Priority Sports sheet as I thought looked good.

08-18-2013, 05:36 AM
Thank's SamT,

Something did go wrong there I think.

This was only an example of what I'm wanting to achieve without relying on excel.

I've been swatting up on vb.Net with some YouTube vids and it does indeed seem perfect for what I'd like to move towards. It has opened up some more questions though.

1/ is there a free version for me to play with, like what Mark007 referred to, as the link takes me to the trial version of the full thing
2/ Do you have to use Access when dealing with tables etc. I've not used access for around 7 years..
3/ If the answer to (2) is yes, does access have to be available on the end users pc (my workplace doesn't have access to... Access). If the answer to (2) is No, can you just use excel.

I fully appreciate that these may sound like very basic questions, that I should already know, Considering what I've managed to achieve in excel, but I still class myself as a complete noob , that just happens to a be tenacious son of a gun, so I've basically not slept for around two years while I've been concentrating solely on excel... Before then, the only time I spent on pc's was playing around on photoshop.

08-18-2013, 10:39 AM
Tenacious? Yes.

I spent the first year of Excel analyzing the MSO 97 Object Model and recreating it as an Excel table(s).

Regardless of the programming language, the seven, (including gathering all the forms,) steps of designing an App are the same as I listed very briefly above.

A more detailed version of the steps is in one of the links I gave. I left out the gathering of forms, because the OP had already done that.

Free VB.Net IDE's:
Ask.com Search (http://www.ask.com/web?am=broad&q=vb6+ide+download&an=google_s&askid=a1d7ac46-4eec-48e1-809b-cfb83d3dba47-0-us_gsb&kv=sdb&gc=0&dqi=&qsrc=999&ad=semD&o=2493&l=dir)

More about VB.net: https://en.wikipedia.org/wiki/Visual_Basic_.NET

Buy it: http://www.ebay.com/bhp/visual-studio-net

08-18-2013, 03:33 PM
The link is to a free version. You have to register within 30 days but registration is free! :-)

When you talk about dealing with tables, I assume what you are referring to is a database. In which case the answer is no, you can use whatever database engine you want to use. This could be Access, XML, SQL server (express is free from microsoft too!), MySQL, text files etc.

Access is not a bad choice to use as the database jet engine is generally installed on windows PCs (or can be redistributed if not). End users don't need to actually have access.

You could use Excel but users would then need Excel installed and this would be a clunky way of doing it.

08-18-2013, 03:47 PM
Thank's for your responses Mark007 and SamT, I'll dig in to these tmo :-)

08-22-2013, 05:37 AM
Sorry for the delay, been a bit hectic.

Ive checked out the links and have been having a good swatting up session. It looks perfect. I'll be back soon with plenty more questions :thumb