VB6 or .NET

[Duval]

Is it possible to interface through either of these? I would rather do it through these than through Excel as Excel is a bit processor hungry. I know it's been asked before but no reply was given.
Thanks

[Duval]

Somebody please!!

[PeteB]

Kind of...

Look at the COM server info in the help file.

But it's not really ready for use in anger yet - for example handling of multiple markets

Search the forum for "COM server" and you will see what other people have / haven't managed to do with it so far.

Excel shouldn't be too processor intensive - it depends how you write it - once you know what code will make COM calls behind the scenes you can avoid these, and you should see big improvements. Basically you only want to interact with the worksheet twice on each refresh - once to read, and once to write if you want to place bets. Cut out everything like Range.Select, and simply set Range.Value2 = your data array, to update big chunks of data in one hit.

A final alternative is to use VSTO (Visual Studio Tools for Office) but it's not cheap. I use this as I already have it for something else. Then you have .NET "managed code" running in a DLL, but you still need to cut down on the COM calls between Excel and the managed code. I do a single read of the Excel data into managed code on each refresh (myarray = Range.Value2), and then all further calculations are within variables in managed code. Finally update Excel if required (Range.Value2 = myarray). Speed-wise I don't think it's too different from well-written VBA, but .NET is a pleasure to work in whereas VBA is hateful.

Biggest problem at the mo is that BA doesn't send an array of unmatched bets to Excel on each refresh - makes it hard to use strategies where you have more than one or two unmatched bets per selection...

[Duval]

Thanks for taking the time PeteB

Sorry, I didn't mention that I am vary proficient in VBA VBn .NET etc. It's just that I want to set my BA strategies off in the morning and leave them running all day while I'm at work. I have 8 tabs running for each race because of the inefficiencies of BA (sorry, not slagging it off, I would have ditched it a long time ago if it wasn't a very good bit of software) times that by maybe 18 races and thats a lot for excel to handle. I'm constantly writing code to cut down the calculations so I have it down to a fine art. I would have thought that .net would have been less hungry and I could do a lot more with it.

I will read other posts and see if I can find something.

Once again thank you for your time

[Mitch]

Sorry for being ignorant but why would you need the same information 8 times?

[Dai_Young]

Blimey, you have 8 tabs open for every horse race. 100+ tabs open at once?

I think my computer would explode.

Are you aware of the imminent implementation of request charges Duval?

[Mitch]

I thought he meant 8 tabs for a race which auto change on the quick pick list so there are only 8 open at a time.

[mike]

Hi Pete,

That sounds interesting since I use a lot of arrays in my code. I fill my sheet now by using a loop which runs through the array and fills the sheet cell by cell.

You mentioned the command: Range.Value2 = MyArray. It is not clear to me how to make this work. Can you give a small example with lines of VBA-code how to put an array in one step into a sheet?

thank you!

regards,
Mike

[PeteB]

Hi Mike,

On hols at the mo without access to code, so will have to make something up...

The Value2 property of a Range will return an array you can then manipulate in code. It's possible to declare an array in code, and then write it to a Range, but I find it easier to set up the array by using the return from the Value2 property, as you don't have to worry about matching up dimensions and indexing. I like to use named ranges, so if I want to write to Excel, I first set up the array variable by doing MyArray = NamedRange.Value2, and then set the data the way you want it, before doing NamedRange.Value2 = MyArray


So a dead simple example - get some data into code, change some values, and put it somewhere else in the sheet:

In a new sheet, name the range A1-C3 "InputRange", and name the range A6-C8 "OutputRange"

Put the following values in A1-C3:
1 2 3
4 5 6
7 8 9

Now put the following code in the sheet:

Code: Select all

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim MyInputRange As Range
    Dim MyOutputRange As Range
    Set MyInputRange = ThisWorkbook.Names("InputRange").RefersToRange
    Set MyOutputRange = ThisWorkbook.Names("OutputRange").RefersToRange
   
    Dim MyArray
    ' Get the data from the Input Range
    MyArray = MyInputRange.Value2
   
    ' Process the data in some way
    MyArray(1, 3) = MyArray(1, 1) + MyArray(1, 2)
    MyArray(2, 3) = MyArray(2, 1) + MyArray(2, 2)
    MyArray(3, 3) = MyArray(3, 1) + MyArray(3, 2)
   
    ' Write the data to the output range
    MyOutputRange.Value2 = MyArray

End Sub


Now change data and see what happens!

(In fact in this case the lines
Set MyInputRange = ThisWorkbook.Names("InputRange").RefersToRange
Set MyOutputRange = ThisWorkbook.Names("OutputRange").RefersToRange
ideally wouldn't be done in the Worksheet_Change function, as each of these lines is an interaction with Excel and only needs to be done once, and could be done in Worksheet_Startup instead - but you get the idea...)

If you need to switch rows to columns, then use the Application.WorksheetFunction.Transpose function - google will show you how

(Of course you would also test the dimensions of MyArray before attempting to access specific members, but I have been lazy for now)

[mike]

Hi Pete,

Many thanks for your extensive explanation! I will try to integrate your example in my code. This will take some time. I will let you know if it succeed.

Many thanks!
regards,
Mike