# Mathematical Excel tool Automation

• ### Question

• I have developed a calculator in "TOOL.xlsx" file. It takes 4 inputs and returns 2 outputs. The calculation is performed on the calculator sheet in the "Tool.xlsx" workbook. 4 inputs correspond to 1 data set. I have another Excel file named "DATA.xlsx" that contains around 20,000 datasets (4 inputs per data set) and it also has an output column that collects the output. I would like to automate the "DATA.slsx" and "Tool.xlsx" interaction so that the inputs for the "TOOL.xlsx" are automatically called from the "data.xlsx" and the output column in the "data.xlsx" are filled with the outputs of "TOOL.xlsx". I would really appreciate any help because I am really stuck up here. PS: I an new to VBA. Please find a screenshot ( http://i.imgur.com/ETC83wc.jpg )at that illustrates my problem.
Saturday, March 14, 2015 6:53 PM

• I recommend that you read this tutorial:
http://www.wiseowl.co.uk/blog/s161/online-excel-vba-training.htm

Open the file TOOL.XLSX first, right-click the worksheet tab and select "View Code".

Insert the code below into the editor and save the file as TOOL.XLSM, otherwise the macro is removed (XLSX files can not save macros).

Open the file DATA.XLSX, press ALT-F8 and run the macro TOOL.XLSM!Test

However, the execution will take some time, calculations performed in this way are very slowly.

Andreas.

```Sub Test()
Dim R As Range
Dim TInput(1 To 4) As Range
Dim TOutput(1 To 2) As Range
Dim i As Integer
Dim Ws As Worksheet

'Set a reference to the cells in tool.xlsx
With Workbooks("Tool.xlsx").ActiveSheet
For i = 1 To 4
Set TInput(i) = .Range("C3").Offset(i - 1)
Next
For i = 1 To 2
Set TOutput(i) = .Range("F3").Offset(i - 1)
Next
End With

'Be sure the calculation is on
Application.Calculation = xlCalculationAutomatic

With Workbooks("Data.xlsx").ActiveSheet
'Visit each cell in column B
For Each R In .Range("B4", .Range("B" & Rows.Count).End(xlUp))
'Copy the values from this row into tool.xlsx
For i = 1 To 4
TInput(i).Value = R.Offset(, i - 1).Value
Next
'Be sure the worksheet is calculated
DoEvents
'Copy the results back into H:I
For i = 1 To 2
R.Offset(, 5 + i).Value = TOutput(i).Value
Next
Next
End With
End Sub
```

Sunday, March 15, 2015 10:02 AM