I did not think that this topic would run around so long!
I have well understood what I previously did not, about the exchanges
between Excel and VBA and I got the message that long  formulas are
not that long!

Those long formulas  that I wanted to replace  by functions  are very
few, isolated (I mean not part of a long array of hundreds of cells),
and would be calculated only once a day, maximum, sometimes less.
I'll leave them as they are now.

I do not have long arrays filled with data that I have to go though
with a sub.

But I do have a quite long array that does not contain data, but
formulas (not very long formulas but hundred of them!)

Even if the lines contain only 0 values when the user starts, (they
will be filled day after day with the values derived from those
received by 2 of the 3 Web Queries when people use the spreadsheet,
the third one is used less often and does not serve to fill an array,
just update individual cells).
The cells in that long array are recalculated pretty often, and that,
even if have set my Excel options to calculate immediately except for
the tables.
In that array, the only cells that are not calculated by a formula
are in the first column (dates) that serve for the vlookup.

This also takes quite a long time, especially when one closes the
Workbook and worst, when Excel makes its preventive copies), when
they are all recalculated again, during which everything comes to a
halt until the book has been saved.
Because once the user has updated the web query, some days, he/she
still has lots if thinking to do in front of the workbook...

Now I wonder if it would not be better, in the sub that controls
the Web queries, to also add the formulas day by day, as the array
becomes populated.

In that case, the sub would use only one Worksheet function,
vlookup for the date, then the seven columns to the right of the
first one would be filled with their respective formulas, then the
result of the calculation (which then would concern only 7 cells),
would be copied and pasted.
In such a way, I think that the time ratio between using one
Worksheet function, as compared to recalculating all the cells (7 for
about 400 rows), would be favourable.

Don't you think?