VBA to call WorksheetFunction from a different Workbook

[SliderJeff]

Hey guys,

I'm running into an issue where I can't seem to get my VBA to perform the WorksheetFunction.Max on the correct worksheet's column.

Here is the prototype for my subroutine so you can see what variables I have access to:

Sub FindExtremeValues(ByVal fileNum As Integer, ByRef fileName As String, ByRef outputWorkbook As Workbook, ByRef inputWorkbook As Workbook)

The key params to pay attention to are the outputWorkbook, which is where I want to store the result of the Max function, and inputWorkbook, where I want to grab Column B and perform the Max function. "fileName" is the same as the name of the sheet in inputWorkbook upon which I wish to perform the Max function.

Here is what I tried, but it appears that the "Columns"B:B"" portion of that line is grabbing the Column B from outputWorkbook and not inputWorkBook.

    Cells(3 + fileNum * NUM_PARAMS_IN_CSV, 2).Value = Workbooks(inputWorkbook.Name).Worksheets(fileName).Application.WorksheetFunction.Max(Columns("B:B"))

The left hand side of the equal sign is just fine. It is correctly storing the Max value into the appropriate cell in outputWorkbook.

So my questions are:

1.) How to I get the right hand side of the equation to perform the Max function on Column B of inputWorkbook?

2.) Is there a better/cleaner way to do all this "dereferencing" of workbooks, worksheets and functions than what I have written, as this seems exorbitantly long, likely because I am just cutting my teeth on VBA over the last week or two.

Please let me know if you need to see any other code in my sub or

Thanks for any help you can provide!

Regards,
Jeff

 

[wideboydixon]

Cells(3 + fileNum * NUM_PARAMS_IN_CSV, 2).Value = Workbooks(inputWorkbook.Name).Worksheets(fileName).Application.WorksheetFunction.Max(Columns("B:B"))

Both items in red above are unqualified so VBA will take them from the active sheet. You need something like this:

Cells(3 + fileNum * NUM_PARAMS_IN_CSV, 2).Value = Application.WorksheetFunction.Max(inputWorkbook.Worksheets(fileName).Columns("B:B"))

If would be even better to quality the Cells() on the left:

outputWorkbook.Worksheets("SomeSheet").Cells(3 + fileNum * NUM_PARAMS_IN_CSV, 2).Value = Application.WorksheetFunction.Max(inputWorkbook.Worksheets(fileName).Columns("B:B"))

WBD

 

[SliderJeff]

PERFECT, WBD! Thanks a bunch! I'm still working my way through figuring out how best to call functions vs. referencing workbooks and worksheets and in what order to do it. Clearly I was doing it bass-ackwards based on your excellent correction of my code. Thanks again!

Regards,
Jeff