Share via

How do I use "Max" and "Large" Functions in VBA

Anonymous
2013-08-28T06:36:06+00:00

I am having a problem getting my ppt macro to recognize excel functions and I'm not sure why. I'm trying to get the address of the largest and second largest cells in my array and then return the name of the cell (the cell to the left of the addressed cell). My debugger gives me an "Object Required" error when I try this, which makes me feel like this must be a simple fix, but I can't seem to find the problem. Here's what I have:

' Dim objects for calling DATA (Excel File)

Dim xlsApp As Object

Dim xlsWb As Object

Dim slxWs As Object

Dim ppt As Presentation

' Dim objects for Takeaway (Slide 6)

Dim BigOneProvince As String

Dim BigTwoProvince As String

Dim ProvinceOne As String

Dim ProvinceTwo As String

Dim TakeAway As String

' Set objects for DATA (Excel File)

Set xlsApp = CreateObject("Excel.Application")

Set xlsWb = xlsApp.Workbooks.Open(ActivePresentation.Path & "" & "DATA.xlsx")

Set ppt = ActivePresentation

' Set Worksheet to sheet one

Set xlsWs = xlsWb.Worksheets(1)

BigOneProvince = xlsApp.WorksheetFunction.Max(xlsWs.Range("D33:D38")).Address

BigTwoProvince = xlsApp.WorksheetFunction.Large(xlsWs.Range("D33:D38"), 2).Address

ProvinceOne = xlsWs.Range(BigOneProvince).Offset(0, -1)

ProvinceTwo = xlsWs.Range(BigTwoProvince).Offset(0, -1)

ppt.Slides(6).Shapes("TextBox 1").TextFrame.TextRange.Text = xlsWs.Range("E6") & _

"’s brand has established interest from all across China, with significant interest from the " & _

ProvinceOne & " and " & ProvinceTwo & " provinces."

' Slide 6: Takeaway

If ProvinceOne = ("Beijing" Or "Shanghai") Then

If ProvinceTwo = ("Beijing" Or "Shanghai") Then

TakeAway = "Tier 1 cities are an option!"

Else

TakeAway = "Tier 1 cities may be an option."

End If

Else

If ProvinceTwo = ("Beijing" Or "Shanghai") Then

TakeAway = "Tier 1 cities may be an option."

Else

TakeAway = "Tier 1 cities are not your market."

End If

End If

ppt.Slides(6).Shapes("Rectangle 4").TextFrame.TextRange.Text = "Takeaway: " & TakeAway

Help? Thanks in advance.

Ryan

Microsoft 365 and Office | PowerPoint | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2013-08-28T07:10:52+00:00

    Max and Large do not work the way you are asking them to. They return the value, not the cell so you cannot directly derive the .Address property from the returned value. You would need to .Match the .Max for the row number and feed that into .Index.

    Both .Max and .Large will work as Application.Max and Application.Large so Application.WorksheetFrunction should be unnecessary. Same for .Index and .Match.

    The following works from within an Excel VBA workspace. I believe you should be able to translate that for your own purposes.

    Sub maxtest()

    Dim rng As Range, xlsApp As Application

    Set xlsApp= Application

    Set rng = xlsWb.Worksheets(1).Range("D33:D38")

    Debug.Print xlsApp.Index(rng.Offset(0, -1), xlsApp.Match(xlsApp.Max(rng), rng, 0))

    Debug.Print xlsApp.Index(rng.Offset(0, -1), xlsApp.Match(xlsApp.Large(rng, 2), rng, 0))

    Set xlsApp= Nothing

    End Sub

    1 person found this answer helpful.
    0 comments
Answer accepted by question author
  1. HansV 462.6K Reputation points MVP Volunteer Moderator
    2013-08-28T06:53:18+00:00

    xlsApp.WorksheetFunction.Max(xlsWs.Range("D33:D38")) returns the maximum value, not the cell containing the maximum value.

    You have to find the maximum value, then (for example) use Match to find where the maximum occurs in the range, and use that as offset in column C:

    ' Dim objects for calling DATA (Excel File)

    Dim xlsApp As Object

    Dim xlsWb As Object

    Dim xlsWs As Object

    Dim dblMax As Double

    Dim dblMax2 As Double

    Dim lngMatch As Long

    Dim lngMatch2 As Long

    Dim ppt As Presentation

    ' Dim objects for Takeaway (Slide 6)

    Dim ProvinceOne As String

    Dim ProvinceTwo As String

    Dim TakeAway As String

    ' Set objects for DATA (Excel File)

    Set xlsApp = CreateObject("Excel.Application")

    Set ppt = ActivePresentation

    Set xlsWb = xlsApp.Workbooks.Open(ppt.Path & "" & "DATA.xlsx")

    ' Set Worksheet to sheet one

    Set xlsWs = xlsWb.Worksheets(1)

    dblMax = xlsApp.WorksheetFunction.Max(xlsWs.Range("D33:D38"))

    lngMatch = xlsApp.WorksheetFunction.Match(dblMax, xlsWs.Range("D33:D38"), 0)

    ProvinceOne = xlsWs.Range("C32").Offset(lngMatch, 0).Value

    dblMax2 = xlsApp.WorksheetFunction.Large(xlsWs.Range("D33:D38"), 2)

    lngMatch2 = xlsApp.WorksheetFunction.Match(dblMax2, xlsWs.Range("D33:D38"), 0)

    ProvinceTwo = xlsWs.Range("C32").Offset(lngMatch2, 0).Value

    ...

    0 comments

6 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. HansV 462.6K Reputation points MVP Volunteer Moderator
    2013-08-28T07:50:19+00:00

    You're using late binding, so

    Dim rng As Range

    is not valid; use

    Dim rng As Object

    instead. And you already have an Excel.Application object: xlsApp. There is no need to create another such object. Use xlsApp.

    Dim rng As Object

    Set rng = xlsWb.Worksheets(1).Range("D33:D38")

    ProvinceOne = xlsAppApp.Index(rng.Offset(0, -1), xlsApp.Match(xlsApp.Max(rng), rng, 0))

    ProvinceTwo = xlsApp.Index(rng.Offset(0, -1), xlsApp.Match(xlsApp.Large(rng, 2), rng, 0))

    ppt.Slides(6).Shapes("TextBox 1").TextFrame.TextRange.Text = xlsWs.Range("E6") & _

        "’s brand has established interest from all across China, with significant interest from the " & _

        ProvinceOne & " and " & ProvinceTwo & " provinces."

    0 comments
  2. Anonymous
    2013-08-28T07:49:28+00:00

    That worked for the first part! Now I'm getting a type mismatch error at this part:

    ' Slide 6: Takeaway

    If ProvinceOne = ("Beijing" Or "Shanghai") Then

    If ProvinceTwo = ("Beijing" Or "Shanghai") Then

    TakeAway = "Tier 1 cities are an option!"

    Else

    TakeAway = "Tier 1 cities may be an option."

    End If

    Else

    If ProvinceTwo = ("Beijing" Or "Shanghai") Then

    TakeAway = "Tier 1 cities may be an option."

    Else

    TakeAway = "Tier 1 cities are not your market."

    End If

    End If

    ppt.Slides(6).Shapes("Rectangle 4").TextFrame.TextRange.Text = "Takeaway: " & TakeAway

    I recognize that I'm in over my head here. Thanks to you both for the help you've given me. You've been very helpful and I appreciate it.

    0 comments
  3. Anonymous
    2013-08-28T07:38:56+00:00

    This is how I adapted your code into my program:

    Dim rng As CellRange, NewApp As Application

    Set NewApp = Application

    Set rng = xlsWb.Worksheets(1).Range("D33:D38")

    ProvinceOne = NewApp.Index(rng**.Offset**(0, -1), NewApp.Match(NewApp.Max(rng), rng, 0))

    ProvinceTwo = NewApp.Index(rng.Offset(0, -1), NewApp.Match(NewApp.Large(rng, 2), rng, 0))

    ppt.Slides(6).Shapes("TextBox 1").TextFrame.TextRange.Text = xlsWs.Range("E6") & _

    "’s brand has established interest from all across China, with significant interest from the " & _

    ProvinceOne & " and " & ProvinceTwo & " provinces."

    Set NewApp = Nothing

    When I first tried to Dim rng, it said that "Range" was a user-defined type not defined error, so I changed it to "CellRange" and that seemed to fix that problem. Now I'm getting a method or data member not found error and it highlights the section of code that I've bolded above. What do you make of that?

    0 comments