Are you able to use other languages instead of VB for Excel macros?
-
Walkthrough: Office Programming (C# and Visual Basic) shows how to create an add-in that uses C# or VB.Net.– Sam HobbsOct 14, 2017 at 1:17
-
An add-in is not the same as a macro, at least to the extent that a C# add-in won't work for the Mac version. Right?– Sam HobbsOct 15, 2017 at 20:54
6 Answers
Yes it is possible to write excel macros in Python via pyxll
On a side note, you can even edit the existing macros in Python:
https://stackoverflow.com/questions/9913603/is-it-possible-create-edit-an-excel-macro-from-python
-
Very interesting. Do you know, how does the speed of invocation of Excel methods (i.e. round trip Python -> COM -> Excel -> COM -> Python) compares with the of VBA (in %)? I suspect it can be much slower, especially with arrays of Variant types. Oct 7, 2012 at 16:41
-
1Bare in mind this isn't free/open source - you need to pay £250/yr/user - @RobGale's answer is FOSS– icc97Nov 9, 2018 at 8:54
-
1@icc97 - not just that but if you send your file to other people, will they be able to run the macros without buying and installing pyxll too?– LWCOct 23, 2020 at 10:40
Another option is C# using .NET. Check out this link:
I've not used it much, but it might be worth a try.
-
Assuming it's free as claimed in this comment, what if you send your file to other people? Will they need to install it too in order to run the macros?– LWCOct 23, 2020 at 10:43
It is important to note, that since exactly all Excel automation capabilities available to VBA are in fact served through the COM interface, you can use any language, which supports Windows COM (ActiveX) objects. It roughly translates to almost every modern programming language on the Planet. I personally successfully used Excel from Wolfram Research Mathematica and pure C++. If you don't have specialized library for handling Excel object hierarchy (like the one that I presume exists for Python), it may not be that nice to use as with native VB.
Visual Basic 6 (and Visual Basic for Applications) were designed with compliance with COM architecture in mind. It means, that most data types defined for COM are native for Excel and they don't need to be transformed upon each call. So I'll suspect, that handling Excel from within VBA is the most efficient.
-
1The question asked about macros; those things executing within Excel. You are talking about the reverse; executing outside Excel. Oct 14, 2017 at 0:33
-
@user34660 I believe you are wrong. COM services offered by Excel are using Excel as so-called "Out-of-process". This means that whatever language you use to call those COM objects, the actual work will be done by a running Excel instance, e.g. one that is opened interactively by the user. Personally I have experience in using Excel objects from within R and C++. Oct 15, 2017 at 9:14
-
Execute Excel. In the Developer toolbar there is an icon for VB and a "Macros" icon. I see no other language available. The problem here is that the question is extremely vague. I think this question would be downvoted in Stackoverflow because it is so vague. It says "for Excel macros" so I see no way for any language other than VBA. Yes, VBA can use other languages and the question is super unclear about whether that is relevant. Oct 15, 2017 at 20:52
-
1@user34660 I agree, but in my experience it is not unusual. Remember, that posing a good question is already half an answer, and many people lack even that knowledge. What I like in SO is that this site gives view of different perspectives from which any problem can be understood. Oct 16, 2017 at 9:05
You can use C# to create Excel commands and functions with my ESharper add-in. The code can be edited and executed directly in a live Excel session.
AutoHotKey (.ahk scripts) also work with Excel, though I'm unsure how extensive the COM support is. You can check with their @Helpers through their Discord
Here's an example use case of indirect, synergistic usage that doesn't involve direct interfacing but technically qualifies as macroing inputs (if we're not being picky on vernacular specificity).
From Office 2016 onwards, you can use office.js https://docs.microsoft.com/en-us/office/dev/add-ins/develop/understanding-the-javascript-api-for-office
This also works on Office365.