+ Reply to Thread
Results 1 to 50 of 50

Python or VBA in excel

  1. #1
    Registered User
    Join Date
    06-20-2017
    Location
    london
    MS-Off Ver
    2010
    Posts
    38

    Python or VBA in excel

    Hi,

    I am fairly amateur at this programming move and all the hype to learn it. From what I see in my line of work, everyone seems to be moving towards python with its libraries being easily available. However, i've been using basic excel spreadsheets for ages (i am bad in vba).

    Theres a new need to actually move to either learn VBA now, or pick up python. What should i do? Start from scratch in python or pick up vba. i worry that picking up vba will make me more obsolete in the future when python is all the new craze.

    obviously the best choice is to do both but i just dont have the time and mental capacity... not to mention i'll probably get confused.

  2. #2
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Python or VBA in excel

    Hi songhaegyo
    You might want to check this existing Thread out:
    https://www.excelforum.com/the-water...thon-tips.html

    _._________

    I also get the feeling that people are thinking of learning Python as they think VBA might get a bit out dated.
    But I think VBA can do so many things, that even after ages of using it you find you can still learn new ways of doing things that you did not think possible, for example:
    Someone looking at alternatives to VBA mentioned to me the problem of doing things like multiple calculations on a column of data in VBA ( without looping) . I showed him how that can actually be done.

    Even if Microsoft “dump” VBA, I expect for decades the usefulness will keep people using it and people like the helpers here will be around to give support. I am feeling safe to rely on it as my main programming language to do just about anything I can think or ever wanting to do…
    But I should say I do not have a clue about Python. Maybe someone can suggest a good simple introduction source for me????

    Alan

    P.s. Googling tells me that Python has been around since the early 90’s , so I am a bit confused that it is regarded as the “new” craze to get into???? Maybe someone in the know can set me straight on that, or rather tell me why it is suddenly the craze now??
    Last edited by Doc.AElstein; 08-01-2017 at 03:18 AM.
    '_- Google first, like this _ site:ExcelForum.com Gamut
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE
    http://www.excelforum.com/the-water-...ml#post4109080
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/ ( Scrolll down to bottom )

  3. #3
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Python or VBA in excel

    Choose:
    • C#
    • C/C++
    • Java
    • JavaScript
    • MySQL
    • Perl
    • PHP
    • Python
    • Ruby
    • VBScript ASP
    • Visual Basic
    • XML
    • and much more...

    it's up to you

  4. #4
    Registered User
    Join Date
    06-20-2017
    Location
    london
    MS-Off Ver
    2010
    Posts
    38

    Re: Python or VBA in excel

    dude thats common sense

  5. #5
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Python or VBA in excel

    IMO
    Quote Originally Posted by sandy666 View Post
    Choose:
    • C# -Yes
    • C/C++ -Best if you can
    • Java -Still most popular in enterprise applications
    • JavaScript- The only kid in the front-end town, but there is a hype of "Web Assembly"
    • MySQL- Skip
    • Perl- Almost dead
    • PHP- Dreadful language, but still dominant in the sever-side town
    • Python- Popular for big data and machine learning
    • Ruby- Not popular after "Rails" went to de-rail
    • VBScript ASP- Dead
    • Visual Basic- Dead
    • XML- Still relevant for complex data exchange, but JSON has taken the shine.
    • and much more...


    it's up to you
    VBA:
    Do not waste your time on dead language. I still regret to this day, why I spent all these years learning it.
    Python has been eating a chunk from excel and will continue to do so in the near feature.

    Python is widely used in web development and applications. It is popular with big data, statics and machine learning.
    I had briefly a go at it, but indenting was a complete turn-off, but might a go again in the near feature.
    Last edited by AB33; 08-01-2017 at 04:55 PM.

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Python or VBA in excel

    Why concentrate on a specific language?
    If posting code please use code tags, see here.

  7. #7
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Python or VBA in excel

    MAC user: Mac is better than PC
    PC user: PC is better than Mac

    so maybe assembler?

  8. #8
    Forum Expert
    Join Date
    04-01-2013
    Location
    East Auckland
    MS-Off Ver
    Excel 365
    Posts
    1,340

    Re: Python or VBA in excel

    For what it is worth, here is The TIOBE Programming Community index which is an indicator of the popularity of programming languages.

    https://www.tiobe.com/tiobe-index/
    If you want something done right... find a forum and ask an online expert.

    Time flies like an arrow. Fruit flies like a banana.

  9. #9
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Python or VBA in excel

    Quote Originally Posted by scottiex View Post
    For what it is worth, here is The TIOBE Programming Community index which is an indicator of the popularity of programming languages.

    https://www.tiobe.com/tiobe-index/
    My view is then in tune with the market.

  10. #10
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Python or VBA in excel

    Hahaha,
    I know: LOGO is the best!

  11. #11
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Python or VBA in excel

    Quote Originally Posted by Norie View Post
    Why concentrate on a specific language?
    I suppose in my case it is partially ignorance of knowing any other programming language. I am not a professional programmer. If I was working in computers professionally , I think I would make a point of getting at least some basic knowledge of the most popular languages about.


    But I have occasionally delved into languages that might be considered more suitable to some of my requirements. But usually it has turned out to be more trouble than it is worth and with a bit of work and some novel solutions I have usually found I can get VBA to work as good or occasionally better..

    I think there can be some advantages in concentrating and specialising in one language, especially one so vast as VBA. As you delve deeper into it, you sometimes find ways of doing things not yet noticed by anyone that can give a “leap frog” in performance. That can sometimes make the typical performance comparisons of languages in certain areas pretty meaningless as you can find a novel solution that works really well for a particular application.

    For now, I have a good store of Office 2003, 2007, 2010 installation discs. I am feeling confident that that, and Forums like these will satisfy any programming requirements I have for some time.

    _..___

    By the way, I do not see VBA on any of the lists discussed here??, - Do I assume it comes under the title of Visual Basic?
    Strange, when I started a few years back I went on a short night school course in Visual Basic 2010, as it was the only introductory programming course available ( and I mainly needed to find out what Object Orientated Programming was about!! )
    The teacher made a point of saying VBA was something else. With hindsight the UserForms ( VBA ) and Forms ( Visual basic 2010 ) side of things looks very similar in both languages ????

    Alan





    https://www.excelforum.com/the-water...thon-tips.html
    Last edited by Doc.AElstein; 08-02-2017 at 03:17 AM. Reason: Added link to parallel Thread on Python

  12. #12
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Python or VBA in excel

    Hi Alan,
    You do not see VBA in the language discussion since it is not considered to be as a "Mainstream". It is an off-shot of VB-6 which was dead long time ago. It was designed specifically for office applications. VB-6 was replaced by VB Dot Net. If I were a betting man, I will put my money on Visual Basic family to face the fate of IE.

  13. #13
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Python or VBA in excel

    According to TIOBE's definition VBA is grouped in to Visual Basic
    Visual Basic: Visual Basic (confidence: 50%), VB (confidence: 50%), VBA, VB6

    @songhaegyo, if you are choosing a language for a specific need then you have to evaluate the requirements of the task and the easy with which either language will allow you to achieve them.
    If the choice is just a language then pick the one most appropriate to you every day use of programmed solutions. If you want code to automate the tasks you do each day in excel then VBA. If the automation is for data outside of excel then python.

    You may find the code academy an easy entry point for python
    https://www.codecademy.com/learn/learn-python
    Cheers
    Andy
    www.andypope.info

  14. #14
    Registered User
    Join Date
    06-20-2017
    Location
    london
    MS-Off Ver
    2010
    Posts
    38

    Re: Python or VBA in excel

    Thanks fellas - sounds like vba is gonna be dead in 10 years.

    probaly sholdnt go down that route and make myself obsolete

  15. #15
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Python or VBA in excel

    It is difficult to predict when VBA will be buried- it could 5, 10 or 15 years. It does not cost any for MS to ship it with new releases of Office products. It will be around so long as excel is around. There is a talk of "Web office works", but this is going to a long shot.

  16. #16
    Registered User
    Join Date
    08-02-2017
    Location
    India
    MS-Off Ver
    2010
    Posts
    0

    Re: Python or VBA in excel

    I have one doubt ... Please help me.
    I want to merge two excel sheet in following manners .

    ex- 1st excel has 2000 records
    A1 ( col1 col2 )
    AS LX
    BX LX
    CS LX
    CS LX
    CS LX
    2nd excel has 3000 records
    A1 ( col1 col2 )
    LX BS
    LX BS
    LX BX
    LX BX
    LX CS
    LX CS
    LX CS
    LX CS

    I want to merge them in below manner :-

    AS LX
    LX BS
    LX BS
    BX LX
    LX BX
    LX BX
    CS LX
    CS LX
    CS LX
    LX CS
    LX CS
    LX CS
    LX CS

    Regards
    rajesh

  17. #17
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,365

    Re: Python or VBA in excel

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  18. #18
    Registered User
    Join Date
    06-20-2017
    Location
    london
    MS-Off Ver
    2010
    Posts
    38

    Re: Python or VBA in excel

    rajesh dont hijack my thread!!!!!

  19. #19
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Python or VBA in excel

    Internet Explorer
    Quote Originally Posted by AB33 View Post
    … If I were a betting man, I will put my money on Visual Basic family to face the fate of IE.
    Hi AB33. Shame about IE too in my opinion, ( I liked that too. Although, since I got clued up through the forum on scrapping via the “VBA using HTML stuff” way, ( which I find great ), I do not need or use it so much. )
    _.______________
    Visual Basic ( What does that mean? )
    Quote Originally Posted by AB33 View Post
    ..not see VBA in the language discussion .. not considered a "Mainstream". - an off-shot of VB-6 which was dead long time ago.- designed specifically for office applications. VB-6 was replaced by VB Dot Net.
    Quote Originally Posted by Andy Pope View Post
    According to TIOBE's definition VBA is grouped in to Visual Basic
    Visual Basic: Visual Basic (confidence: 50%), VB (confidence: 50%), VBA, VB6..
    ( https://www.tiobe.com/tiobe-index/pr...es-definition/ ) Thanks Andy, Thanks AB33.
    It seems a bit of a mixed up jumble, but I think I am getting the jist of it.
    Visual Basic” can, in everyday usage, be used very crudely to mean the similar object-oriented programming language coding that the different “things” share. Somewhere along the line a “thing” which, amongst other stuff, used this coding was incorporated into a Visual Studio “thing”y, somewhere around 2005-2008. In fact what I learnt as Visual Basic 2010 probably comes under VB.Net, as does at least two others I know about, Visual Basic 2008 and Visual Basic 2013

    Actually if you google a lot you find the exact definition of where and what each Visual Basic “Thing” refers to, or belongs to, is actually a topic of debate, even amongst the professionals. So it is a bit of a jumbled up mess. It is one of those crazy situations in my opinion, - even the people who wrote the stuff are not quite sure what it is they are talking about… the first step in computers taking over , I guess….
    _.______
    VBA and Microsoft. / Python or VBA
    Possibly there is a parallel to a car Model and a car manufacturer…

    By chance they make a particular Model that is really great and reliable and seems to run great for ages. So people with them take care of them and don’t buy a new car for ages.
    The manufacture gets nervous of loosing new car sales so brings out a few newer versions of the car that don’t last or work too well, and then a few years later the manufacturer discontinues and dumps the support on the entire model range.

    Those driving the company financed Company car are forced to update to get the newest gadgets so they have something to talk about on business trips and at the workmates barbeque party. They don’t care about the latest diesel scandal or the lingering smell from when the kids puked up in the car over the weekend – the car will just keep being updated at the companies expense when the ash trays are full anyway.

    People driving their own car that happens to be that great model, might sensibly get a good stock of spares, all the user repair manuals, and get to know their reliable model and so be self sufficient in satisfying their transport requirements for a few decades.

    I am guessing that somewhere around the 2002 – 2010 era , Microsoft accidentally made a great product in VBA in Office. So they have done a few later versions of Excel that are not so good and will discontinue all support for, and dump it in a few years. ( I do not have the 2013 and 2016 office/ Excel versions myself, but I have read a lot of threads where members, including some very senior, who moan about a lot of bugs and the lack of support from Microsoft when reporting them. ( Opinions seem to vary between XL2003 and XL2010 as the most stable versions. )

    I think I will keep my 2003, 2010 Excel VBA up and running, possibly on some older computers and keep them mostly away from the internet to prevent anyone sabotaging me through a sabotaging virus, or a Microsoft update, which are sometime the same thing….
    Quote Originally Posted by Andy Pope View Post
    .. .. If you want code to automate the tasks you do each day in excel then VBA. If the automation is for data outside of excel then python.
    I mostly use VBA to automate my Excel daily tasks. But I do also pull in a lot of external data sometimes, or part thereof. I tried the more apparently appropriate languages for the large data manipulating side of things, but found some novel solutions in VBA involving efficient search codes and external closed workbook references to actually work better than anything, so I can do it all with VBA better in my case…

    Bottom line is from me then:
    _ Q : Python or VBA
    _ A : In my opinion, VBA, but get well “stocked up” on the earlier versions and keep involved with excelforum for continuing support
    .


    Alan
    Last edited by Doc.AElstein; 08-03-2017 at 04:36 AM.

  20. #20
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Python or VBA in excel

    Hi Alan,
    Well, MS does not have a choice when it comes to browse. Chrome has taken over the top position with 68% of market share since its debut. IE does not even come close to second- FF with 24%.

    I can not imagine anyone on the web community will mourn the passing of IE. I have not found a single person whose main job is to develop a web likes IE. I have seen this comment on many web pages
    “IE is affront to mankind”.

    You also see many ifs statements on JavaScript to accommodate the different versions of IE. In short, it is a nightmare to use IE.
    I cannot remember the last time I am re-minded by Chrome to upgrade to newer version- It does it seamlessly.
    MS will no longer ship the Active X object with ME (Microsoft Edge), so there will not be any support on web scraping using ME. You can still use IE on VBA.
    MS made it clear that it is no longer actively support IE and will die from old age.

    The jury on ME is still out and will not make any comment until it reaches a verdict.

  21. #21
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Python or VBA in excel

    Thanks AB33,
    I confess I do like the right click source code view in Google Chrome… but I just don’t like “Big Brother” Google getting so intimate with me. … Google … you hate it, but it is difficult to live without it unfortunately..
    I must get around to playing with Fire fox one day…

  22. #22
    Registered User
    Join Date
    06-20-2017
    Location
    london
    MS-Off Ver
    2010
    Posts
    38

    Re: Python or VBA in excel

    about to start my python journey. Any help?

  23. #23
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Python or VBA in excel

    MS will no longer ship the Active X object with ME (Microsoft Edge), so there will not be any support on web scraping using ME.
    What's the relation between activeX-objects and webscraping ??



  24. #24
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Python or VBA in excel

    Quote Originally Posted by snb View Post
    What's the relation between activeX-objects and webscraping ??


    ActiveX is a software framework created by Microsoft that adapts its earlier Component Object Model (COM) and Object Linking and Embedding (OLE) technologies for content downloaded from a network, particularly from the World Wide Web.




    ActiveX - Wikipedia



    https://en.wikipedia.org/wiki/ActiveX

    May be my understanding was incorrect.
    Last edited by AB33; 08-07-2017 at 05:11 PM.

  25. #25
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Python or VBA in excel

    I think the wikipedia text is incorrect.

  26. #26
    Forum Contributor JP Romano's Avatar
    Join Date
    10-09-2008
    Location
    Princeton, NJ
    MS-Off Ver
    2010
    Posts
    500

    Re: Python or VBA in excel

    I've been hearing that VBA will be dead in x years for more than x years now. I still develop robust, dependable, easily distributed apps with it, many of which are used by my peers globally. It comes right out of the box with what you need to get going. You don't have to figure out what IDE's / Editors / Versions and modify class paths and figure out github nonsense. It just WORKS. And it's explicit and logical...

    And I'm *terrible* at it.

    I'm yet to come across a real world situation or problem that is better solved with python than VBA. I will admit, though, that the people who use python do higher level analysis of superlarge datasets (hundreds of millions of records) which falls outside of any use case I've come across in the past decade.

    I admire people who can pick it up and get it do *anything* useful. After tinkering on and off with it for over a year (2 training classes and countless youtube tutorials), I find that when I have a real problem to solve, an app to build, or just need to get something DONE, I revert back to VBA. And it works, every single time.

  27. #27
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Python or VBA in excel

    1 million pigeonholes is not something you can call an application.
    By "Dead" means, it is not longer actively supported by the owner of the software. COBOL was developed in 50's and yet it is still used by some Banks. It costs nothing for MS to ship it out with every version, but the whole Basic language is dead man walking.

  28. #28
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Python or VBA in excel

    I don't know if this appropriate on an Excel specific forum, I have Open Office installed on one of my computers. I happened to pull up the Macros dialog today and noticed that Open Office has built in support for Python as a macro language. It seems that Excel/MS Office is the main thing that keeps VBA alive because this is the chosen macro language the MS Office supports and encourages.

    With this discussion in mind, is there value in dropping Excel/MS Office and choosing a different spreadsheet/office suite -- one that has built in support for Python or other non-dead language as its macro language? For myself, I don't think this would be overly difficult -- the hardest VBA I have programmed I already translated from Fortran to VBA, so I don't expect it would be overly difficult to tranlate it again (though does this old dog want to).

    Perhaps this is more a question for those in schools educating the upcoming generation or others in a "just starting out" position, before they have built up a lot of library of "legacy" VBA macros. Is there value for someone in this position (or advising those in this position) to choose other office suites over MS Office because they expect to need macros somewhere along the way and anticipate the value of learning something other than VBA for the macro language?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  29. #29
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Python or VBA in excel

    VBA is not supported on excel's on-line version. Google's spreadsheet is JavaScript browse based. It does not have the full features of excel. From my very limited knowledge, JavaScript was not designed to do the heavy lifting of data storage. MS has been fully aware that the way forward is NOT Desktop office applications, BUT CLOUD. MS has been signalling and working on "Office Web Workers" applications. There has been some limited JavaScript adds-in (API) being added to excel.

    https://dev.office.com/reference/add...api-for-office

    https://msdn.microsoft.com/en-us/magazine/jj891051.aspx

    Python is getting popular with big data and machine learning. The phrase "Data Scientist" is largely associated with Python.
    Last edited by AB33; 09-23-2017 at 04:28 PM.

  30. #30
    Valued Forum Contributor
    Join Date
    03-24-2014
    Location
    England
    MS-Off Ver
    Excel 2003 - 2016
    Posts
    575

  31. #31
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Python or VBA in excel

    Hi songhaegyo,

    I have another thought on this topic. Microsoft prides itself on building tools for programmers. The newest add-in which is now part of Excel 2016 is called Power Query or Get & Transform in the 2016 versions. It is much easier to learn than VBA and is like a selective filter which can bring many different types of data structures into Excel to be analyzed. If you are good at Excel already, get a taste of Power Query and look up some extra "M Language" functions. Build off of what you know, instead of learning something completely new.

    https://support.office.com/en-us/art...6-59e18d75b4de
    https://support.office.com/en-us/art...9-c6d29cb8df6a

    Power Query is available for 2010 and 2013 excel as an Add-In also as they have made it backwards compatible.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  32. #32
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Python or VBA in excel

    @MarvinP

    add DAX for PowerPivot to complete with PQL ("M")

  33. #33
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Python or VBA in excel

    Another addition to this discussion. I came across something like xlwings (https://www.xlwings.org/ ) that provides an interface or whatever is needed to allow one to use python from within Excel. What do you make of something like this, that allows one to use a more modern programming language in Excel? Are there other similar utilities for Java, Javascript, or other languages?

  34. #34
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Python or VBA in excel

    There's some for R. One of more well known one below.
    http://rcom.univie.ac.at/

    For java...
    http://obba.info/
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  35. #35
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Python or VBA in excel

    You can write Python code in VBA editor. This is very interesting to explore. I do not know how this works. Do you need a Python interpreter some where in the VBA environments or is it adds-ins?

  36. #36
    Forum Expert
    Join Date
    06-25-2009
    Location
    Sofia, Bulgaria, EU
    MS-Off Ver
    Excel 2003-2013
    Posts
    1,290

    Re: Python or VBA in excel

    Quote Originally Posted by AB33 View Post
    You can write Python code in VBA editor. This is very interesting to explore. I do not know how this works. Do you need a Python interpreter some where in the VBA environments or is it adds-ins?
    https://www.xlwings.org/
    http://ericremoreynolds.github.io/excelpython/
    https://www.pyxll.com/
    Last edited by buran; 11-02-2017 at 02:57 AM.
    If you are pleased with a member's answer then use the Star icon to rate it.

  37. #37
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Python or VBA in excel ..... OR .... .. Python for Excel. ..

    Hi,
    Here is some news that I just recieved, which I think is worth droping a quick note about in this Thread.. it is about a ..".... Excel Uservoice and Python for Excel.."..
    https://newtonexcelbach.com/2017/12/...hon-for-excel/
    https://excel.uservoice.com/forums/3...pting-language

    Worth a look I expect for people following this thread.....
    Alan
    Last edited by Doc.AElstein; 12-29-2017 at 01:12 PM.

  38. #38
    Forum Expert
    Join Date
    06-25-2009
    Location
    Sofia, Bulgaria, EU
    MS-Off Ver
    Excel 2003-2013
    Posts
    1,290

    Re: Python or VBA in excel

    Yeah, I also received an e-mail asking to fill in the survey. Already did it.

  39. #39
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Python or VBA in excel

    Hi Alan,
    Interesting!
    I suspect there is politics involved. Unless MS has been swayed by the popularity of Python, it could have considered this option long time ago. Python has been around longer than VBA, after all. It is quite often, big companies tend to buy a niche player, swallow it and then inject some resource to scale it up. This is how Google has been doing business for long time. You Tube, Android and Google Docs are some examples.
    I think MS has also bought a company which developed PowerPivot (PV). PV was initially deployed as third party add-ins before it was fully integrated in to excel.
    I do not know a lot about Python, but it seems to me some of the popular Python's add-ins are popped up recently. So, MS has not looked in to them as a serious viable business. It appears that MS has been embarked on JavaScript bandwagon.

  40. #40
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Python or VBA in excel

    Tested xlwings and it's pretty useful and simple to use.

    Few notes.
    1. If you don't have absolute preference on Python interpreter, use Anaconda. It has all the modules needed (or recommended) by xlwings included out of the box.
    2. Make sure you set up environmental path for folder containing python.exe and scripts folder.
    3. I'd recommend not using default installation path. Use something that's easier to get to. Ex. C:\Anaconda\
    4. Install xlwings using conda

    While it will work with many other python interpreter, I found Anaconda to be simplest to set up the environment.

    Main benefits that I see so far...
    1. Multi-threaded processing
    2. BeautifulSoup - Simplifies web scraping process
    3. cleaner code structure
    4. Multitude of modules designed for specific operation (from imaging library to machine learning).

  41. #41
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Python or VBA in excel

    @CK76

    Did you find any good Python UI like a VBA Userform ?

    It's a pity Anaconda is still Python 2., not 3.

  42. #42
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Python or VBA in excel

    I'm running Anaconda with Python 3.6.3 @x64, with spyder as IDE.
    Edit: To clarify there are 2 Anaconda distribution, for 3.6 & 2.7

    https://www.anaconda.com/distribution/

    I haven't delved much into UI, there are few options.

    https://opensource.com/resources/python/gui-frameworks
    https://wiki.python.org/moin/GuiProgramming

    When using xlwings, I use Excel sheet as UI to pass variables if needed.
    Last edited by CK76; 01-10-2018 at 09:11 AM.

  43. #43
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Python or VBA in excel

    @CK76

    Thanks

  44. #44
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Python or VBA in excel

    GUI in Python? It is an absolute joke.
    I spent 2 days to get a simple 2 user forms in Tkinter to work, while it took me 2 minutes (Drag & Drop) to get the same 2 forms in .NET(C#). When it comes to GUI, Microsoft products shines and this is the area in which I miss MS badly. Despite Tkinter is a third-party library incorporated in Python, it goes against the very ethos of Python- User-friendly- It is nether user friendly, nor is intuitive.

  45. #45
    Forum Expert
    Join Date
    06-25-2009
    Location
    Sofia, Bulgaria, EU
    MS-Off Ver
    Excel 2003-2013
    Posts
    1,290

    Re: Python or VBA in excel

    well, the only advantage of Tkinter is that it comes pre-installed with python distribution for Windows as well as some Linux distributions.
    There are much better (in every aspect, incl. aesthetic).
    Some of them have drag and drop visual UI design tools if lack thereof is a deal-break.
    extensive info available at https://wiki.python.org/moin/GuiProgramming

    EDIT: Didn't notice CK76 already provided the same link...

  46. #46
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Python or VBA in excel

    AB33

    What sort of 'forms' were you trying to create?

  47. #47
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Python or VBA in excel

    Hi Norie,
    I believe the class (Object) is called "Form" in all Dot Net platform and including VBA. A form could be a component or container for other forms. I just want 2 forms with Buttons, List Boxes, Text Boxes, etc. and should be able to easily switch and navigate between the forms.

  48. #48
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Python or VBA in excel

    Hi Buran,
    Thanks for the link!
    Drag and Drop is a big deal for me.
    As a learner of Python, I would like to focus on certain parts of a code or project. It might be easier for you to navigate through Tinker and get some widgets in no time, but spare a thought for novice like me.

  49. #49
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Python or VBA in excel

    AB33

    You do know that that type of functionality really doesn't really have anything to do with the programming language, it's the programming environment, i.e. IDE, that allows you to 'drag and drop'.

  50. #50
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Python or VBA in excel

    Hi Norie,
    Yes, still .NET methods and properties on components are much simpler and intuitive than Tinker.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Calling Python code from Excel VBA
    By trizzo in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-17-2017, 04:21 AM
  2. python in excel
    By idandush in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-05-2015, 04:26 PM
  3. Can you use (or compile?) Python code in Excel
    By mrvp in forum The Water Cooler
    Replies: 7
    Last Post: 06-08-2014, 06:49 AM
  4. call a python script from excel vba
    By sheffieldlad in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-28-2013, 07:11 PM
  5. [SOLVED] Can Excel solve my problem or do I need Access / Python?
    By MHT888 in forum Excel General
    Replies: 11
    Last Post: 01-13-2013, 02:54 PM
  6. Python commands in Excel
    By ljoseph in forum Excel General
    Replies: 0
    Last Post: 09-18-2009, 07:02 AM
  7. Excel and Python
    By davidlawrence in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-24-2008, 08:42 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1