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 measu
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

In message of Thu, 23 Jun 2016
21:03:53 in microsoft.public.excel.programming, Walter Briscoe
writes
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.


[snip]

p.s. I now measu
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.

I've identified some writing to a string variable, which is not read for
anything essential.
My sub writes to cells; it derives from a sub which writes to a file.
The string operations are needed for that sub. I now have:
10 buscrawl, Time = 4680, Mean = 4766, sd = 771
That IS a result. Thanks to my readers here for helping me to thought.
--
Walter Briscoe

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?

When coding for Cell.Formula the entire formula needs to be a string in
VBA. This means the single quotes within the formula require being
wrapped in quotes so they get interpreted correctly. So...

"Canada Water Bus Station stop ?"

...needs to be coded...

"""Canada Water Bus Station stop ?"""

The entire formula needs to be coded as follows...

Cell.Formula = "=HYPERLINK("""https...lineId=1""","""Canada...?"" ")"

I suspect using a variant type causes VBA to sort this out itself,
possibly?

As for emailing.., best to upload a file to a public drop box and
include your email in the file.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus