Moving on from Excel VBA to another programming language - what to choose? - Page 2

Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 16 of 16

Thread: Moving on from Excel VBA to another programming language - what to choose?

  1. #11
    Board Regular
    Join Date
    Oct 2011
    Location
    Norway
    Posts
    57
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Moving on from Excel VBA to another programming language - what to choose?

     
    Thanks alot Already downloaded on my iPad now!

    Appreciate all the help!

  2. #12
    Board Regular
    Join Date
    Jan 2010
    Location
    UK, Shropshire, Shrewsbury
    Posts
    469
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Moving on from Excel VBA to another programming language - what to choose?

    My 2 Cents worth:

    A lot of what people try to do in Excel is better suited to databases (think any time you have created reference sheets for drop down lists, or made copious use of Vlookups to link customer records to order records, or job items to their descriptions)

    My first port of call would be to download SQLExpress and MSSQL Management studio and learn about SQL and desigining good databases (google 3rd Form Normalisation)

    Strong database structures and be levereged into Excel and you CAN build decent small applications with Excel VBA and a database connection for internal use. I think SQL is easy to learn with a lot of quick wins, especially in an environment where you are sharing data with colleagues and all updating the same information.

    You could then learn Access: It is also VBA based although the objects and classes are completely different, the code is similar in structure. You can build great applications in Access, but it is also extremely easy to build really BAD applications in Access if you don't know what you are doing.

    My next choice would be PHP. Along with .NET (VB and C#) it is one of the most common languages for developing web sites. You can download XAMPP which is the windows version of LAMP (Linux, Apache, MySql & PHP). the X denotes Windows - from the days of XP! There is also a MAMP version for Mac fanboys. Apache is a web server and is one of the default server technologies (the other is IIS which is windows based). MySQL is a free SQL database and is pretty powerful but not as robust or scalable as MSSQL but will suffice for learning. PHP is a 'plain englis' 4GL programming language that essentially comes in two flavours: Procedural and Object Oriented. There is great documentation and a highly active user base who are welcoming to new users. If you are used to VBA code, then the switch to PHP is relatively easy: The syntax is different but the way you structure a program is similar. Once you have gotten to grips with PHP and basic web development (creating a form, Processing the results, saving the data, presenting the output etc..) I would suggest learning a PHP framework. My preferred framework is CodeIgniter but you could just as easily learn Zend, Cake, or a whole bunch of others.

    Web based apps seems to be the way of the future (this week...) but to be a complete web developer you need to know as a minimum:

    HTML5
    CSS
    PHP or .NET
    SQL
    Javascript

    Each of these on their own could take at least 6 months to master and they are all developing at increasingly frequent releases so keeping up to date is almost a full time job.

    For me, the .NET languages and the different options (web Forms, MVC2, MVC3, WebMatrix, LINQ, RAZR) just make it too difficult to get past the Hello World tutorials. The proliferation of classes and having to know which workspace to include to get at the classes leaves me frustrated. As an example find some code for connecting to a database whose connection details are stored in the webconfig file. (from the MSDN site)
    Code:
       System.Configuration.Configuration rootWebConfig =
    System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration("/MyWebSiteRoot");
    System.Configuration.ConnectionStringSettings connString;
    if (rootWebConfig.ConnectionStrings.ConnectionStrings.Count > 0)
    {
    connString =
    rootWebConfig.ConnectionStrings.ConnectionStrings["NorthwindConnectionString"];
    if (connString != null)
    Console.WriteLine("Northwind connection string = \"{0}\"",
    connString.ConnectionString);
    else
    Console.WriteLine("No Northwind connection string");
    }
    The are ten types of users in this world, those that understand Binary and those that don't

    Why do developers get Xmas and Halloween mixed up? Because OCT31 = DEC25

    Work: XL2010/Windows 7 over Citrix 12 on WYSE terminals
    Home: XL2007/Vista home Premium 32 bit

  3. #13
    Board Regular
    Join Date
    Oct 2011
    Location
    Norway
    Posts
    57
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Moving on from Excel VBA to another programming language - what to choose?

    I probably should have delved into Access considering how much excel sheets and lists are floating around in the projects with all manners of information. We already have software though which contains alot of the data. However, depending on project, we have three different engineering databases (Kesys, Time and Comos), as well as different document control tools (ProArc and Projectwise) and we have a variety of PCS (Project Completion Systems) software depending on our client (Go Complete, CCMS, MIPS, ProCoSys and a variety of others) as well as our planning tool which for the most part is Safran and our plant design tool which mostly is PDMS.

    All of this different software has various bits of information that needs to be linked together. Some information is transferred between the software automatically on a daily (or rather nightly) basis. But alot of the information is kept within each database, and unless you know what to search for (such as specific document numbers. purchase orders, plan activities or objectnumbers for tags, cables, motors etc) then it's very hard to keep an overview (remember we work with anything from 2.000 to 30.000 objects in each project and all those objects have their range of attributes, various documents, linked to planned activities and platform systems). Mostly this is done by exporting one excel report from one database and comparing/transferring information from it to another excel report from another database to get the information they need. Goes without saying that the few people that I have shown how to use VLOOKUP suddenly find themselves working much faster than before.

    I don't think I will touch on developing websites and such, except for personal interest if anything. The majority of the work consists of coordinating everything and making sure everything is in place, has the correct data, is being installed according to plan and will be ready for hand-over to our client according to milestone dates. And to do that I need to run several reports to track changes, additional scope of work being designed in, progress for each discipline as well as for each platform system and so on.

    So yeah... summed up, it's alot about information management. Which is also why I've seen the huge advantage of Excel's VBA programming. Now I just want to get a few steps further and figure out how to make a program that can fetch the data from the servers I want (as you see above, there's a bunch of databases and servers with information already so I don't need more databases... I think?), piece it together and output the reports that I desire based on different parameters each week.

    At the end of the day, it's all about cutting down on the workload for myself (and others) in the projects.

    I'm still poking around with the VBA (I quite enjoy the, relative anyways, simplicity of VBA). But I also started reading up on several C# courses and I'm going through the video-course from the app I downloaded (so far it's been a very good course and he does a good job detailing everything in his code so there's little, if any at all, room for questions or assumptions). My greatest (granted, it's only thing I tried so far) achievement in C# is making it prompt for my name and then display it back in the console. Baby steps, I know
    Last edited by xancalagonx; Oct 28th, 2012 at 04:20 PM.

  4. #14
    Board Regular
    Join Date
    Jan 2010
    Location
    UK, Shropshire, Shrewsbury
    Posts
    469
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Moving on from Excel VBA to another programming language - what to choose?

    Based on that response, I would suggest doing some research into Datamarts, and if you are Microsoft orientated then build on SQL Enterprise Edition (has some extra costs but significant benefits), Integration services (SSIS) and Analysis Services (SSAS). These will enable you to Corral all the data into a single database and develop reports and analysis from there in a structured manner. If you are not Microsoft orientated then Oracle, Crystal Reports, OBIEE and Universes is the direct competitor. You can also go SAS, Hyperion or a bunch of lesser data mart products.

    I would suggest that you get a specialist company in to give you the lowdown on benefits and costs.
    The are ten types of users in this world, those that understand Binary and those that don't

    Why do developers get Xmas and Halloween mixed up? Because OCT31 = DEC25

    Work: XL2010/Windows 7 over Citrix 12 on WYSE terminals
    Home: XL2007/Vista home Premium 32 bit

  5. #15
    New Member
    Join Date
    Sep 2010
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Moving on from Excel VBA to another programming language - what to choose?

    Many of the answer implicitly assume that moving on from Excel VBA also means moving on from Excel. It need not.

    I would suggest you get to know the .NET framework, but by moving your VBA code to VB.NET, still running inside Excel. That means you don't have to deal with too many new things at once (getting your head around .NET and Visual Studio can be quite a lot already).

    You can download the free Visual Studio 2012 Express for Windows Desktop as a start
    Using the free Excel-DNA library (which I develop) your VB.NET code can run inside Excel, as macros or high-performance UDFs. Patrick O'Beirne has written a really nice step-by-step guide on migrating VBA add-ins to VB.NET with Excel-DNA, so that would be a good start.

    By starting to use .NET (and getting used to Visual Studio) you will be able to access the many .NET libraries for maths, web access etc, and also make your own libraries that you can use in Excel or from a command-line tool or windows application.

  6. #16
    Board Regular strive4peace's Avatar
    Join Date
    Jul 2009
    Location
    Colorado
    Posts
    678
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Moving on from Excel VBA to another programming language - what to choose?

      
    I've enjoyed reading the wealth of information on this thread, especially on languages I have not used. VBA is a good place to start if you use Excel, Access, Word, or PowerPoint and can experiment so you can learn and implement logic. I have some chapters for learning VBA posted here:

    Learn VBA By Crystal

    Last year, I started learning C#. I found Bob Tabor's free C# course to be so good that I bought a lifetime subscription to his video tutorials:
    https://www.learnvisualstudio.net/start-here/

    However, with all the new stuff, I might recommend focusing on HTML5 and JavaScript, which can reference objects like C# or VB (which I like better). Here are a few of the slew of links I have for js:

    https://developer.mozilla.org/en-US/docs/JavaScript
    JavaScript Fundamentals
    JavaScript: from the Ground to Closures | JavaScript Tutorial
    Apache POI - the Java API for Microsoft Documents

    I like the way that the dev center for Windows store (for Win8 apps) allows you to see code without being able to run the download:

    Getting started with Windows Store apps (Windows)

    Pluralsight.com has some excellent tutorials but it is a subscription -- well worth the price if you can afford it.
    Warm Regards,
    Crystal
    Microsoft MVP, Access

    Remote Programming and Training ~ let's connect!
    http://msaccessgurus.com/

    Free Access Tools
    http://msaccessgurus.com/freetips.html

    free book
    http://www.accessmvp.com/strive4peace


    *
    have an awesome day
    *

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com