vendredi 30 octobre 2020

VBA forumula as a variable

I'm writing a VBA form which looks at a sheet of data and narrows down a selection based on drop boxes selected. The data sheet contains the options for each drop box under different headings eg

Drop box 1 - Please select your company -> narrows down contents of drop box 2

Drop Box 2 - Please select your department -> narrows down contents of drop box 3

Drop Box 3 - Do you wish to raise or approve -> narrows down to a selectable list

I have the above working, utilizing scripting dictionaries and a lot of nestled if statements, as per the below. Basically this populates the dictionary with any rows which match the relevant criteria.

Is there a neater way of doing this? It means reviewing the full criteria each time you get into the nestled if statement, so the 'If Not Dic.Exists(rCell.Value) And rCell.Value...' statement just gets longer and longer under each if statement.

I'm wondering if there is a way to have the componants of that line embedded as variables eg If Not Dic.Exists(rCell.Value) And rCell.Value <> "" And Statement1 And Statement2

where

Statement 1 = rCell.Offset(0, -2).Value = CompanySelect.Value
Statement 2 = rCell.Offset(0, 1).Value = "Display access"

I could then change the statements based on the different If values so if we were looking at display access it would be the above, but for approving it would be

Statement 1 = rCell.Offset(0, -2).Value = CompanySelect.Value
Statement 2 = rCell.Offset(0, 1).Value = "Approval"

Current code:

If CompanySelect.Value <> "" Then

If SAPDisplay.Value = False And SRMUse.Value = False And SAPEdit.Value = False Then
RoleSelector.RoleList.Clear
For Each rCell In ws.Range("C2", ws.Cells(Rows.Count, "C").End(xlUp))
    If Not Dic.Exists(rCell.Value) And rCell.Value <> "" And rCell.Offset(0, -2).Value = CompanySelect.Value And rCell.Offset(0, -2).Value = CompanySelect.Value Then
    Dic.Add rCell.Value, Nothing
    End If
Next rCell
For Each Key In Dic
    RoleSelector.RoleList.AddItem Key
Next
    Dic.RemoveAll

ElseIf SAPDisplay.Value = True Then
If SRMUse = False Then
    RoleSelector.RoleList.Clear
    For Each rCell In ws.Range("C2", ws.Cells(Rows.Count, "C").End(xlUp))
        If Not Dic.Exists(rCell.Value) And rCell.Value <> "" And rCell.Offset(0, -2).Value = CompanySelect.Value And rCell.Offset(0, -2).Value = CompanySelect.Value And rCell.Offset(0, 1).Value = "Display access" And rCell.Offset(0, 2).Value = "N/A" Then
            Dic.Add rCell.Value, Nothing
        End If
    Next rCell
    For Each Key In Dic
        RoleSelector.RoleList.AddItem Key
    Next
    Dic.RemoveAll

    ElseIf SRMUse = True Then

        If SRMDisplay.Value = False And SRMCreate.Value = False And SRMApprove.Value = False Then
        RoleSelector.RoleList.Clear
        For Each rCell In ws.Range("C2", ws.Cells(Rows.Count, "C").End(xlUp))
            If Not Dic.Exists(rCell.Value) And rCell.Value <> "" And rCell.Offset(0, -2).Value = CompanySelect.Value And rCell.Offset(0, -2).Value = CompanySelect.Value And rCell.Offset(0, 1).Value = "Display access" And rCell.Offset(0, 2).Value <> "N/A" Then
                Dic.Add rCell.Value, Nothing
            End If
            Next rCell
            For Each Key In Dic
            RoleSelector.RoleList.AddItem Key
            Next
            Dic.RemoveAll

Aucun commentaire:

Enregistrer un commentaire