Speeding up com control of Excel

Get help with using AutoHotkey and its commands and hotkeys
User avatar
Relayer
Posts: 134
Joined: 30 Sep 2013, 13:09
Location: Delaware, USA

Speeding up com control of Excel

09 Feb 2015, 11:31

Hi,

I really like controlling Excel with AHK. I find it much more convenient than dealing with Excel macros... that might just be me. Anyway, is there anything I should be doing to allow the interface to work as quickly as possible? Most functions are simple enough that the lag time is insignificant but some take enough time to make it annoying, especially if I intend on sharing my work with anyone else. So far I'm specifying SetBatchLines -1

Relayer
User avatar
Blackholyman
Posts: 1292
Joined: 29 Sep 2013, 22:57
Facebook: socialjsz
Google: +Jszapp
Location: Denmark
Contact:

Re: Speeding up com control of Excel

09 Feb 2015, 12:33

Can you post an example of a snippet thats giving you lag?

That maybe the only Real way we can help you with something like that
Also check out:
Courses on AutoHotkey

My Autohotkey Blog
:dance:
User avatar
jigga
Posts: 93
Joined: 24 Jan 2014, 00:31
Facebook: joel.fazio
Google: +JoelFazio

Re: Speeding up com control of Excel

09 Feb 2015, 12:50

It seems to run much faster if the document is not visible. Not sure if you need the spreadsheet open or not, but I do lots of excel stuff via ahk without the actual spreadsheet open on my desktop.
User avatar
jethrow
Posts: 188
Joined: 30 Sep 2013, 19:52
Location: Iowa

Re: Speeding up com control of Excel

09 Feb 2015, 14:14

jigga wrote:It seems to run much faster if the document is not visible.
This probably has more to do with screen updating that the sheet actually being visible. Here is an example:

Code: Select all

xl.ScreenUpdating := False
Loop 10000 {
	xl.Range("A1").value := A_Index
}
xl.ScreenUpdating := True
Try that with & without the ScreenUpdating on. Note that if most of your updating is off-screen, the impact won't be as dramatic.

Also, if possible, you could build/modify your data with AHK, which would be in-memory (ahk process) & perform much faster:

Code: Select all

arr := ComObjArray(12,10000,1)
Loop 10000 {
	arr[A_Index-1,0] := A_Index
}
xl.Range("A1:A10000").value := arr
See safe arrays and range object

Note - you could also get your Range (ComObjArray) from the worksheet - though it would be 1-based rather than 0-based:

Code: Select all

arr := xl.Range("A1:A10000").value
Loop 10000 {
	arr[A_Index,1] := A_Index
}
xl.Range("A1:A10000").value := arr
User avatar
Relayer
Posts: 134
Joined: 30 Sep 2013, 13:09
Location: Delaware, USA

Re: Speeding up com control of Excel

09 Feb 2015, 15:55

Thanks for all the replies. Unfortunately, what I am doing requires the Excel UI to be active. Also, I'm not messing with arrays of data per se. Here's what I'm doing in short: making huge data sets visible in charts by allowing data scrolling and panning. It works by using a named range and then adjusting the axes to scale properly. Excel charts are really poor at dealing with large amounts of data that need to be scrutinized in certain areas.

Here's the code that does the heavy lifting... it's not the whole script.

Code: Select all

PanLeft:
		sign := 1
		Gosub Pan
	Return
	
	PanRight:
		sign := -1
		Gosub Pan
	Return
	
	Pan:
		start := xl.ActiveSheet.Range(cell4StartingPoint).Value
		width := xl.ActiveSheet.Range(cell4NumberOfPoints).Value
		
		factor := sign * floor(width * 0.1)
		if (( a := start + factor ) > minData.2) and (a + width < maxData.2)
			newStart := a
		else if (a <= minData.2)
			newStart := minData.2
		else if (a + width >= maxData.2)
			newStart := floor(maxData.2 - width)
		
		xl.ActiveSheet.Range(cell4StartingPoint).Formula := newStart
		
		xa1 := maxData.1 . floor(newStart)
		xa2 := maxData.1 . floor(newStart + width - 1)
		xl.ActiveChart.Axes(1, xlPrimary).MinimumScale := xl.ActiveSheet.Range(xa1).Value
		xl.ActiveChart.Axes(1, xlPrimary).MaximumScale := xl.ActiveSheet.Range(xa2).Value
	Return
	

	ZoomIn:
		factor := 0.9
		Gosub Zoom
	Return
	
	ZoomOut:
		factor := 1.1
		Gosub Zoom
	Return
	
	Zoom:
		start := xl.ActiveSheet.Range(cell4StartingPoint).Value
		width := xl.ActiveSheet.Range(cell4NumberOfPoints).Value

		tmp := floor(width * factor)

		if ((a := floor(start + (width - tmp)/2)) > minData.2) and (a < maxData.2)
			newStart := a
		else if (a <= minData.2)
			newStart := minData.2
		else if (a >= maxData.2)
			newStart := maxData.2
		
		xl.ActiveSheet.Range(cell4StartingPoint).Formula := newStart
		points := floor((tmp > maxPoints) ? (maxPoints) : (tmp))
		xl.ActiveSheet.Range(cell4NumberOfPoints).Formula := points
		
		xa1 := maxData.1 . floor(newStart)
		xa2 := maxData.1 . floor(newStart + points - 1)
		xl.ActiveChart.Axes(1, xlPrimary).MinimumScale := xl.ActiveSheet.Range(xa1).Value
		xl.ActiveChart.Axes(1, xlPrimary).MaximumScale := xl.ActiveSheet.Range(xa2).Value
	Return
	
	
	PanUp:
		max := xl.ActiveChart.Axes(xlCategory, xlPrimary).MaximumScale
		min := xl.ActiveChart.Axes(xlCategory, xlPrimary).MinimumScale
		inc := (max - min) * 0.1
		xl.ActiveChart.Axes(xlCategory, xlPrimary).MaximumScale := round(max - inc, 2)
		xl.ActiveChart.Axes(xlCategory, xlPrimary).MinimumScale := round(min - inc, 2)
		
		max := xl.ActiveChart.Axes(xlCategory, xlSecondary).MaximumScale
		min := xl.ActiveChart.Axes(xlCategory, xlSecondary).MinimumScale
		inc := (max - min) * 0.1
		xl.ActiveChart.Axes(xlCategory, xlSecondary).MaximumScale := round(max - inc, 2)
		xl.ActiveChart.Axes(xlCategory, xlSecondary).MinimumScale := round(min - inc, 2)
	Return
	
	PanDown:
		max := xl.ActiveChart.Axes(xlCategory, xlPrimary).MaximumScale
		min := xl.ActiveChart.Axes(xlCategory, xlPrimary).MinimumScale
		inc := (max - min) * 0.1
		xl.ActiveChart.Axes(xlCategory, xlPrimary).MaximumScale := round(max + inc, 2)
		xl.ActiveChart.Axes(xlCategory, xlPrimary).MinimumScale := round(min + inc, 2)
		
		max := xl.ActiveChart.Axes(xlCategory, xlSecondary).MaximumScale
		min := xl.ActiveChart.Axes(xlCategory, xlSecondary).MinimumScale
		inc := (max - min) * 0.1
		xl.ActiveChart.Axes(xlCategory, xlSecondary).MaximumScale := round(max + inc, 2)
		xl.ActiveChart.Axes(xlCategory, xlSecondary).MinimumScale := round(min + inc, 2)
	Return
	
	Compress:
		max := xl.ActiveChart.Axes(xlCategory, xlPrimary).MaximumScale
		min := xl.ActiveChart.Axes(xlCategory, xlPrimary).MinimumScale
		inc := (max - min) * 0.1
		xl.ActiveChart.Axes(xlCategory, xlPrimary).MaximumScale := round(max + inc, 3)
		xl.ActiveChart.Axes(xlCategory, xlPrimary).MinimumScale := round(min - inc, 3)
		
		max := xl.ActiveChart.Axes(xlCategory, xlSecondary).MaximumScale
		min := xl.ActiveChart.Axes(xlCategory, xlSecondary).MinimumScale
		inc := (max - min) * 0.1
		xl.ActiveChart.Axes(xlCategory, xlSecondary).MaximumScale := round(max + inc, 3)
		xl.ActiveChart.Axes(xlCategory, xlSecondary).MinimumScale := round(min - inc, 3)
	Return
	
	Expand:
		max := xl.ActiveChart.Axes(xlCategory, xlPrimary).MaximumScale
		min := xl.ActiveChart.Axes(xlCategory, xlPrimary).MinimumScale
		inc := (max - min) * 0.1
		xl.ActiveChart.Axes(xlCategory, xlPrimary).MaximumScale := round(max - inc, 3)
		xl.ActiveChart.Axes(xlCategory, xlPrimary).MinimumScale := round(min + inc, 3)
		
		max := xl.ActiveChart.Axes(xlCategory, xlSecondary).MaximumScale
		min := xl.ActiveChart.Axes(xlCategory, xlSecondary).MinimumScale
		inc := (max - min) * 0.1
		xl.ActiveChart.Axes(xlCategory, xlSecondary).MaximumScale := round(max - inc, 3)
		xl.ActiveChart.Axes(xlCategory, xlSecondary).MinimumScale := round(min + inc, 3)
	Return
User avatar
jethrow
Posts: 188
Joined: 30 Sep 2013, 19:52
Location: Iowa

Re: Speeding up com control of Excel

09 Feb 2015, 16:59

There isn't much in your code that should be slowing down the script - let us know if there are areas that are performing poorly.

You could increase performance (though you probably won't notice) by saving object references rather than re-accessing them from the Application object each time:

Code: Select all

    Expand:
        oAxes_Primary := xl.ActiveChart.Axes(xlCategory, xlPrimary)
        max := oAxes_Primary.MaximumScale
        min := oAxes_Primary.MinimumScale
        inc := (max - min) * 0.1
        oAxes_Primary.MaximumScale := round(max - inc, 3)
        oAxes_Primary.MinimumScale := round(min + inc, 3)
        
        oAxes_Secondary := xl.ActiveChart.Axes(xlCategory, xlSecondary)
        max := oAxes_Secondary.MaximumScale
        min := oAxes_Secondary.MinimumScale
        inc := (max - min) * 0.1
        oAxes_Secondary.MaximumScale := round(max - inc, 3)
        oAxes_Secondary.MinimumScale := round(min + inc, 3)
    Return
User avatar
Relayer
Posts: 134
Joined: 30 Sep 2013, 13:09
Location: Delaware, USA

Re: Speeding up com control of Excel

09 Feb 2015, 17:51

jethrow,

Yes, using object pointers did speed it up a little. Thanks. That is probably a best practice in general.

What I found is really doing the trick for me is turning off screen updating then turn it back on after I'm done fooling with the chart. This really makes it respond in real time.

Relayer

Return to “Ask For Help”

Who is online

Users browsing this forum: Bing [Bot], RubbeH and 43 guests