Widening the VBA knowledge

Hi

What other branches of knowledge would widen the programming knowledge of a VBA Developer and how.
Like if I am an accountant ,only the accounting is not sufficient math also, statistics as well and "Business communication and report writing will make an accountant perfect.

What other skills will make a VBA Developer perfect?


TIA to Gurus/Experts

Thanks

Note:Offtopic but needed.

Best Regards
Imran Bhatti

Hi Imran,

Good question. VBA is used in Excel to automate repetitive tasks. Tasks that need to be done over and over again. Tasks that can be one in Excel or Word that repeat, so you write a VBA macro to go through the steps, so you save time and effort. Excel also is wonderful for sorting and filtering data on a single sheet, but what if it repeats? What if you need to combine multiple files, like 100 of them to get your needed answer? VBA can be written but there might be an easier way.

Excel has written a newer tool called Power Query or Get & Transform (in 2016) that uses "M Language". This extension to excel and dealing with multiple workbooks or files might be your next step beyond VBA. If you are in a company that networks and Microsoft is the standard then Power BI (Business Intelligence) would be a good extension beyond VBA. DAX is an underlying language for Power BI. Microsoft is also offering Azure as a platform for businesses that might lead you away from VBA. It seems to me, now that the world is collecting more and more data that Excel's 1 million rows limit isn't big enough. This might be a good direction to extend your VBA into.

In my opinion, to be a good VBA developer, you need mostly to fully understand the program(s) that you are using VBA to automate- since that is probably 90% of VBA usage. Using VBA purely as a language, as opposed to interacting with an object model, is quite rare.

Once I was to modify a userform code.The task was to show tooltiptext on MouseMove(it was ok for enabled controls but tough for disable ones). Anyway I had to add a specific code for as many times as the number of controls, in the said Event of all the controls. But after sometimes I learnt that there may a class module be created , the code of which can be shower upon all the controls. I realized if I were aware of this , I would have adopted this easy and robust approach at the first place.

I thought your question was about other branches of knowledge? That is simply VBA knowledge.

Originally Posted by AB33

The code took 56 minutes to run. I have tried all the tricks in the books to speed up the code, but these will result in few minutes saving.

I could easily draw quite another conclusion instead of blaming VBA.....

Generalising on n=1 doesn't make a sophisticated impression.

Sir MarvinP

I tried to record a macro while playing with the Power Query and saw a different code generated as compared to what we normally see in VBA scripts. So this is another other door of VBA (for me at least)

Originally Posted by xlnitwit

I thought your question was about other branches of knowledge? That is simply VBA knowledge.

Yes . My question is certainly about the other branches .I just mentioned about the class moduls because I did not know it. I think if a good mathematician learns VBA .He would stunn the world with his tricks in VBA due to mathematical tricks.

Hi Imran,

A few weeks ago I read the difference between a high level and low level language. A problem was given to each set and here was the answer.

Low Level would take 7 weeks to write the code and it would run in .01 seconds
High Level would take 1 week to write the code and it would run in 1 second.

In searching for a better answer I found this site, where I liked the conclusion:
https://www.educba.com/high-level-la...vel-languages/

On the other hand; I'm thinking I should learn to do a web site and searching myself for the "best for me" software to develope my site. I'm thinking WordPress but see there is a .com vs .org version of it. It also requires a Host (of which there are many) and FTP that I'm not very used to. Which tool should I learn??

On the other hand, my normal work (I work for free) involves showing others how to solve their problems (for my own ego or karma, I guess ) and wonder if a WebSite is the answer. Then I look at YouTube and see lots of training videos. If the writeing and reading world is shifting to the listening and viewing world; I should be learning to make Videos instead of websites. I have done some Camtasia videos and put them on YouTube already. SOO.. what is my answer. The correct choice will be to solve more of my future problems. Perhaps the time to learn more Video creation is the answer?

Originally Posted by AB33

I did not blame VBA but myself for not using the right tool.
If the right tool is a hammer, but do not have or do not know how to use it, I use a "Screw" instead. I then blame the screw for screwing up my job.

In all honesty, VBA does not stand a chance against other more widely used Web languages. With things like HTMLAgilityPack for parsing html is now the goto tool for things like that. Even with the "Microsoft HTML object library" you could spend weeks just constructing complex Regex patterns to get the data you want.

I just made a Web scrapper to get data from Amazon that is not included in the API, not only is Amazon's html a nightmare to work with , b/c the same item can have several different urls and when I say several, there can dozens of them. But , firstly you have to get around the issue of detection as a bot from Amazon itself, that in itself is a huge challenge with them, as they are getting increasingly good at detection, especially with ML AI these days. Anyway, I can get 50,000 pages of data and retrieve the data I need in about 15 min. But to do this , I needed to use multithreading with about 200-300 threads and unfortunately even with the hacks out there to make the VBA environment seem like it is multithreading, it is impossible, b/c VBA is single threaded.

So, even if you were able to work out the Regex to get the data you wanted and get a semi-multithreaded environment working inside VBA, it would take so long to get the data some where in the magnitude of days, maybe even weeks to get the needed data. By that time, I suspect Excel will crash anyway.