Subtracting Arrays

[lopiner]

Hi to all.

I'm making some calculations based on a multi array and i wanted to subtract two arrays but i cannot seem to accomplish that without an expensive loop. Here is my code:

Dim TrainingResults as DAO.Recordset
Dim NormalizedPricesArray As Variant
Dim ArrArrayX As Variant
Dim ArrArrayY As Variant
Dim RecordsCount as Integer

Set TrainingResults = CurrentDb.OpenRecordset("TrainingResults", dbOpenDynaset)
TrainingResults .MoveLast
RecordsCount = TrainingResults .RecordCount
TrainingResults .MoveFirst
NormalizedPricesArray = TrainingResults.GetRows(RecordsCount)
TrainingResults.Close
Set TrainingResults = Nothing

ArrArrayX = Excel.WorksheetFunction.Index(NormalizedPricesArray, 1)
ArrArrayY = Excel.WorksheetFunction.Index(NormalizedPricesArray, 2)

I wanted to create a New Array with the subtraction between ArrArrayX and ArrArrayY. Is this possible without looping the two arrays (maybe using Excel library). Thanks for any help.

 

[vbaInet]

An array isn't like a table where you can use a query to perform calculations on many records. You would need to loop through every item in the array and perform the subtraction on each line then save into the new array.

 

[lopiner]

vbaInet

Thanks for the reply.
I understand that, i was just hoping that i could use a library like the one from Excel for the subtraction that could improve the speed. I have to subtract approximately 5 million arrays and any improvement in speed would be great.

 

[vbaInet]

Perform the calculation in a query and update the table.