Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #109021

Writing formulae from array to range

Show all headers | View raw

I started to compose this as a problem. I now have a solution, I would
value an explanation. I have a second problem at the end.

I have a sub which is worth speeding.
I wrote a type and a sub to calculate time means and standard
deviations.
I measured 10 activations and got the following in milliseconds:
mean = 6459, standard deviation = 1195.

I had the following declarations:
    Static cell(1 To 256)   As String
    Static ci               As Long
    Dim destination         As Range

Data are static as the sub is recursive.
I had the following code cribbed from work by Chuck Pearson:
        Set destination = Range("B" & ActiveCell.Row)
        Set destination = destination.Resize(1, ci)
        destination.Value = cell

I found that data like
=HYPERLINK("https://tfl.gov.uk/bus/stop/490004733D/canada-water-bus-
station?lineId=1", "Canada Water Bus Station stop ?")
were written as strings, rather than formulae.

I NOW have code which seems to work
    Static cell(1 To 256)   As variant

Why the difference?

p.s. I now measure:
10 buscrawl, Time = 6381, Mean = 6639, sd = 1152
I have not bothered doing any difference of means calculation.
The optimisation has plainly not achieved anything useful.
As my sub is called about 1400 times, time savings would be worth
having. I run the relevant code about once a week.

Any suggestions on optimisation?
My sub consists of about 180 lines and I think it unreasonable to post
here. I would email to any interested, well-known maven.

I will post my statistics sub on any interest.
-- 
Walter Briscoe

Back to microsoft.public.excel.programming | Previous | Next — Next in thread | Find similar | Unroll thread

Thread

Writing formulae from array to range Walter Briscoe <wbriscoe@nospam.demon.co.uk> - 2016-06-23 21:03 +0100
  Re: Writing formulae from array to range Walter Briscoe <wbriscoe@nospam.demon.co.uk> - 2016-06-23 21:31 +0100
  Re: Writing formulae from array to range GS <gs@v.invalid> - 2016-06-23 22:31 -0400

csiph-web