在PowerPoint中查找数组中的最低值，次最低值和最高值VBA

Question

当用户在PPT幻灯片上运行GUI时，它会显示一个用户窗体，如下图所示。

他们最多可以选择在复选框3个危害。我试图弄清楚如何编写代码，以便标签中编号最小的复选框进入“Hazard1”，具有次数最少的标签（最多可选择3个）进入Hazard2 ，第三低的标签进入Hazard3。任何复选框的每个Tag属性只有一个数字。这是我将用于我的排名优先。

这是迄今为止代码：

Public dict, dict2, dict3, dict4, dict5 As Object, Key, val 'Makes the dictionaries public so they can be accessed by other Modules. 

Sub MainImageDict() 

'This is the dictionary for the Main Image portion of the slides. 

Set dict3 = CreateObject("Scripting.Dictionary") 

Key = "Day 1 High Temperatures": val = Array("URL_to_Image") 
dict3.Add Key, val 
Key = "Day 2 High Temperatures": val = Array("URL_to_Image") 
dict3.Add Key, val 

End Sub 

Function CountSelectedCheckBoxes(chkboxes As Variant) As Long 
    Dim ctrl As Control 
    ReDim chkboxes(1 To Me.Controls.count) 

    For Each ctrl In Me.Controls '<--| loop through userform controls 
     If TypeName(ctrl) = "CheckBox" Then '<--| check if current control is a "checkbox" one 
      If ctrl Then '<--| check if it's "checked" 
       CountSelectedCheckBoxes = CountSelectedCheckBoxes + 1 '<--| update checked checkboxes counter 
       Set chkboxes(CountSelectedCheckBoxes) = ctrl '<--| store it in the array 
      End If 
     End If 
    Next 
    If CountSelectedCheckBoxes > 0 Then ReDim Preserve chkboxes(1 To CountSelectedCheckBoxes) '<--|size checkboxes array to actual checked checkboxes found 
End Function 

任何想法：

Private Sub Hazards() 
    Call Dictionary.HazardsDict 

    'References the Dictionary for the Hazard Image options. 

    Dim chkboxes As Variant 
    Dim iCtrl As Long 

    Select Case CountSelectedCheckBoxes(chkboxes) 
     Case Is > 3 
      MsgBox "Too many selected checkboxes!" & vbCrLf & vbCrLf & "please select three checkboxes only!", vbCritical 
     Case Is = 1 'If only one checkbox is selected 
      For iCtrl = LBound(chkboxes) To UBound(chkboxes) 
       HazardList = Array(chkboxes(iCtrl).Caption) 
       Debug.Print chkboxes(iCtrl).Caption 
       Next 
       'MsgBox chkboxes(iCtrl).Tag '<--| here you output each selected checkbox Tag. This is the "number" to use in the ranking 
      For Each Ky In HazardList 
       ActiveWindow.Selection.SlideRange.Shapes("Hazard1").Fill.UserPicture (dict5.Item(Ky)(0)) 
       ActiveWindow.Selection.SlideRange.Shapes("Hazard1Text").TextFrame.TextRange.Text = dict5.Item(Ky)(1) 
      Next 
     Case Is = 2 'If exactly 2 checkboxes are selected 
      For iCtrl = LBound(chkboxes) To UBound(chkboxes) 
       HazardList = Array(chkboxes(iCtrl).Caption) 
       Debug.Print chkboxes(iCtrl).Caption 
      Next 
      For Each Ky In HazardList 
       ActiveWindow.Selection.SlideRange.Shapes("Hazard1").Fill.UserPicture (dict5.Item(Ky)(0)) 'The checkbox with the lowest number in its Tag would go here. 
       ActiveWindow.Selection.SlideRange.Shapes("Hazard1Text").TextFrame.TextRange.Text = dict5.Item(Ky)(1) 
       ActiveWindow.Selection.SlideRange.Shapes("Hazard2").Fill.UserPicture (dict5.Item(Ky)(0)) 'The checkbox with the second lowest tag number would go here 
       ActiveWindow.Selection.SlideRange.Shapes("Hazard2Text").TextFrame.TextRange.Text = dict5.Item(Ky)(1) 
      Next 
    End Select 

    Set dict5 = Nothing 

End Sub 

上面的代码指的是如下的解释？谢谢！

Answer 1

您可以通过调整CountSelectedCheckBoxes函数来返回一个包含所选复选框和计数的结构。下面是未经测试的代码，但我认为它会给你一个如何进行的想法。

声明自定义类型（结构）

Public Type structChkBoxeses 
    selectedCount As Long 
    first   As CheckBox 
    second   As CheckBox 
    third   As CheckBox 
End Type 

函数，返回你需要修复正常订单的复选框的逻辑选择复选框和他们的计数

注意。

Function GetSelectedChkBoxes(structChkBoxes As structChkBoxeses) As structChkBoxeses 

    Dim ctrl As Control 


    ReDim chkboxes(1 To Me.Controls.Count) 

    For Each ctrl In Me.Controls '<--| loop through userform controls 
     If TypeName(ctrl) = "CheckBox" Then '<--| check if current control is a "checkbox" one 
      If ctrl Then '<--| check if it's "checked" 

       With structChkBoxes 
        .selectedCount = .selectedCount + 1 '<--| update checked checkboxes counter 

        ' you need to add some logic here but 
        ' basically you want get all the checkboxes 
        ' assigned to the right variables 

        If .third Is Nothing Then 
         Set .third = ctrl 
        Else 
         If .third.Tag > ctrl.Tag Then 
          Set .second = .third 
          Set .third = ctrl 
         End If 
        End If 
       End With 

      End If 
     End If 
    Next 

    GetSelectedChkBoxes = structChkBoxes 

End Function 

如何利用结构来获取正确的字典项

Sub Hazards(structChkBoxes As structChkBoxeses) 

    For Each Ky In HazardList 
     With ActiveWindow.Selection.SlideRange 
      .Shapes("Hazard1").Fill.UserPicture (dict5.Item(structChkBoxes.first)(0)) 'The checkbox with the lowest number in its Tag would go here. 
      .Shapes("Hazard1Text").TextFrame.TextRange.Text = dict5.Item(Ky)(1) 
      .Shapes("Hazard2").Fill.UserPicture (dict5.Item(structChkBoxes.second)(0)) 'The checkbox with the second lowest tag number would go here 
      .Shapes("Hazard2Text").TextFrame.TextRange.Text = dict5.Item(Ky)(1) 
     End With 
    Next 

End Sub 

可能structChkBoxes.first需要是这样的CInt(structChkBoxes.first.tag)例。我不完全清楚你用什么字典键。

您还需要更改一些其他代码，因为您计算复选框的函数现在正在返回一个结构而不是数组。