6.6-查询问题

本节支持三个条件的联合查询：问题状态，问题种类，问题ID号。虽说是查询，实际上执行的逻辑也是显示问题，只不过只是显示满足要求的问题，基本逻辑如下：

• 1）删除所有问题

• 2）对数据库中所有数据进行遍历

• 3）满足要求的问题，将形状显示出来

代码如下

Sub 查询问题()
    Call delAllShape
    Set sht = ThisWorkbook.Worksheets("问题管理")
    problemStatus = sht.Range("H15")
    questionType = sht.Range("H16")
    uniqueId = sht.Range("H17")
    
    ' 获取行数
    maxRow = sht.Cells(Rows.Count, "Q").End(xlUp).Row
    
    For i = 3 To maxRow Step 1
        uniqueId_i = sht.Cells(i, "J").Value
        questionType_i = sht.Cells(i, "K").Value
        problemStatus_i = sht.Cells(i, "L").Value
        
        yn = checkCondition(uniqueId_i, questionType_i, problemStatus_i, uniqueId, questionType, problemStatus)
        
        If yn = True Then
            If questionType_i = "尺寸" Then
                shapeType = msoShapeOval
            Else
                shapeType = msoShapeRectangle
            End If
            
            positionX = sht.Cells(i, "R").Value
            positionY = sht.Cells(i, "S").Value
            widthVal = sht.Cells(i, "T").Value
            heightVal = sht.Cells(i, "U").Value
            fillColor = sht.Cells(i, "V").Value
            lineColor = sht.Cells(i, "W").Value
            
            Set newShape = sht.Shapes.AddShape(shapeType, positionX, positionY, widthVal, heightVal)
            shapeName = newShape.Name
            Debug.Print (shapeName)
            
            sht.Cells(i, "Q").Value = shapeName
            
            With newShape.Fill
                .Visible = msoTrue
                .ForeColor.RGB = fillColor
                .Transparency = 0
                .Solid
            End With
        
            With newShape.Line
                .Visible = msoTrue
                .ForeColor.RGB = lineColor
                .Transparency = 0
            End With
        End If
    Next i
End Sub

Function checkCondition(uniqueId_i, questionType_i, problemStatus_i, uniqueId, questionType, problemStatus)
    result = True
    
    ' 如果条件不为空，则判断与条件是否一致，任何一个条件不满足则不满足
    If uniqueId <> "" Then
        If uniqueId_i <> uniqueId Then
            result = False
        End If
    End If
    
    ' 如果条件不为空，则判断与条件是否一致
    If questionType <> "" Then
        If questionType_i <> questionType Then
            result = False
        End If
    End If
    
    ' 如果条件不为空，则判断与条件是否一致
    If problemStatus <> "" Then
        If problemStatus_i <> problemStatus Then
            result = False
        End If
    End If
    
    checkCondition = result

End Function

每显示一个问题，务必更新该形状对应的变量名称。在多条件联合查询中，先默认某一行满足要求，然后再逐一对每个查询条件进行判断，只要有一个不满足，则该行不满足，无需显示。