How should I proceed as I learn computer programming?
November 1, 2020 8:55 AM   Subscribe

I've developed a really useful, full-featured Excel workbook to track inventory and vehicles at my workplace. I've become pretty good at using forms, custom types, various kinds of loops, with statements, etc. I haven't done much with class modules, but I'm reasonably familiar with the concept. Now I want to take everything I've learned and design a standalone Windows application based on this workbook.

I've started learning Python, and it really feels like I have enough experience that the Python portion will be fairly easy. All the principles are the same but with a different syntax. (I hate that you don't declare your variables in Python!)

Where I'm completely in the dark is learning how to use Python in conjunction with a database and GUI tools. I'm thinking I need to look up YouTube videos on using Python with MySQL, yes? And once I'm comfortable using Python to manipulate a relational database, then I should start looking at GUI development applications?

I'm hoping I can have this project completed in a year, at which point I'd be a reasonably competent programmer. Is this a realistic expectation? Am I thinking about the process correctly, and are there some other issues I need to be thinking about at this stage?
posted by jwhite1979 to Computers & Internet (22 answers total) 8 users marked this as a favorite
Have you considered or examined the pure Microsoft infrastructure route? Dot Net? C#?

Something like what is suggested here?

You suggest that you will be rewriting you application in python, so you might not have considered the alternate path provided by the extremely rich Microsoft ecosystem. Rather than rewriting, you will be embedding and integrating your Excel work.

You mention the target as being a Windows application. Why this narrow target? Don't you want it to run anywhere?
posted by the Real Dan at 9:18 AM on November 1, 2020 [5 favorites]

While I don’t want to dissuade you from this approach, you might be making a lot of work for yourself by taking a rewrite from scratch approach. Seconding The Real Dan, people sell Office extension software all the time and that ecosystem is quite deep and robust.
posted by mhoye at 9:21 AM on November 1, 2020 [1 favorite]

PS: you’re already a competent programmer, the reason we refuse to think of excel as a “real programming platform” and the people who us it as Real Programmers is that if we did we’d have to admit that most of the Real Programming done in the world is done by women in pink collar fields working on business logic, and my goodness we can’t have that.
posted by mhoye at 9:24 AM on November 1, 2020 [9 favorites]

I'm glad I hit you guys up for advice. I was thinking Python because, though I didn't mention it, I was thinking I'd start with a Windows desktop application which I'd eventually adapt to run on the company's server. Is that possible using a Microsoft ecosystem? And if so, where do I begin learning how to do that? Also, will my VBA toolkit be more relevant if I take this approach?

And thanks, mhoye, for saying so. A huge portion of what I've learned comes from lessons designed for the demographic you're referring to, and I'm proud to think of myself as belonging to that class.
posted by jwhite1979 at 9:30 AM on November 1, 2020 [1 favorite]

The main issue with using Python for this project is that it's not really designed for standalone app windows UI development. I've never found a super great way to get a good windows UI for python apps, unless you just make it a web page that ties into something like chromium. I may be wrong and there is a good way to do this but I haven't found it.

Your best bet for standalone windows application is definitely C#. Your Python skills will transfer over pretty well and C# is absolutely a real programming language. By using C# and visual studio you get access to the easy to use UI creator that can make something that feels a lot like Excel (I think you can also get it other ways but I've always done it in Visual Studio). You can also port it later to Mono for Linux/etc if you want to, C# is versatile these days

The one suggestion I would have is to avoid using VBA. You can do many of these things with VBA but that won't increase your skill set and give you a good jumping off point for future things. By building your "program logic" in C# you can either have it talk to your standalone winforms UI, or eventually act as a web server program, C# works well for both. Apparently the newer WPF is better for designing for both standalone GUI and web, but I haven't used WPF myself
posted by JZig at 9:33 AM on November 1, 2020 [3 favorites]

I'm going to go against the grain here in that I became interested in programming in a similar situation that you're in. I had a task at work that was a worksheet and figured there was a better way to do it.

I'd highly recommend just getting something done and not worrying about technologies. Like me, without someone to help you, you're going to end up tripping up on tooling and stupid things that aren't really indications that you don't know what you're doing.

With that said, I'd say force whatever it is you're trying to do into Django. What's faster, easier, better for an experienced programmer is not going to be the same from someone getting into the mindset of the relative safety net of Excel to the wide open world of programming. Most importantly anything you'e trying to do is probably already done in Django and any errors you come across will be google-able. It might not have the same look and feel as you like, but I'd start with literally just recreating the functionality of the workbook. Find the simplest thing you're doing and get it done with Django.

To get you end the mindset of how complicated things can come so quickly: in Excel you have your forms already done for you. Let us say you have each row as a make, model and year. You have the form right there! You simply enter the make model and year. In programming you have none of that, so I'm saying introduce some of that with a heavy framework like Django. You might not feel like you are programming and you might hate Django but it doesn't matter. Eventually you'll get something done, then get another thing done. Keep chipping away and at a minimally viable product.

Similarly Microsoft's tooling isn't bad and offers a ton of support but moving over to the C# ecosystem isn't simple especially if you're learning because you get stuck in a lot of marketing terms. You already know Python syntax sort of and how to get your way around it. I'd suggest downloading a heavy IDEA like PyCharm, get Django and as much help in tools as possible. Again, your first program is going to be shit and you'll look back in 6 months and say "How did I not know that?!"

You'll develop a development "loop" for lack of a better term and begin to think of how to approach tasks like a developer. I think not using a heavy framework and doing things from scratch is like giving you 100 acres of land and telling you to build a house. Since you're not on a team full of people to help you on your journey you wouldn't start building a house from scratch. You'd probably buy a manufactured home then improve it where you need it.

So stick with Python, download Django, get what you need to done and don't worry about mySQL v Postgres just use whatever works for you to get your job done and you'll be surprisingly far in a short amount of time.
posted by geoff. at 9:49 AM on November 1, 2020 [4 favorites]

Pyside2 is a super great way to make a good Windows UI for Python apps. It's mature enough and popular enough that there's a community ready to answer questions in case you run into problems, and there are various tutorials and maybe books available. The development tools work well too.

There's something very satisfying about creating a stand-alone Windows app that doesn't rely on a browser to run, where you can add buttons and scroll wheels and widgets to your heart's content without jumping through hoops.
posted by Umami Dearest at 9:59 AM on November 1, 2020 [2 favorites]

the reason we refuse to think of excel as a “real programming platform” and the people who us it as Real Programmers is...
it's based on vba, which is not polymorphic, and is generally meant to be so accepting of poor practice that it doesn't ask anything in terms of rigorous design and engineering standards. Further, it doesn't favor oodd, functional, domain oriented or other overarching paradigm - instead, the vast majority of it's vendor and user examples are early 90s style procedural dreck that is prone to spaghettification and not really amenable to refactoring for reuse.

is it super useful? to a lot of businesses.
is it a value add? to a lot of businesses.
is it 'real programming'? not usually.

could it be? absolutely yes. depends on the coder. if you set the low bar at feature correct, no it will never be. add in

security, maintainability, testable, unit tested, componentized for reuse, extensible, handles exception states, performant...


to directly answer the question: get visual studio code and build a wpf app with a mssql backend (as was mentioned upthread, the ms ecosystem. it's arguable wether it's the better or worse than anything else, but the barriers to entry are super low. cost wise, and conceptually.)

db types
3nf (please get this down)
stored procedures
security basics

entity framework for data access
CRUD in entity

just some general oodd so you have a paradigm to work with. just know you need to look for other rapidly popularizing paradigms later.

single responsibility principle

common .net core libraries

visual studio code
get a github account, use SCC
a deployment tool like wix

at some point, learn unit testing. nunit and moq are reasonable frameworks.

memail me if you you need a specific sample to build, I'll find something. congrats for moving out of your comfort zone. pat yourself on the back for keeping yourself technically curious and stepping up. your excel work is super valuable and worthwhile; it got you where you are and showed that you have the creativity and energy to bring something useful into the world. well done.
posted by j_curiouser at 10:37 AM on November 1, 2020 [1 favorite]

on preview, geoff. has good insight too and i find his recommendation an equally valuable approach.
posted by j_curiouser at 10:38 AM on November 1, 2020 [2 favorites]

If you're running a desktop app on a server, how do you imagine that someone would access it?

I'm not a massive Django fan (purely issues of personal taste) but I agree with the underlying thought: that your language choice and your use case (central app, multiple clients) would be more a website use case than an app one.

The functionality would be about equivalent, the use easier and the skills you learned would be both more applicable to other problems and, if it's interesting, more marketable.

VBA is not a terrible language, by any means, and agreed that you can work magic for in app programming with it; but ultimately you will be stymied by the lack of support for programming tools that help you make fewer errors - clever IDEs, static analysis and test tools. I don't think you will care about static analysis any time soon, the IDE may also not be an issue, but the more complex the app the greater the need for testing.
posted by How much is that froggie in the window at 11:24 AM on November 1, 2020 [2 favorites]

A few thoughts:

- The state of .NET/C# GUI frameworks is a bit weird right now. Windows Forms is the easiest way to go even though it’s pretty old and no longer really receives feature updates. WPF is newer but also much more complex, and it’s been similarly deprecated in favour of UWP/WinUI (which you can think of as the evolution of WPF). Unfortunately WinUI is in a bad place while a big overhaul (Project Reunion) is worked on and probably won’t be newcomer-ready for another year.

- The state of Excel/Office addins is similarly weird. VSTO is the way to build addins in C# but it’s been deprecated in favour of JavaScript web addins which are arguably not yet ready for prime time.

- Use SQLite if you can. You’ll be able to experiment and iterate super super quickly compared to using a database with a separate server.

I live in these spaces professionally, feel free to shoot me a DM if you have questions about choosing the right MS technology.
posted by ripley_ at 12:08 PM on November 1, 2020 [2 favorites]

Whatever programming language you end up going for, the data is presumably going to end up in an SQL database. A key part of this job will therefore be designing a normalised set of data tables.
Most data in Excel is not normalised, even if you think it already is, it probably isn't. Getting that structure right is absolutely essential if you don't want to end up in programming hell.
posted by Lanark at 12:25 PM on November 1, 2020 [3 favorites]

(Very generic stuff follows)
You don't code to a particular database. You use a DB abstraction that has drivers for multiple database backends. You use an ORM of some sort on top of that. You should aim for your code to just look like code and not SQL and be capable of just changing some connection configuration and be able to work the same with MS SQL Server or MySQL or PostgreSQL or Sybase or Oracle, etc. Unless you need to because of data complexity... the DB stuff can be just automagick and you barely have to think about it.

Excel / VBA is an eventual horrorshow. You can do a lot with it but when it fights back you might not even notice. Gene researchers recently changed the names of gene sequences because Excel kept turning them into dates. The UK lost thousands of COVID case information because Excel has limits. It's great for simple things but will bite you in the ass for complex things. Horrorshow.

There are few and far between cross-platform languages / frameworks that can really look like a native application. There's always some little difference between Windows, Mac, X11, Wayland that makes things just a little bit wonky. As much as I hate to say it, you're probably better of using C# or some other .Net/Mono/VS Microsoft thing when it comes to native application.

If you are going for the client-server web-app sort of thing.... go nuts. You probably end up with a folder of program stuff instead of a singe executable but who cares? As long as you can point a browser to the server it's working.

Definitely look into Test Driven Development (TDD) because there's nothing more satisfying than hacking away all day and pressing a button and having the test come back OK. You fixed the problem (that you wrote a test for) and you didn't break anything else in the process.

Your other sort of option between going native and C# or doing some sort of web client/server is (it pains me so) Java. That's a different sort of hell but it probably would let you go cross platform and familiar or make it a web service.
posted by zengargoyle at 12:40 PM on November 1, 2020 [2 favorites]

If you choose to stick with Python, and are doing anything complicated with large amounts of data, look into Pandas, install it with Anaconda and experiment on it with JupyterLab.
posted by signal at 12:47 PM on November 1, 2020 [3 favorites]

It looks like I'll be diving into C#. I've downloaded Microsoft Visual Studio and watched an introduction video, and I've gotta say, it looks so weird compared to what I've seen. I'm sure it'll all make sense soon enough though.
posted by jwhite1979 at 12:51 PM on November 1, 2020 [1 favorite]

In my job I sometimes get thrown into situations where I need to take already existing "business logic" and "expose" it to different "front ends" (Web, system conversions, testing automation, etc.) What I find to be enormously useful is the "Proof of Concept". You stare at the assigned task and think "what would be the simplest functional demonstration that would show me this will really work?". Not part of my job but as an understandable example, if the business logic is "car loans" what is the simplest non-pretty way to take a request for a car loan, do basic validation and reply with a quote? If you can't do the simplest thing in the language and interface you've chosen, then you either need to learn more, adjust your approach, pick a different language, etc. Once the POC goes well then you know you're literally "on track". Of course a POC could run fine and a real world example could run slow or have terrible security, but since you're relatively new to all this I just thought I'd share what helps me.
posted by forthright at 3:00 PM on November 1, 2020 [2 favorites]

You don't code to a particular database.
That may be good practise if writing something you need to sell to multiple different customers all running different platforms, but for a custom solution you really risk re-inventing the wheel. Why spend a lot of time writing code that deals with Unicode or event logging or email, if the database already comes with a pre-written, pre-tested stored procedure that will do the same thing in one line? Concentrate your development effort on things that will make a difference.
posted by Lanark at 1:11 AM on November 2, 2020 [1 favorite]

I'm thirding Geoff. I took a pretty similar route in my career (Excel application to VBA to Python) and Python will likely be simpler to just get this done. Personally, I'm happier that I went toward Python as well as it opened up options to me that would not have been as easily available with a .NET/C# route.

I can't speak as much to the GUI side of things with Python, but if you still wanted to work with Excel using Python, I had good experience with openpyxl and xlwings.

For database stuff, you can either use the python packages for whatever db you decide to use (psycopg2 for postgres or sqlite3 for sqlite), or you can use something like SqlAlchemy which will get you a whole bunch of extra features and make working with databases more python-ic.

And signal's suggestion for Pandas, Anaconda, and JupyterLab is great as well.
posted by taltalim at 7:11 AM on November 2, 2020 [1 favorite]

Why are you set on creating a desktop app?

A webapp would be much easier to create, has more online tutorials, and would potentially be more useful (you can check inventory on a pc...or iPad...or phone in the future)

Most of the tooling mentioned here is geared towards webapps, with hacks to make everything work in a desktop application space.
posted by unexpected at 10:57 AM on November 2, 2020 [1 favorite]

I really, really hate web applications for this kind of database management. Maybe I've just used the wrong ones, but every one that I've tried--ShareMyToolBox, Milwaukee's proprietary tracking system, deWalt's--are all clunky as hell. Filtering and searching in Excel is way faster and more intuitive than anything I've found hosted remotely. I'm looking to keep that speed and flexibility in whatever it is that I design.
posted by jwhite1979 at 11:24 AM on November 2, 2020 [1 favorite]

understood. I think a lot of that is commercial applications have to be everything for everyone, whereas you get to make a solution specific to your needs.

Have you messed around with airtable?
posted by unexpected at 1:41 PM on November 2, 2020 [1 favorite]

Write a command line application. Check out (but not too seriously) Model–view–controller. The reality is that any application the 80% is command line able, maybe with a horrendous argument list or a DSL... *BUT* the graphical GUI/Webthing is just another 20% on top of that 80% command line capable code. You have to write that 80% anyway, there's no way around it. The GUI/Web 20% bit is really a PITA because it's doing by definition the exact same thing that that 80% of code does just fine.

Luckily, in my limited experience of such things GUI-like things like VS or other UI builders they often go into the MVC land and create a bit of an interface layer between the GUI and the code that does the work.

Your users might not care, but write the 80% command line driven tested code and then do the 20% GUI stuff. You always need the 80%, you can use that in cron jobs or batch scripts or midnight support calls as "tippity type boom done" and the 20% GUI is really just window dressing (heh).
posted by zengargoyle at 2:33 PM on November 2, 2020 [1 favorite]

« Older How can I stream *all* the *new* Lifetime Holiday...   |   Fashion resources for bigger dudes? Newer »

You are not logged in, either login or create an account to post comments