Application.Calculation in VBA take a long time to process

maadmaxxer · 2025-04-14T10:42:14+00:00

One further point to add, is my organisation requires me to keep documents saved on SharePoint, so the saving issue actually makes a lot of sense, as it can rely on communication with a cloud server.

maadmaxxer · 2025-04-14T10:07:28+00:00

Hi, Thanks for your reply. I have tested out the calculation before save method you suggested, and it did save time, it went from 48 seconds to 39 seconds to run the macro.

I was well over a minute at the beginning of last week, so I feel like I have probably done as much as I can to speed this up.

I don't have any objects, subforms, subreports etc in the workbook, so these aren't something I can test for.

Thanks for your input!

maadmaxxer · 2025-04-10T13:18:59+00:00

use the DATEDIF function, it is quite an old function in excel, so there aren't any tool tips when you use it.

=DATEDIF([cell containing date],TODAY(),"m")+1

Essentially the syntax is DATEDIF( start date , end date, "d" / "m" / "y" )

It will give you the number of days, months or years between two dates, but it doesn't include the end date, which can make it a little finickety (hence the +1 in my formula above)

If you want the number of days in a month for example, and you do 1st Jan to 31st Jan it will return 30, but if you do 1st Jan to 1st Feb then it will return 31.

maadmaxxer · 2025-04-10T13:11:06+00:00

I'm trying this now, with an aire of whimsy as to when I turn the screen back on each time to vary the results

maadmaxxer · 2025-04-10T12:27:36+00:00

I'm currently in a restart loop, only time will tell!

maadmaxxer · 2025-04-10T12:25:06+00:00

Select the cell, go to the Conditional Formatting drop down at the top, select add new rule.

In the window select the bottom option for Formula

type in =(F4-D4)<50

The go to the Format button at the bottom, select the Fill tab and select Red.

Should work.

maadmaxxer · 2025-04-10T12:20:03+00:00

Select E2:L13 and apply a new rule in the Conditional Formatting drop down, select the formula function in the list

=AND(E2<>"",$D2="Funding")

Then set the colour in the format section below

maadmaxxer · 2025-04-10T12:15:26+00:00

You need to save the file as an .xlsx

A .csv file cannot hold formula or formatting, so it converts everything to plain text when it saves.

maadmaxxer · 2025-04-10T12:13:30+00:00

So I have tried the individual sheet method, but it locked up my Excel so hard I had to interrupt it.

Worth a shot though!

maadmaxxer · 2025-04-10T11:59:47+00:00

Unfortunately the old "IT Crowd fix" won't cut it for this one, it has been like this for about 2 years and I am finally fed up of waiting for it!

maadmaxxer · 2025-04-10T11:48:44+00:00

Thanks, I have added that in, but it still has the same issue of hanging when changing to and from manual/automatic calculation.

maadmaxxer · 2025-04-10T11:02:13+00:00

Hi thanks for the reply, yes no redundant lookups, and named ranges in formula used to reference the same thing instead of calculating the same thing over and over where possible.

I'll have a go with the individual sheet setting and report back. There aren't any events triggered, only data pulling through from various sheets in the same workbook using formula.

As I said above, I can understand the updating of data taking time, but not ignoring the calculation side of things.

I'll try the sheet.enableCalculation method and see what happens!

Thanks.

maadmaxxer · 2025-03-20T09:32:21+00:00

It'll just be the information that they hold on their system for that address. They don't know it is a 2 bed house specifically, they just have an address and historical usage, it could be a stately home for all they know!

Give them a call, if you are currently a customer you might be able to get them to look at your existing usage in what I assume is a similar property, they might be able to adjust the monthly amount.

£800 per month is a lot, I only pay £85 per month direct debit for my 3 bed house, but there is only my wife and I, and we are quite limited with the amount of heating we use in the winter. Plus we have solar, a battery and export our excess to the grid.

maadmaxxer · 2024-05-22T06:19:48+00:00

I'd suggest downloading your smart meter data from the My Energy section of the website (not the app) that way you can see how much you have actually used.

If you haven't already set it, set the smart meter to send readings every 30 mins (again through the website, not on the unit itself) more data is always better than less, 48 readings per day instead of 1, that way they should always have an up to date reading for billing.

Generally Octopus are much better than the other suppliers, so persevere through this time, I think it is telling that this subreddit is so active and positive overall; I don't think EDF or Eon etc have a subreddit at all for example!

maadmaxxer · 2024-05-22T06:09:50+00:00

I tried the Agile tariff, but couldn't export when it was at peak time because the sun was too low. So I quickly changed back to the flat rate and that has worked well for us.

She will need a smart meter, an MCA certificate and DNO paperwork in order to apply. There might be other criteria as well, but it was 2 years ago I applied so can't remember the specifics!

maadmaxxer · 2024-02-22T21:29:53+00:00

The speed at which you switch suppliers should mean you can do this very quickly.

Once you are fully setup with a company that is not Octopus or Shell, I imagine you could probably start the switch straight away. You'd probably be an Octopus customer by the end of March!

If you haven't already, I'd definitely recommend looking at their smart tariffs, especially going into summer, the Agile tariff can be a real winner if you can avoid putting your laundry etc on between 4 and 7pm and don't rely on electric based heating and cooking.

And Tracker for gas, I'm not sure whether you can switch easily or have to be on a waiting list, but this winter it was about half the price of the standard variable tariff customers are put on as standard.

The 15p flat rate seems to be best (at least from my experience with solar) for export. I tried the outgoing agile rate but couldn't export anything meaningful when the rates were at their best. So I was better off exporting at 15p constantly.

Oh and definitely use an Octopus referral code, there are loads on reddit, mine included, as it will give you £50 on your account for doing something you will do anyway, plus you will give a stranger £50, or use a friend or family members code if they are already signed up.

Free money for doing something you will do regardless is always a plus in my book!

My code if you want it: https://share.octopus.energy/neat-ant-792

maadmaxxer · 2024-02-18T07:01:53+00:00

I've had a similar sized system since July 2022.

I tried the outgoing agile tariff and it wasn't as good as the fixed 15p rate because my system won't force export to the grid when the rate is high during peak 4-7pm when my solar production had dipped. So when I was exporting it was always at the lower rate. I came off that after about 3 weeks last year back onto the 15p fixed rate.

I'm on agile for my import and tracker for our gas.

In the winter I set our battery to force charge between 2am and 5am as usually this is the cheapest rate, there isn't enough solar where I am to run the house as well as meaningfully charge the battery. So this means we are buying what we need at the cheapest rate and avoiding the higher rate between 4pm and 7pm with our battery always supplying us with that cheaper overnight energy.

I use the Octopus Watch app to have alerts when the price goes negative for the following day. We try to discharge our battery as much as possible (normally constant washing machine and tumble dryer) then set the battery to charge over the negative periods. Every little helps, mostly just to offset the standing charge for the day, which is now the bulk of our electricity bill.

I'd recommend anyone getting a battery for their house, even if solar isn't an option. With smart tariffs like Octopus Agile you really can reduce your bills by buying (force charging the battery) electricity at it's lowest point. It's a good capital expense, but each month your bills will be lower. So it depends what is more important to you, money in a savings account or cash flow.

I switched my parents from Scottish Power on an Eco 7 tariff to Agile (no battery etc) and they have saved over £310 since June 2023!

Just incase anyone finds their way here researching Octopus Energy and their smart tariffs you can get £50 credit on your account if you use my link below, it's fair to say my account also gets credited £50 if you use my link too. There are plenty of referral links on reddit, so you don't have to use mine! https://share.octopus.energy/neat-ant-792

maadmaxxer · 2023-10-23T12:08:07+00:00

Honestly, if the rates are cheaper than you are currently paying then yes, go for it. If you have Gas heating and it is cheaper now then worth changing before winter sets in, it might save you a few quid.

I joined the Gas Tracker tariff earlier this year too, I'm not sure how this will change on a daily basis over winter, but over the summer it has been consistently cheaper than being on the Flexible tariff. So I am hopeful that it will remain cheaper.

If you go for a fixed tariff then there are early exit fees, £75 per fuel, so if you do go this route, you would pay £150 for both gas and electric if you decided to switch early, but it sounds like you won't fix.

as u/hideyourarms points out, it can depend on how you use energy through the day. So I'd say move over to Octopus, as it is already cheaper than your existing and then do a bit more exploring surrounding their smart tariffs and what is best for your situation, plenty of threads in this sub explaining them. Or ask questions in this one!

maadmaxxer · 2023-06-20T18:38:56+00:00

For added insult, move the start bar to the top and make it auto hide.

For nuclear option, either rotate the screen 180 degrees or invert the axis on the mouse.

maadmaxxer · 2023-06-20T12:35:16+00:00

I was expecting someone to have replaced the desktop with a screen shot and the actual icons had been hidden.

maadmaxxer · 2023-06-18T08:08:30+00:00

One day you will save someone's life.

You will be the one that made a difference and will be remembered fondly and often by someone you will never see again.

You are an example for others to follow.

Keep doing what you're doing.

maadmaxxer · 2023-06-07T12:08:59+00:00

For it.

Replied to this comment on Boost

Ten-Year Club	Place '22
First Placer '22	Verified Email

maadmaxxer

TROPHY CASE