EXCEL APPLICATION - CAN IT BE USED IN/WITH/BY VISUAL STUDIO ?...

[chazrab]

I have a fully developed Excel application I wrote in VBA. I am of the mindset that while it works great, it could look better
and have much more functionality. The limited controls in the VBA toolbox don't offer me much in enhancing this app
to look and run more effieciently.

Before I embark on a learning curve with Visual Studio, I'd like for someone knowledgeable in both to tell me if it's worth
the time. What do I want to accomplish ? Simply to make the GUI look better. To me, looks are everything in an app.
Nearly the entire toolbox objects that Excel has - Userforms, buttons, textboxes, etc., seem archaic and dated.

I want a better looking userform, a better looking button, and built in Navigation control arrows, etc. The little I've read
in the Visual Studio tutorials imply that I need to learn C# and maybe other things to have the sharp looking professional
application interface I want.

A small example is the replacement of a combobox in Excel with a table of predefined choices that presents the user with a
table choice menu, that he/she only has to click on a selected item and code that performs an action is immediately
run. I know how to do this now with labels or small buttons. It just does not seem like an efficient way to do things since
each button or label will have different code to do different things.

Unless my knowledge of comboboxes and listboxes is limited, In a multicolumn combobox or listbox a user is forced
to select the entire row and only the first item is activated in the row, vs individual items in the multicolumn list.
So, why not have a built-in control that offers a programmer a TABLE choice rather than a long drop down list ?
Images below explain what I would like and don't like.

Need advice. Should I forget trying to develop this app in VS and stay with enhancing it in Excel/VBA ? - OR - can I use what I have already
developed in Excel and somehow PORT (for lack of a better term) the entire application over to VIsual Studio? The Excel app has three
sheets and about 20 userforms and button controls.

Thank you very much anyone for providing any guidance, opinions and/or comments on this.

cr

 

[Micron]

IMO, you should consider Access. If you program a GIU in C or VB or .Net, you'll still need a back end database so why not kill 2 birds with 1 stone, so to speak? The limiting factor might be how much eye candy you want to employ, but I think if you Google for Access interface images you'll get some idea of the possibilities - and there are lots. In terms of process capability, if you can imagine it, it probably is doable, right down to hiding the entire application window (which gives the impression you're using a .Net, C or VB built interface. IMO, your impression of Excel userform controls is justified to some extent - and no, in Access you can retrieve every value in a combobox row quite easily. In addition, you'd be able to take advantage of the inter-operability (is that a word?) of all the other Office apps. Access plays quite well with (I think) all of them. Basically, if it has a vba component, you have possibilities (thinking of Visio, PowerPoint, etc., which are not technically Office programs, I think). There is even support for pdf forms data exchange for Access, as well as being able to use mail without using Outlook (you can use CDO).

However, Access has a significant learning curve if you want to do it properly and well. To begin with, you would need to basically forget what you know about Excel, which more or less relies on column based data. Access data is row based and is relational - hence the term relational database.

 

[chazrab]

Hi Micron - good info. I have a limited knowledge of Access. If Access has more
interface design functionality, then I'll apply my learning curve to it vs. C#.
I imported the main Excel sheet of my Excel app into an Access table. Image below.
Will study design of GUI in Access now.
This table has 31,103 rows and the columns you see in the image. For me,
the next step is creating the objects and code to do what I want the objects to do, now
that I have the data they all use. These are userforms, textboxes on userforms and buttons
all used to search and display data.

Thanks for your guidance and help.
cr

 

[Micron]

You might not have any issues with that design, but it's not right. The data is structured like a spreadsheet, after all, and that's what I cautioned you about. I take it that those headers are versions. If it is possible that you will never want to add another version, you might get away with it, but getting data out will not be as easy as it should be.

Let's say you want to get a verse for a version and build a query for that, and base a form on it. However, you want the same verse in another version - now that query is no good because it's looking at the wrong field. So you create another query. Now what, you need another form because the 1st one is based on the 1st query? Yes you can alter the recordsource for a form at run time, but you'd need a query/sql statement for every version instead of one that you feed the version (and whatever else you need) into it. Your first step should be to study db normalization and understand it and post questions regarding your interpretation according to your project.

I was helping someone at AccessForums.net who was doing a bible verse thing and ended up with a table with over 36K rows, but he didn't follow up so I dropped doing anything with it. The table looks like this

VerseIDpk	verse	canon_order	book	chapter	startVerse	endVerse	versetext
1​	GN1_1	002_1_1	GEN	1​	1​	1​	In the beginning God created the heaven and the earth.
2​	GN1_2	002_1_2	GEN	1​	2​	2​	And the earth was without form, and void; and darkness was upon the face of the deep. And the Spirit of God moved upon the face of the waters.
3​	GN1_3	002_1_3	GEN	1​	3​	3​	And God said, Let there be light: and there was light.
4​	GN1_4	002_1_4	GEN	1​	4​	4​	And God saw the light, that it was good: and God divided the light from the darkness.
5​	GN1_5	002_1_5	GEN	1​	5​	5​	And God called the light Day, and the darkness he called Night. And the evening and the morning were the first day.
6​	GN1_6	002_1_6	GEN	1​	6​	6​	And God said, Let there be a firmament in the midst of the waters, and let it divide the waters from the waters.
7​	GN1_7	002_1_7	GEN	1​	7​	7​	And God made the firmament, and divided the waters which were under the firmament from the waters which were above the firmament: and it was so.

Some of that I didn't understand, such as why the start/end verse thing, but as I say, the thread died.

I foresee a lot of problems for you if you dive in and start creating objects in a db without the basic knowledge. As I said, you have to forget Excel if going the Access route. So far, you're showing that you are still tied to its ways. Maybe these will help you get off to a good start. Understanding db normalization is the footing (never mind the foundation) of any successful db project):


Normalization Parts I, II, III, IV, and V

What Is Normalization, Part I

Thoughts, opinions, samples, tips, and tricks about Microsoft Access

rogersaccessblog.blogspot.com

and/or

Database Normalization – Holowczak.com Tutorials

holowczak.com

Entity-Relationship Diagramming: Part I, II, III and IV

Entity-Relationship Diagramming: Part I

Thoughts, opinions, samples, tips, and tricks about Microsoft Access

rogersaccessblog.blogspot.com

How do I Create an Application in Microsoft Access?

How do I Create an Application in Microsoft Access?

Thoughts, opinions, samples, tips, and tricks about Microsoft Access

rogersaccessblog.blogspot.com

Important for success:
Naming conventions - General: Commonly used naming conventions

MS Access Naming Conventions

_________________________________________________________ Naming Conventions. This document is aimed at the user who is unfamiliar with any naming convention. It is based upon personal experience and the interaction I have had with others. It is not a hard and fast rule. Why use a...

www.access-programmers.co.uk

What not to use in names
- Microsoft Access tips: Problem names and reserved words in Access

About Auto Numbers
- General: Use Autonumbers properly

The evils of lookup fields - The Access Web - The Evils of Lookup Fields in Tables
Table and PK design tips - Microsoft Access Tables, Primary Key Tips and Techniques
About calculated table fields - Microsoft Access tips: Calculated Fields
About Multi Value Fields - http://www.mendipdatasystems.co.uk/multivalued-fields/4594468763

 

[chazrab]

Hi Micron - You haven't heard back from me because I've been refreshing myself on data
normalization and referential integrity. From what I've learned, the common element
in this table is the Book Chapter verse column(BCV) and that to me is the primary key.
The 4 versions then are in a 1-M relationship to the BCV key. From this, I see only
two tables are necessary for full normalization.

The whole purpose of this for me is to give accurate search results and add notes if
I want to any verse. The next step is to build a TABLE of Books to click and search on
as I showed in a previous image and below. I had to use labels, each coded to display
another CHAPTER table, listing the exact number of chapters in the selected book.
Clicking on a chapter number, opens ANOTHER VERSE table, listing all of the verses in
that chapter in that book. This is really not as complicated as it sounds - it just takes
me a whole lot of time coding all these labels. After all three components are selected
and saved, they are coded to appear in a textbox exactly in the same format as the BCV column
in the sheet. The FIND method uses that value and works correctly every time and has given accurate results..

I don't mind doing all this again in Access for the sole reasons of looks(eye candy)
and performance efficiency. This app does not perform calcs. It is primarily a search, display
and comparison tool to help speed up my research into serious, detailed Bible study.

Our BUDGET app was much less painful for me to develop, but it took 10 years to get
it to sing and dance the way I want(meaning report generation and what if scenarios in
manipulating budgeted finances to achieve maximized savings goals. I got it to work.
This Bible app is different because it has to use text number patterns to display selected
before and after verse blocks. There's a lot of people out there who can probably do this
with their eyes closed. I don't want to spend 10 more years, if I live that long, to do this,
though.

Thanks for your help.
cr
Kingwood, Tx

 

[Micron]

Well, glad to help whenever/wherever I can - not that I really understand if I did that here or not.