Often when developing Macro's that operate on a worksheet, one needs to store / access worksheet variables that are either:

1. visible on the sheet or


2. saved in another hidden sheet.

It may be preferred by a Developer if custom meta data could be appended to the sheet properties. This will allow for custom sheet properties to be attached to a sheet rather than referenced from a hidden master sheet.

I appreciate that there may be performance considerations for those people who would like to have a customisable sheet property attribute, if so, it could be controlled by having a global option that turns this feature on or off.

Personally, I think this solution would be welcome by Developers who code in any language as it will be easier and elegant to ensure new sheets inserted into a worksheet have the appropriate custom settings whereas modifying the typical master hidden sheet can be tedious and extremely verbose by comparison.

There are some other ideas here that mention information and sheets, but they do not offer much on the implementation which is why I have posted this new suggestion.

I have a simple table(=listobject) with a UDF in it, something like this:

Public Function TestUDF(varTest As Variant, rngZZTest As Range) As String

TestUDF = rngZZTest.Value & varTest

End Function

Now I save the workbook in Excel 2016 and open it in Excel 2013 (32Bit). Excel jumps into the VBA editor, highlightes the UDF and an error occurs: "Compile error: automation error" (without any error number). I can compile the code and save it in Excel 2013, then the error message disappears. But always if I save the workbook in Excel 2016 and open it in Excel 2013, the error message is back!

I can open the workbook without any problems in Excel 2016, 2010 and 2013 (64Bit).

Hello Excel User Voice,

I have a macro that worked fine for over a year, but after the latest Office update something changed and the macro behaves differently.

Initially, I thought that it was the Insert method that changed, however, having tested this now i can see that it's the Range.Unmerge method that behaves differently.

We have users with three different Office builds in the team:
1. 16.0.4498.1000
2. 16.0.7329.1054
3. 16.0.8201.2075

For users on build 1 and 2, Range.Unmerge method unmerges the cells AND clears contents of clipboard AND/OR sets the Application.CutCopyMode to False.

For users on build 3 the same method only does the unmerging part.

I created a test version of the macro and recorded it with Steps Recorder on two different machines (see the attached files).

Recording 1 is an older build (16.0.4498.1000 64-bit). It just happens to be a 64-bit version, but the behaviour is identical on 16.0.7329.1054 32-bit.

Recording 2 is a machine with the latest Office update installed - 16.0.8201.2075 32-bit.

As you can see in Step 9 of Recording 1, the clipboard gets cleared on Range.Unmerge hence Range.Insert just inserts empty rows (step 11).

And on Recording 2, on step 9, you can see the paste options are not greyed out as the range is still in clipboard, hence the result of Range.Insert is different.

Why has this changed?

J.

As you can see by many posts, we developers heavily utilize VBA. As versions change and items are removed, we have to adapt our macros accordingly. Most of the time major changes in the VBA language happen at major version upgrades (e.g. 2007 to 2010 to 2016).

Recently Version 1705 started to be pushed out to people. I have a simple bit of code that enables the events for all workbooks that open. Prior to Version 1705 it worked perfectly, but now it fails on the variable definition. (Dim x As New EventClassModule now gives an error - "Compile error: Automation error Catastrophic failure".)

EVERY online write-up of this operation has a similar structure. NO WHERE can you find the new way to accomplish this task.

Here is the full code:

[In a module]

Dim x As New EventClassModule

Sub ClassModuleInit()

Set x.App = Application

End Sub

[In a Class Module]

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)

[code here]

End Sub

Half of the office has been pushed the update and are no longer functioning. The other half are still working on an older version.

All of the blogs say roll back to an older version. This will only temporarily solve the problem. We need better documentation of changes and ways to update our macros.

(Also an immediate solution for my issue would be greatly appreciated.)

Thanks.

The macro recorder & editor can be improved on by giving the user the ability to change the macro's programming language, on the fly, while editing. The macro editor should have a built-in language converter.

Before you say it can’t be done, remember that we are at a point where AI is evolving so fast that the “can’t be done” list is evaporating before our very eyes, every day. Also, if Microsoft is indeed ‘Infusing AI Into Every Product and Service’ It Offers:

https://www.seeker.com/tech/artificial-intelligence/microsoft-is-infusing-ai-into-every-product-and-service-it-offers

then why not programming itself?

So back to the improved macro language converter, I would recommend starting with some simple functionality:

• Support VBA (that is, vbscript with typing), Python, and JavaScript to start (and more in the future, including Perl, Lua, Applescript, PowerShell, Ruby, Scratch, PHP, UNIX shell script, Scheme/Lisp, CoffeeScript, Pascal, Tcl, Pure, Erlang, Piet, COBOL, various .NET languages, etc. in the future). This is just a way to give people the option to use whatever syntax they want.




• For now don't get into the difficult-to-translate features of the language, i.e. closures in JavaScript that are not possible in VBA. Just support the basic procedural and object-oriented functionality that VBA currently supports, which can be automatically translated to & from JavaScript and Python syntax.




• This would not be limited to record-time, where if a macro is recorded in Python syntax, it would stay in Python. Rather, the editor will be able to translate from one language to another while editing just by the user selecting a different language from a dropdown.

This will attract more users to use macros, and make it easy to invest their time in learning a language.

It is time to bring back easy visual programming:

1. The macro editor should support a GUI UserForm editor, where you can drag-and-drop controls onto a form, and easily double-click on them to edit their code, etc., just like in the main MS Office products, Visual Studio, and VB6. Make it easy for the regular user to harness the power of their computer in general.




2. Make it easy to use Web-based APIs: there should be a simple GUI way to reference a Web API, like VBA currently lets you reference a COM object. You reference the library, and then you can just dim a variable as whatever types the library makes available, and you get Intellisense for all the methods. You would be able to examine the class in the object explorer, etc.

It's 2017, let's get back to making programming EASY again, and empower regular people to automate their tasks! Visual Basic 6 and VBA were a revolutionary step forward with this, but somewhere along the way, mainstream programming languages and tools threw away a lot of the ease of the visual programming aspect. The expectation changed to be that the user should educate themselves, but the truth is that a lot of the best practices and concepts are more complicated than ever. If anything, there is just more out there. Way too much information for a casual user or non-career programmer to have to know, to create working automation as with the VBA macro recorder. The world needs this kind of ease again, badly.

With VB 6 and VBA you didn't have to learn 1000 different frameworks and design patterns and protocols, it was possible to just kind of wing it. As long as you knew some basics of procedural and OO programming, you could reference a library and figure it out with IntelliSense and the object explorer.

With strong AI and machine learning, we should be able to have tools that take this even further to help regular people create tools & applications and automate tasks (and to educate people to design better while they are programming. I think a big push behind all the current user-unfriendly programming methods and complicated design patterns was prompted by all the spaghetti code that novice programmers were producing back in the early days of the Web (Perl, classic ASP, PHP, etc.) and Office. If software developers are reluctant to open the gates to programming to “the uneducated masses”, then make the tools smart enough to help them create better designs, learn better programming and/or unravel the spaghetti already there. Those capabilities are here now with AI (or quickly emerging).

I think the Macro Recorder is one of the best features of Word and Excel - not only does it help to quickly automate tasks, it also helps learn how to program macros. I am baffled by why this feature is not supported in Outlook (and all the Office apps. And even Windows Explorer! It's not like we can't already automate Windows with VBScript, why not make it easier and provide a full-featured editor like Office's VBA editor.)

So in the bigger picture, I think Microsoft ought to include this macro recorder and language translator in ALL their Office and Windows products, and the OS itself (both desktop and mobile).

This will not only make all their products more usable and easy for novices to program (and learn to program), but it will give Microsoft a HUGE edge over their competition at Apple, Google, etc.