Considered to Implement Add New Contact?

[tothzola]

Hello pchemguy,

first aw all very nice project, very inspiring, thumbs up! I'm working on a similar project maintaining a customers data in excel. (Data validations, multiple forms, list views, quite complex) Will it be difficult to implement an add new contact in your project, died you consider this? Thanks for your reply. Regard, Zoltan

[pchemguy]

Hello @tothzola,

To make sure we are on the same page, this is not a real contact editor, but rather I coded this to learn better OOP in VBA and make some templates. Having said that, I suppose implementing adding routine, I think, is totally doable.

Additionally, I am actually working on an ADODB backend, so that the data could be pulled from an RDBMS, and I focus on SQLite as the prototype. The worksheet backend was a stepping stone on my way to an RDBMS, and it makes more sense to implement a more functional version with such a backend. What do you think?

[tothzola]

Hello @pchemguy

You are absolutely right and it is a very clean written template, I like it.

Couldn't read the whole project, just yet. I'm looking for inspiration to OOP in VBA, wrote code mainly as procedural and had some struggles with OOP in the beginning, that was couple a month's ago so I'm quite new in OOP. After that I stumbled across RubberduckVBA which is awesome 😁 and the MVVM patter, learned some techniques and apply it to my case, which is great but at the moment is a little bit overkill.

Back to my project, would love to do the backend in some database but in my case I have excel and just excel, that's why the whole project lives in excel (Database and UI). Can't change that unfortunately and the code should be as maintainable, efficient as possible because I'm the only person in the firm who codes in VBA...

ADODB is a great idea! Actually I use it in my project to pull worksheet table as disconnected recordset then present the data in the form ListView control this was good till I found your project 😀 can't decide to change it to as you implemented or leave it as it is. Project will grow in the future maybe recordset will read larger tables more efficiently.

Looking forward to your project, maybe after I'm ready my will publish it to GitHub.

[pchemguy]

@tothzola,

RubberduckVBA is awesome, I stumbled upon it a few months ago. Before, I actually made a few templates with ListView/TreeView in old Excel (the 64-bit Excel does not have the TreeView control, which I want to use.) But once I started learning through RubberduckVBA VBA code samples and the blog, I realized what a mess by old code was. So I started redoing using these kind of templates.

I tried to go through MVVM, but did not go to far. SecureADODB is better documented, is somewhat simpler, and I was specifically looking for a VBA ADODB library, which is how I found RubberduckVBA.

Depending on needs, sure, Worksheets can act as basic database as well. Have you considered SQLite though, which would be a second file living next to your Excel file, for example?

Indeed, you can use ADODB to pull data from Excel as well. I am not sure, however, that adding ADODB layer between VBA and the Worksheet can speed things up. I pull entire table from the Worksheet into a Variant 2D array like

Dim Records as Variant
Records = DataSheet.Range("A2:F50")

and you can save a 2D array to a Range in a similar fashion.

[tothzola]

@pchemguy

Thanks for the quick reply. Great idea wit SQL-light, but forgot to mention that the excel file (the finished project) goes up to share point, where customer service haves access, fills the form with new or change data, after the data input an UIPATH automation robot pulls the newly or changed data and transpose it to SAP.

For now I will leave the data in the workbook, maybe if it grows to large will export the database to sql file and implement. That's why I must think the code to be decoupled for easy maintenance an for business logic changes.

Yes the MVVM project leaks for better documentation, it is a great pattern.

Thanks for the tip, will consider it. Working at the moment how to add new data to the all my DBSheets efficiently from the formView.

[pchemguy]

@tothzola,

I see. There are a few instructive posts regarding decoupling the VBA code on RubberduckVBA blog, maybe you have read them already. That is what I also aiming at to accomplish here.

I do not know specific structure of your workbook, but I in general try avoiding looping through cells as much possible. When I have a rectangular region to fill, I would prepare a 2D array and then copy it to the range in one go. On the other hand, if you need to change just a few cells, looping through them might be better.

Effeciency-wise, you could also try https://codereview.stackexchange.com/, to get advices for improving working code.

[tothzola]

Hello @pchemguy how are you? Wrote you in private on twitter.

[pchemguy]

Hey, actually, this is not my account. You could use, for example, Reddit (reddit.com/user/pchemguy).

[tothzola]

Uups sorry for that. Made a Reddit account with this occasion ☺️