Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 279
Default Writing formulae from array to range

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
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 279
Default Writing formulae from array to range

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
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default Writing formulae from array to range

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Writing array to a range [email protected] Excel Programming 11 February 19th 16 06:35 AM
variant array with formula strings to range formulae Amedee Van Gasse Excel Programming 4 May 20th 08 09:27 AM
Writing Array To A Named Range IanC Excel Programming 5 April 15th 08 07:34 AM
Writing a range to an array... Alex Excel Programming 2 December 29th 05 02:59 PM
Writing Range to Array Marston Excel Programming 3 August 9th 04 09:11 PM


All times are GMT +1. The time now is 02:03 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"