Formulas vs VBA

Questions about integrating Excel and Cymatic, programming, formulae, etc

Formulas vs VBA

Postby adenlark » Mon Jan 08, 2018 11:29 pm

Hello people

I'm curious to know peoples thoughts on using formulas vs using VBA - I'm asking in respect to general use spreadsheets, rather than excel bots running high frequency trades. I did some quick research into this topic a few months back, and the opinion of the 'internet experts' was along the lines that formulas were quicker for all but complex spreadsheets, as formulas could be read natively by whichever language Excel is written in, whereas VBA is a language running on top of Excel. Programming is above my pay grade, so that could be a load of nonsense for all I know...

For you experienced Excel users, or anyone with thoughts on the subject:
- Are there any speed and/or efficiency advantages to using VBA over formulas?
- is it better to do all calculations entirely in VBA, then populate cells with the output?
- is a combination of the two the best compromise? (i.e. using VBA to write formulas only where needed?

I guess the next logical step for my workbooks is the latter question - to use VBA to enter formulas only where needed - there's no point having say, 20 rows and several columns of IF statements to read through in a five horse race. Nig299 posted some VBA lessons here a while back - wherever you are Nig299, thanks!

Cheers
adenlark
Wealth can neither be created nor destroyed. It is simply transferred from one illusion to another. - some guy on the internet
adenlark
 
Posts: 83
Joined: Sun Aug 03, 2014 10:00 pm

Re: Formulas vs VBA

Postby Gavin » Tue Jan 09, 2018 10:35 am

Hi Adenlark,

There are certainly some formulae and techniques that cause Excel to be slow. I mentioned them here:
Excel lag

If you are planning to use any of those aspects that can cause lag, then you can usually replace them with VBA to do the same thing without causing the lag.

I haven't done benchmark testing of formulae versus VBA as a general principle, but I think you have accurately described the difference and I concur that formulae might be the most efficient option in general terms (apart from in the circumstances mentioned that can cause lag).

Once you get into really complex multi-column if statements, it sometimes makes more sense to use VBA instead, because it can be written in such a way that it is more manageable and understandable. You can nicely break down your VBA code into modules with elegantly named method and lots of comments. If you ever need to come back to amend your logic, it can be much easier to understand it in well commented VBA than using highly complex formulae in multiple columns. Imagine if more than one person was involved in maintaining the spreadsheet and its logic/code, then a well documented option is even more important. However in fairness, there are ways that you can comment and document a formula based spreadsheet, to help make it more understandable though, so again it is not a simple answer as to which is always the best option. If just using formulae, then you can improve their user-friendliness by inserting comments in cells, using named ranges etc.

As you stated that you are not looking to create high frequency trading robots, it might not be too critical which option you choose, formulae or VBA. It might end up being based on personal preference to some extent and partly based on what you are trying to achieve in each case.

Sorry that I haven't given a simple answer to your question, I just think that there are times when formulae are best and other times when VBA is best. Speed is one consideration, manageability is another.

Kind regards,

Gavin
User avatar
Gavin
Site Admin
 
Posts: 3832
Joined: Wed Jan 23, 2013 9:11 pm
Location: United Kingdom

Re: Formulas vs VBA

Postby adenlark » Tue Jan 09, 2018 1:33 pm

Hi Gavin

Gavin wrote:I just think that there are times when formulae are best and other times when VBA is best. Speed is one consideration, manageability is another.


I think this is an excellent point. Most of my sheets a formula-based, because I find them easy to write and easy to move columns or cells around as I optimize the layout. I've been experimenting with using VBA to insert formulas, and I'm stoked with the result - it makes for a cleaner sheet with no formulas in empty rows, and allows me to write simpler formulas, as I don't have to allow for rows with no data/no runners showing errors.

Gavin wrote:There are certainly some formulae and techniques that cause Excel to be slow.


Agreed - I avoid Select, Activate, Offset, Copy/Paste in my code, and even changing
Code: Select all
= ""
to
Code: Select all
.ClearContents
can make quite a difference in some cases. I've never got the hang of VLOOKUP or HLOOKUP. For those who need it, consider INDEX(MATCH) as it's faster and more powerful.

Prior to this mornings VBA tweaks, my sheet had ~300 formulas to recalculate on every refresh. I tested this against Cymatic last night with the streaming throttle wide open. I couldn't see any lag in Excel (plenty of lag in my eyes though!), and the sheet functioned smoothly even at <20ms refresh rates, so I'll stick with my current mixture of VBA and formulas.

Commenting - good advice! I've added comments to some helper cells, but I've been a bit slack in commenting my VBA, which is not helpful when I'm tweaking code.

Thanks for the feedback and advice

Kind regards

adenlark
Wealth can neither be created nor destroyed. It is simply transferred from one illusion to another. - some guy on the internet
adenlark
 
Posts: 83
Joined: Sun Aug 03, 2014 10:00 pm

Re: Formulas vs VBA

Postby Gavin » Tue Jan 09, 2018 4:59 pm

Hi adenlark,

Thanks, you make some interesting points and some good tips there.

I'm glad you mentioned how important it is not to use the Select command in VBA, it is good advice you give. For anyone wondering why, selecting cells in VBA is not necessary in automated strategies, you can just reference the cell directly without having to select it (selecting is like moving the cursor to it and is slow). Selecting cells is the sort of VBA code produced if you record a Macro instead writing the actual code. So I'd advise anyone is learning Excel VBA to realise that you won't get good code if you create your code by recording macros. Macros record all your keystrokes as you move the cursor from cell to cell, so they are littered with cell selection commands.

Cheers,

Gavin
User avatar
Gavin
Site Admin
 
Posts: 3832
Joined: Wed Jan 23, 2013 9:11 pm
Location: United Kingdom

Re: Formulas vs VBA

Postby adenlark » Wed Jan 10, 2018 10:29 pm

Hi Gavin

Slightly off-topic here, but I noticed in another thread (lay at odds 1000), you mentioned writing a custom spreadsheet for the user and providing a quote for the service. I wonder, would there be interest from the community (presuming you and I are not the only ones reading this) to have their spreadsheets confidentially assessed, and possibly rewritten, or advice given on where coding improvements could be made.

If I can get my VBA skills to the point where I can write my own bots, I would definitely consider paying a programmer to review my code and comment on any speed/efficiency improvements I could make. Although I've learnt a lot by finding code samples on stackoverflow and other resources, there is often more than one way of achieving a result, and these examples may or may not be suited to low latency automated trading. In short, it would be more time-efficient to pay someone to review (and optimize) my code, rather than spending hours reading and not really understanding technical jargon, in the hopes of finding the 'best way' to code something.

Cheers, adenlark
Wealth can neither be created nor destroyed. It is simply transferred from one illusion to another. - some guy on the internet
adenlark
 
Posts: 83
Joined: Sun Aug 03, 2014 10:00 pm

Re: Formulas vs VBA

Postby Gavin » Thu Jan 11, 2018 2:46 pm

Hi adenlark,

Interesting idea, thanks. I wouldn't be surprised if several users would be interested in using a confidential bespoke coding and advisory service if it was being offered by Cymatic.

My concern is that if I offered to do projects myself, that either my fees would be too high for most users, or if the fees were not too high that I might get overwhelmed with work and lose focus on the core product. That is why I have generally not offered to do quotations when asked, if at all. The 'Find A Developer' section of the forum has successfully helped introduce some users to third party coders, but perhaps users might prefer to obtain those services directly from Cymatic rather than third parties.

I am to build a bespoke coding and advisory service under the Cymatic name, then I'd probably want to get another coder onboard in order to help make it cost effective for users and to ensure that I don't lose my main focus.

Kind regards,

Gavin
User avatar
Gavin
Site Admin
 
Posts: 3832
Joined: Wed Jan 23, 2013 9:11 pm
Location: United Kingdom


Return to Excel - Help / Support

Who is online

Users browsing this forum: No registered users and 1 guest

cron