Separate worksheet in separate workbook as macro

[dranon]

This is probably going to sound lame, because I'm tired and my brain
isn't functioning.

I have a "routine" that requires a lot of input and a lot of
intermediate calculations. Sounds like a spreadsheet, huh? <smile>

What I'd like to do is arrange a UDF which would take the vaules from
the "user spreadsheet", stuff them into the "background spreadsheet",
wait for the "background spreadsheet" to finish recalculating and then
lift the result found at a particular cell in the "background
spreadsheet" and return that value as the returnvalue from the UDF.

Something like:

=mybigfunction(celladdress1, celladdress2, celladdress3, celladdress4,
celladdress5, celladdress6, celladdress7, celladdress8)

Is this doable? Is it trivial and I just can't seem to focus on how
it might work? I have no trouble with hard coding the name of the
"background spreadsheet" in the excel UDF (I know it will break if the
"background spreadsheet" isn't loaded).

Apologize in advance for my being this tired, but, we all soldier on
the best way we can.

Thanks

 

[Dave Peterson]

UDF's can't stuff values into other cells. They return values to the cells that
contain the UDF call.

But you can use a macro (run it on demand???) to accomplish something very
similar.

Saved from a previous post:

Create one sheet that contains the input cells and (soon to be) output cells.

Then create another sheet that contains all the calculations that you need.

Then you type in all the input cells and the macro takes each line of data,
populates the calc sheet, calculates, and then extracts the resulting cells that
you want and places them onto the same row as the input values on that input
sheet.

If that sounds reasonable, here's a macro that may get you started...

You'll have to change this to use the right cells on the calculation sheet:

Option Explicit
Sub testme()

Dim InputWks As Worksheet
Dim CalcWks As Worksheet
Dim myRng As Range
Dim myCell As Range

Set InputWks = Worksheets("sheet1")
Set CalcWks = Worksheets("sheet2")

With InputWks
'headers in row 1
Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

With CalcWks
For Each myCell In myRng.Cells
'populate the CalcWks with values from the input sheet
.Range("a1").Value = myCell.Value
.Range("x99").value = mycell.offset(0,1).value
.range("iv323").value = mycell.offset(0,2).value

'do the calculation
Application.Calculate

'take some values back from the calcwks to the input sheet
myCell.Offset(0, 3).Value = .Range("b1").Value
myCell.Offset(0, 4).Value = .Range("c1").Value
myCell.Offset(0, 5).Value = .Range("d1").Value
Next myCell
End With

End Sub

You'll have to change all the addresses that get populated and add more lines as
you need them. And same thing with the "after calc" portion. You'll want to
put them in the cells you want.

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[dranon]

UDF's can't stuff values into other cells. They return values to the cells that
contain the UDF call.

Can it do so with respect to a separate workbook? That is, can a UDF
defined in workbook # 1 insert values into a worksheet in workbook #
2, calculate results in workbook # 2 and then interrogate the results
cell in workbook # 2 and then return that value?

But you can use a macro (run it on demand???) to accomplish something very
similar.

Well, there really is no need for the macro at all if both the input
worksheet and the calculation worksheet are in the same workbook, is
there?

Saved from a previous post:

Create one sheet that contains the input cells and (soon to be) output cells.

Does the fact that this sheet is in a separate workbook make a
difference?

Then create another sheet that contains all the calculations that you need.

If I put this sheet in a different workbook, does it make a
difference?

Then you type in all the input cells and the macro takes each line of data,
populates the calc sheet, calculates, and then extracts the resulting cells that
you want and places them onto the same row as the input values on that input
sheet.

If that sounds reasonable, here's a macro that may get you started...

Thanks. This will definitely work and I think I can make it work if
the two sheets are in the same workbook. The two questions are: 1)
Can a UDF work if the worksheets are in separate workbooks?; and, if
the answer to (1) is "no", then: 2) Can a macro work if the worksheets
are in separate workbooks?

You'll have to change this to use the right cells on the calculation sheet:

Thanks. I can easily do that.

Option Explicit
Sub testme()

Dim InputWks As Worksheet
Dim CalcWks As Worksheet
Dim myRng As Range
Dim myCell As Range

Set InputWks = Worksheets("sheet1")
Set CalcWks = Worksheets("sheet2")

Can I set the CalcWks object to something like:

Set CalcWks = Workbooks("Calculations").Worksheets("sheet2")

With InputWks
'headers in row 1
Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

With CalcWks
For Each myCell In myRng.Cells
'populate the CalcWks with values from the input sheet
.Range("a1").Value = myCell.Value
.Range("x99").value = mycell.offset(0,1).value
.range("iv323").value = mycell.offset(0,2).value

'do the calculation
Application.Calculate

Can this be set to calculate not the application, but the specific
workbook?

Thanks

 

[Dave Peterson]

#1. Nope. UDF's called from a formula in a worksheet can't do this kind of
thing.

#2. Why not? I would think keeping the data in a nice tabular form on a
separate worksheet and the calculations on a different sheet would be useful.
And if you had 1000 rows of separate data, you could populate the calculation
worksheet, read the results and update those results in cells on the the same
row as the input data.

#3. You can simply change these two lines:

to point at any workbooks you want:

(for instance).

#4. Calculation is an application setting. You can use a range calculation,
but there are problems with that. But excel is very smart. It'll only
reevaluate those cells that it needs to. If you have lots of workbooks open
(and excel is set to manual calculation), you could see a delay.

If that's the case, then don't open lots of workbooks when you're playing with
these scenarios.

 

[dranon]

Thanks. There are good reasons why I need to have the guts in a
separate workbook.

Many thanks for the code, I'll give it a whirl.

 

[Dave Peterson]

There's no reason you couldn't have one workbook for the code, one workbook for
the calculation sheet and multiple workbooks for the data.