Excel Macro Language Compatability with Excel versions and MacOS Versions

  • I am hoping there is someone who still uses the Excel Macro language as I do. I am currently using Mac OS El Capitan 10.11.6 and Excel for Mac 2011 on a Mac Mini late 2014. It's clear that I will have to upgrade in the future but noone I've talked to knopws about how forward compatible the excel macro language is. Does anyone here have any insight on this. Much appreciated.

  • What do you mean how compatible is the language is? That question makes no sense to me because how compatible to what exactly? Are you asking how compatible is it to the newer VBA, well your version haves many older formulas so you can probably code what you have and it will still work with the new versions of Excel because they have all the old formulas and VBA in it plus new stuff. When you get your new computer or version of Excel it should work just fine with it. You need to google Mac Comparisons to Windows comparison. I have Office 365 and heard that the Mac version might be missing a couple of new formulas.

  • I used the old macro language for years, and had to support a company through a migration, with a major workbook being operated via old macro language functionality. It stopped worked on migration due to oddities in name definitions, an alteration very subtle in the way Excel works, causing a break-down in the logic of the macros. Until you try on a different version of Excel I don't think you can predict what could go wrong.

  • The old Macro Language is excel is very different from VBA. To give you a couple of very simple examples. To format cells as a % is not something one can do with a key stroke but with these 4 lines in an excel spreadsheet you can:


    Once you have designated the cell in which percentage is as an excel Macro with the keyboard equivalent of command option p. The language is very powerful and I use it to run regressions on ±50 mutual funds on a weekly basis. Excel for Mac 2011 works with this and Excel 2011 works with El Capitan so that is where I am stuck. I'm trying to find another user who has figured out what combination of Mac and Excel software will work. Hope this clarifies the issue I'm trying to get help on.



    A Macro to format selected cells as % without access to the pull down menu. [p]
    percentage
    =FORMAT.NUMBER("0.0%")
    =RETURN()
  • Reply to GlennUK. First I'm thrilled to hear from someone who is familiar with this wonderful old language. Do you remember if you got the old Macro Language to work with later versions of the MacOS and Excel?

  • Reply to GlennUK. First I'm thrilled to hear from someone who is familiar with this wonderful old language. Do you remember if you got the old Macro Language to work with later versions of the MacOS and Excel?

    I was working on Windows - Excel 2010 was the last version I did anything complex in (see my previous post). Like I said, you won't know what problems could arise until you try it out on the later version yourself ... can you do that?

  • I have pondered this for a long time. I am aware of the ability to run a second MacOS partition in a newer version of the MacOS and I'm sure Microsoft has trial versions but because most of the projects require action on spreadsheets the macros would require a lot of rewriting to effect those 'calls' on external sheets. Not impossible I accept but it just would be nicer to find someone who is using this old language and is more proficient than I, a 70 + retired person, am.

  • Yes indeed I am talking about the 'old macro sheets'.


    First I apologize for not putting the code in the correct format. I had not read the forum rules - so apologies again!


    I checked out your web site lots on VBA but nothing I could find on the old macros

  • I might know the old macro language, but I fully recommend a rewrite in VBA ... it's the only solution where other people can be used for maintenance and support (you are going to be hard-pressed to find anyone who knows the old ways).

  • How difficult is it to translate into VBA. I tried when VBA came out but found it too complicated. Maybe there are better resources now. I'll look on Amazon

    It is difficult to get into your head that you set the attributes in assignments, like:

    Range("E12").Font.Bold = True

    ... instead of issuing commands to set attributes.

    Microsoft issued a PDF in the 1990s which translated every Excel 4.0 macro command into a VBA equivalent ... I found that very useful during learning. I'll see if I can locate one via Google.

  • I've looked for info about the Excel 4.0 macro commands vs VBA, and it's not online anyway ... but, I printed something off back then, and I still have the print-out. If I get VERY bored I will scan it into a PDF, but it is dozens and dozens of pages, so would take a while in any case.


    As for hints, there are loads of things online for learning VBA, I just can't recommend any, as I learned VBA before such things were available, and so never had any requirement for training for myself.


    There are some tricks I've learned over the years, and here are a few:

    • VBE Toolbars -Edit: in the macro editing environment (the VBE), switch on visibility for the Edit toolbar, as there are a couple of buttons there that are really useful, being the Indent and Outdent buttons for shifting code left or right, and the Comment Block and Uncomment Block buttons ... all of the buttons mentioned work on selected blocks of code, which is very useful when making changes to code.
    • Declare your variables : you don't have to declare variables in VBA, which is great when doing something quick and disposable, but if you are writing anything even slightly more complex then declare your variables. This is the recommended thing to do anyway, and if you set Option Explicit at the top of your module, any spelling mistakes will be caught. But the real advantage is the autocomplete for declared variables ... I use Camelcase, which is prefixing variable names that indicate the variable type, so str for String, int for Integer, lng for Long, wks for Worksheet ... like :
      Dim intSectionCount as Integer, strTitle as String, wksInputData as Worksheet
      .. and then, when you are typing your code, and you start typing a variable name, after you've typed say 4 characters, press Ctrl+Space and the editor will automatically complete the name, or give you a choice of variables/functions that begin with those characters in a choosable tip. That saves me so much time, and I didn't even know about that feature for the first few years of programming in VBA.
    • Set objects to save typing ... you can set an object (create an object) that behaves as whatever you've set it as. That is hard to understand, so I'll give an example ...say you are writing some code, and you are referencing a particular workbook and worksheet ... I could set that worksheet as an object, like:
      Set wksInputData = Workbooks("Annual Collation Sales").Worksheets("Monthly Analysis Summary")
      ... then I can type wksInputData any time I was to use that worksheet object ... the object created is equivalent to the fully qualified object referenced in the Set command. You can imagine how easy that makes programming.
    • Alternative to Set ... With : Instead of setting up an object to reference, issue a With statement, and that allows you to perform a series of statements on a specified object without requalifying the name of the object, like:
      With Workbooks("Annual Collation Sales").Worksheets("Monthly Analysis Summary")
      .Range("A12").Value = "Hello"
      ... the Range object parent is not qualified, but because a "." is the prefix, it will take whatever is specified in the With statement .. a With block is switched off/terminated with an End With
    • For Each loops :- same as FOR.EACH() in old Excel 4.0 macros, but supercharged ... the VBA For Each loops with cycle through each member of ANY collection, whether in-built or user created. So you can look at every sheet in a workbook, or every cell in a range, etc etc, like:
      For Each wksCurr In Workbooks("Input Data.xlsx").Worksheets
      wksCurr.Range("A1").Value="Audited!"

    Hope that helps.

  • Glen this is loads of help. I've already been on the Microsoft excel board to ask if they have a copy of the pdf you mention. Your specific suggestions are illuminating to say the least and a little daunting. I learned basic in the early 70s which I found very simple (I was much younger then) and found VBA when I first looked at it had no relationship to the Basic that I had learned. That is essentially the moral here. The technology evolves. One of my macros does a series of regressions on about 50 mutual funds and interestingly it ran faster on a Mac IIfx in the early 1990s than it does today because of the increase in Excel overhead even though the computers today are way faster.


    THANKS AGAIN

  • An additional thought on your copy of the pdf. My brother who is England could pay you to ship it to me (I'm in the beer capital of the USA - San Diego). I'd scan it here and return it to you.


    Just a thought if you trust the mail systems!

  • aGlen this is loads of help. I've already been on the Microsoft excel board to ask if they have a copy of the pdf you mention. Your specific suggestions are illuminating to say the least and a little daunting. I learned basic in the early 70s which I found very simple (I was much younger then) and found VBA when I first looked at it had no relationship to the Basic that I had learned. That is essentially the moral here. The technology evolves. One of my macros does a series of regressions on about 50 mutual funds and interestingly it ran faster on a Mac IIfx in the early 1990s than it does today because of the increase in Excel overhead even though the computers today are way faster.


    THANKS AGAIN

    I can imagine it's all a little daunting ... I read about VBA on and off for about a year (1998), but was still only developing for users who only had Excel v3.0. So I knew a little VBA in theory, and I had that PDF/hlp file for Excel macro vs VBA printed out ... but then a user in the Far East updated their entire office with new software without telling us about it, and I had to rewrite a 600 row Excel v4.0 macro in VBA a.s.a.p., which was a massive learning curve (you know, VBA Forms instead of Dialog boxes, etc etc), but a great baptism into VBA.

    In 1999 I learned a lot about VBA, and some of the things were worrying, like, how much slower certain commands were in VBA (especially Page Setup for printing ... about .25 sec to 10 secs in VBA ... ended up using ExecuteExcel4Macro to use Excel 4.0 PAGE.SETUP function to speed things back up). Also, VBA operates a lot better if you don't select the cells you are changing (yeah, seems obvious now, but it wasn't then), like:

    Range("A12").Value="Done"

    instead of:

    Range("A12").Select
    Selection.Value="Done

    You can also turn off screen updating if you're updating a lot of cells, so that Excel doesn't have to try to keep the screen up to date with what you are doing ... that can be a big time saving.

    Be aware that the hints for Excel 4.0 macros to VBA was written for the old-style interface, so there are references on the menubar manipulation, which no longer makes sense. Also, the charting engine was redesigned for either 2007 or 2010, and so the charting commands cannot be guaranteed to be the correct VBA syntax/command.

    I find it very interesting that your code ran faster back in the day, but as you say, the increase in Excel overhead must be massive.

  • I'm making slow progress and have been able to successfully record use and save simple macros. I did wonder whether you have the title of this book you refer to:


    Microsoft issued a PDF in the 1990s which translated every Excel 4.0 macro command into a VBA equivalent ... I found that very useful during learning. I'll see if I can locate one via Google.


    If I had the title I may well be able to find it in a library around here.


    Thanks for your help - I don't think I would have accomplished what little I have without your encouragement and now I'm beginning to feel i could actually succeed! THANKS