Can a workbook be used as a function

  • Thread starter Thread starter DCJ
  • Start date Start date
D

DCJ

Hi,
I have an excel workbook (A) that givern three parameters, temp1, temp
and flow calculates the energy transfer for a process we conduct,
have another workbook (B) which contains the hourly logged data for th
process from which we generate monthly reports.
Is it possible to use workbook (A) like a function and pass to it th
three parameters from workbook (B) and return to workbook (B) th
answer ie. energy transfered.
Workbook (B) contain approx 750 logs so i need to repeat th
calculation 750 times

I could link the two which works for the first piece of logged data bu
how to automatically change the links for the next 749 ??
alternatively make workbook (A) a function and call it 750 time - ho
??

Any suggestions !!

D
 
DCJ said:
I could link the two which works for the first piece of logged data but
how to automatically change the links for the next 749 ??
alternatively make workbook (A) a function and call it 750 time - how
Click to expand...

Far easier to "make workbook (A) a function call". This is something
that has been suggested for years, now, but has not been implemented in
any spreadsheet that I know of. Using a bit of VBA, however:


Assume your input data was in A1:C750 and you wanted your values
returned in D1:D750. Further assume that the inputs on your calculation
workbook (say, "Calc.xls") are on Sheet1, Cells A1:C1 and the output of
the "function" is in [Calc.xls]Sheet1!J100:

Public Sub GenerateValues()
Dim rInputs As Range
Dim rOutput As Range
Dim rCell As Range

With Application
.ScreenUpdating = False
.Calculation = xlManual
End With

With Workbooks("Calc.xls").Sheets("Sheet1")
Set rInputs = .Range("A1:C1")
Set rOutput = .Range("J100")
End With

For Each rCell In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
rInputs.Value = rCell.Resize(1, 3).Value
rOutput.Calculate
rCell.Offset(0, 3).Value = rOutput
Next rCell

With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub
 
Back
Top