Max of selected cells - shortcut?

[MePenguin]

Hi, I'm getting extremely frustrated here trying to simply use VBA to get the Max of the currently selected cells or range.

I'm trying to insert the code into an already functioning sub in an Access db which draws multiple bar charts for a spreadsheet. I want the Max value so that I can scale the graphs in a different way to what the autoscale function does.

I know this works:

    'Select data range for graph on pctresults sheet
        ExcelApp.Worksheets("PctResults").Activate
        With ExcelApp.ActiveSheet
            .Range(.Cells(StartRow, StartColumn), .Cells(EndRow, EndColumn)).Select
        End With

'NEW CODE TO GO HERE

    'Create graph object
        Set ObjExChart = ObjExcel.Charts.Add

because I've been using it for months.

After some trouble and coffee I managed:

Dim rngdata As Range
Set rngdata = ExcelApp.ActiveSheet.Cells(StartRow, StartColumn).Resize(EndRow - StartRow, 1)
        MaxEco = ExcelApp.WorksheetFunction.Max(rngdata)

Which works, but seems silly - is there any way I can use the Max function with the currently selected cells?

What this comes down to is I really don't know how to refer to the active range...

Help please?

Phil

Phil

---------------
Pass me the ether.

 

[Loomah]

Hi
Try
max(selection)

happy friday

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[MePenguin]

Oh hell, missed that one (hysterical laughter)...

Thank's!

Think I'll go to the pub.

Phil

---------------
Pass me the ether.

 

[MePenguin]

Here's a funny thing - it worked, but now it doesn't! Nothing changed... just packaged the db (still running the same version), copied some files, edited a log, and went to tell a colleague how foolish I was...

    'Select data range for graph on pctresults sheet
        ExcelApp.Worksheets("PctResults").Activate
        With ExcelApp.ActiveSheet
            .Range(.Cells(StartRow, StartColumn), .Cells(EndRow, EndColumn)).Select
        End With

    If ChartLoop > NumCodes Then 'Is sum graph, so recalc x-axis scales
        MaxEco = 0

'THIS VERSION WORKS
          '  Dim rngdata As Range
         '   Set rngdata = ExcelApp.ActiveSheet.Cells(StartRow, StartColumn).Resize(EndRow - StartRow, 1)
        '    MaxEco = ExcelApp.WorksheetFunction.Max(rngdata)
            'above replaced by the, much simpler:
'END OF WORKING VERSION

'AND THIS NOW FAILS...
        MaxEco = ExcelApp.WorksheetFunction.Max(Selection)

I get an "invalid procedure call or argument" error on the selection line.

What's up with that?


Phil

---------------
Pass me the ether.

 

[xlbo]

2 possibilities I reckon

1: there is more to your code and you are selecting something else between the initial selection and the MAX calc

2: you may need to qualify "selection" to:

MaxEco = ExcelApp.WorksheetFunction.Max(ExcelApp.Selection)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[MePenguin]

Thank's Geoff, nr. 2 solved it.

PS. I like the crayons thing

Phil

---------------
Pass me the ether.