# Off Topic > The Water Cooler >  >  Python or VBA in excel

## songhaegyo

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.

----------


## Doc.AElstein

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??

----------


## sandy666

Choose:
C#C/C++JavaJavaScriptMySQLPerlPHPPythonRubyVBScript ASPVisual BasicXMLand much more...
it's up to you

----------


## songhaegyo

dude thats common sense

----------


## AB33

IMO




> Choose:
> C# -YesC/C++ -Best if you canJava -Still most popular in enterprise applicationsJavaScript- The only kid in the front-end town, but there is a hype of "Web Assembly"MySQL- SkipPerl- Almost deadPHP- Dreadful language, but still dominant in the sever-side townPython- Popular for big data and machine learningRuby- Not popular after "Rails" went to de-railVBScript ASP- DeadVisual Basic- DeadXML- 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.

----------


## Norie

Why concentrate on a specific language?

----------


## sandy666

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

so maybe assembler?  :Smilie:   :Smilie:   :Smilie:

----------


## scottiex

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/

----------


## AB33

> 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.

----------


## sandy666

Hahaha, 
I know: LOGO is the best!  :Smilie:

----------


## Doc.AElstein

> 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_

----------


## AB33

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.

----------


## Andy Pope

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

----------


## songhaegyo

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

probaly sholdnt go down that route and make myself obsolete

----------


## AB33

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.

----------


## Rajesh1981

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

----------


## AliGW

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.

----------


## songhaegyo

rajesh dont hijack my thread!!!!!

----------


## Doc.AElstein

Internet Explorer




> … 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? )




> ..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.







> 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 200*2* – 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….




> .. .. 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
 :Smilie:

----------


## AB33

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.

----------


## Doc.AElstein

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…

----------


## songhaegyo

about to start my python journey. Any help?

----------


## snb

> 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 ??

----------


## AB33

> 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.

----------


## snb

I think the wikipedia text is incorrect.

----------


## JP Romano

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.

----------


## AB33

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.

----------


## MrShorty

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?

----------


## AB33

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.

----------


## BellyGas

http://analystcave.com/vba-dead-whats-future-vba/

----------


## MarvinP

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.

----------


## sandy666

@MarvinP

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

----------


## MrShorty

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?

----------


## CK76

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

For java...
http://obba.info/

----------


## AB33

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?

----------


## buran

> 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/

----------


## Doc.AElstein

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

----------


## buran

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

----------


## AB33

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.

----------


## CK76

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).

----------


## snb

@CK76

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

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

----------


## CK76

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.

----------


## snb

@CK76

Thanks

----------


## AB33

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.

----------


## buran

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...

----------


## Norie

AB33

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

----------


## AB33

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.

----------


## AB33

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.  :Smilie:

----------


## Norie

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'.

----------


## AB33

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

----------

