[VBA] When Variable is value between 1 or 5 execute specific steps

[NessPJ]

Hi all,

I have a public variable in my routine called "AantalPallets" which can vary from 1 to 5.

I am now stepping in the code/routine that i pasted at the end of this post.

If the value is 3 for example:

I would like to execute the steps for Batch1, THT1, SSCC1 through xxx3 but not for xxx4 and xxx5.

Same goes for putting the values to a cell (which follows after that in the posted routine).

I would like to enter/process the values for Batch1, THT1, SSCC1 through xxx3 but not for xxx4 and xxx5.

Is there a way i should do this using an IF block, an Array for each of the xxx1, xxx2, xxx3 variables? or should i make this a For Loop ?
I'm a bit puzzled what the best approach would be.

    Public Batch1 As String, THT1 As String, SSCC1 As String, Batch2 As String, THT2 As String, SSCC2 As String, Batch3 As String, THT3 As String, SSCC3 As String, Batch4 As String, THT4 As String, SSCC4 As String, Batch5 As String, THT5 As String, SSCC5 As String
    Public SSCCVerplicht As String

Batch1 = Sheets("Menu").Range("H5").Value
THT1 = Sheets("Menu").Range("H6").Value
SSCC1 = Sheets("Menu").Range("H7").Value
Batch2 = Sheets("Menu").Range("H9").Value
THT2 = Sheets("Menu").Range("H10").Value
SSCC2 = Sheets("Menu").Range("H11").Value
Batch3 = Sheets("Menu").Range("H13").Value
THT3 = Sheets("Menu").Range("H14").Value
SSCC3 = Sheets("Menu").Range("H15").Value
Batch4 = Sheets("Menu").Range("K5").Value
THT4 = Sheets("Menu").Range("K6").Value
SSCC4 = Sheets("Menu").Range("K7").Value
Batch5 = Sheets("Menu").Range("K9").Value
THT5 = Sheets("Menu").Range("K10").Value
SSCC5 = Sheets("Menu").Range("K11").Value

If SSCCVerplicht = "J" Then
    GoTo SSCCVerplichtJa
Else
    GoTo SSCCVerplichtNee
End If

SSCCVerplichtJa:        'Controle of alle vereiste data wel is ingevuld

If Batch1 = vbNullString Or THT1 = vbNullString Or SSCC1 = vbNullString Or _
    Batch2 = vbNullString Or THT2 = vbNullString Or SSCC2 = vbNullString Or _
    Batch3 = vbNullString Or THT3 = vbNullString Or SSCC3 = vbNullString Or _
    Batch4 = vbNullString Or THT4 = vbNullString Or SSCC4 = vbNullString Or _
    Batch5 = vbNullString Or THT5 = vbNullString Or SSCC5 = vbNullString _
Then
    GoTo EindeLeeg
Else
    GoTo DataOkay
End If

Exit Sub

SSCCVerplichtNee:        'Controle of alle vereiste data wel is ingevuld (zonder SSCC)

If Batch1 = vbNullString Or THT1 = vbNullString Or _
    Batch2 = vbNullString Or THT2 = vbNullString Or _
    Batch3 = vbNullString Or THT3 = vbNullString Or _
    Batch4 = vbNullString Or THT4 = vbNullString Or _
    Batch5 = vbNullString Or THT5 = vbNullString _
    Then
    GoTo EindeLeeg
Else
    GoTo DataOkay
End If

Exit Sub


'Wegschrijven van de gegevens

DataOkay:

DatumTijd = Format(Now, "dd-mm-yy hh:mm")

Sheets("Registratie").Cells(RegistratieLR, 2).Value = DatumTijd
Sheets("Registratie").Cells(RegistratieLR, 2).NumberFormat = "DD-MM-YY hh:mm"

Sheets("Registratie").Cells(RegistratieLR, 3).Value = Usernr

Sheets("Registratie").Cells(RegistratieLR, 4).Value = Batch1                                     'Pallet 1
Sheets("Registratie").Cells(RegistratieLR, 5).NumberFormat = "@"
Sheets("Registratie").Cells(RegistratieLR, 5).Value = THT1
Sheets("Registratie").Cells(RegistratieLR, 6).NumberFormat = "@"
Sheets("Registratie").Cells(RegistratieLR, 6).Value = SSCC1

Sheets("Registratie").Cells(RegistratieLR, 7).Value = Batch2                                     'Pallet 2
Sheets("Registratie").Cells(RegistratieLR, 8).NumberFormat = "@"
Sheets("Registratie").Cells(RegistratieLR, 8).Value = THT2
Sheets("Registratie").Cells(RegistratieLR, 9).NumberFormat = "@"
Sheets("Registratie").Cells(RegistratieLR, 9).Value = SSCC2

Sheets("Registratie").Cells(RegistratieLR, 10).Value = Batch3                                     'Pallet 3
Sheets("Registratie").Cells(RegistratieLR, 11).NumberFormat = "@"
Sheets("Registratie").Cells(RegistratieLR, 11).Value = THT3
Sheets("Registratie").Cells(RegistratieLR, 12).NumberFormat = "@"
Sheets("Registratie").Cells(RegistratieLR, 12).Value = SSCC3

Sheets("Registratie").Cells(RegistratieLR, 13).Value = Batch4                                      'Pallet 4
Sheets("Registratie").Cells(RegistratieLR, 14).NumberFormat = "@"
Sheets("Registratie").Cells(RegistratieLR, 14).Value = THT4
Sheets("Registratie").Cells(RegistratieLR, 15).NumberFormat = "@"
Sheets("Registratie").Cells(RegistratieLR, 15).Value = SSCC4

Sheets("Registratie").Cells(RegistratieLR, 16).Value = Batch5                                     'Pallet 5
Sheets("Registratie").Cells(RegistratieLR, 17).NumberFormat = "@"
Sheets("Registratie").Cells(RegistratieLR, 17).Value = THT5
Sheets("Registratie").Cells(RegistratieLR, 18).NumberFormat = "@"
Sheets("Registratie").Cells(RegistratieLR, 18).Value = SSCC5

 

[dmt32]

Hi,

Firstly, where you have variables with same name & a suffix of same data type, it is cleaner to declare a single variable as an array sized with the required number of elements. You can then initialize & read the array in a loop which will save repeating code lines.

Most programmers try to avoid Variables declared as Public (global) as they remain in the memory until program execution is completed which can contribute towards unexpected issues - Unless there is no other way in your application, I personally would suggest that you scope your variables either at Module Level or Procedure level. If you need to share a variable with another procedure you can pass it as an argument.

Following is an update to your code changing your variables to arrays.

As you will see, I have incorporated your variable AantalPallets in a For Next Loop. This loop will, based on its value, only cycle up to 5 iterations. Each iteration will access the elements of each of the arrays to pass to the ranges.

Update is not tested and only intended for guidance; you will need to adjust to meet specific project need as required.

Sub NessPJ()
    Dim Batch(1 To 5)   As Variant, THT(1 To 5) As Variant
    Dim SSCC(1 To 5)    As Variant
    Dim i               As Long
    Dim DatumTijd       As Date
    
    
    With ThisWorkbook.Worksheets("Menu")
    
        For i = 1 To 5
            Batch(i) = .Cells(Choose(i, 5, 9, 13, 5, 9), IIf(i < 4, 8, 11)).Value
            THT(i) = .Cells(Choose(i, 6, 10, 14, 6, 10), IIf(i < 4, 8, 11)).Value
            SSCC(i) = .Cells(Choose(i, 7, 11, 15, 7, 11), IIf(i < 4, 8, 11)).Value
            If Len(Batch(i)) = 0 Or Len(THT(i)) = 0 Or Len(SSCC(i)) = 0 Then GoTo EindeLeeg
        Next
        
    End With
    
    DatumTijd = Format(Now, "dd-mm-yy hh:mm")

    With ThisWorkbook.Worksheets("Registratie")
    
        .Cells(RegistratieLR, 2).Value = DatumTijd
        .Cells(RegistratieLR, 2).NumberFormat = "DD-MM-YY hh:mm"
    
        .Cells(RegistratieLR, 3).Value = Usernr
    
        If AantalPallets > 5 Then AantalPallets = 5
        For i = 1 To AantalPallets
            .Cells(RegistratieLR, Choose(i, 4, 7, 10, 13, 16)).Value = Batch(i)    'Pallet 1 - 5
            With .Cells(RegistratieLR, Choose(i, 5, 8, 11, 14, 17))
                .NumberFormat = "@"
                .Value = THT(i)
            End With
            With .Cells(RegistratieLR, Choose(i, 6, 9, 12, 15, 18))
                .NumberFormat = "@"
                .Value = SSCC(i)
            End With
        Next i
    End With
    
EindeLeeg:
End Sub

Hope Helpful

Dave

 

[NessPJ]

Hi,

Firstly, where you have variables with same name & a suffix of same data type, it is cleaner to declare a single variable as an array sized with the required number of elements. You can then initialize & read the array in a loop which will save repeating code lines.

Most programmers try to avoid Variables declared as Public (global) as they remain in the memory until program execution is completed which can contribute towards unexpected issues - Unless there is no other way in your application, I personally would suggest that you scope your variables either at Module Level or Procedure level. If you need to share a variable with another procedure you can pass it as an argument.

Following is an update to your code changing your variables to arrays.

As you will see, I have incorporated your variable AantalPallets in a For Next Loop. This loop will, based on its value, only cycle up to 5 iterations. Each iteration will access the elements of each of the arrays to pass to the ranges.

Update is not tested and only intended for guidance; you will need to adjust to meet specific project need as required.

Sub NessPJ()  ...

Hope Helpful

Dave

Thanks a lot for the help! Your solution looks very efficient.

I use the Public variables because i am switching between Modules and Userforms.

I had 2 more questions:
- What does the IIF function do? (Im not familiar with this one).

- I also have a piece in the routine that will determine the .Min of a list of a values.
As with the previous routine, values xxx4 and xxx5 could sometimes be empty.
To try and solve this, i create a DotNetArray which i then convert to a VBA Array after checking if the values were empty or not.
Appearantly this is not exactly working the way i anticipated because values THT4 and THT5 still seem to get included leading to a .Min result of "0".

What am i still doing wrong here?

Dim DotNetTHTArray As Object
Dim THTArray As Variant
Dim FinalTHT As String, FinalTHTWrite As String

Set DotNetTHTArray = CreateObject("System.Collections.ArrayList")
If Not IsEmpty(THT1) Then DotNetTHTArray.Add THT1
If Not IsEmpty(THT2) Then DotNetTHTArray.Add THT2
If Not IsEmpty(THT3) Then DotNetTHTArray.Add THT3
If Not IsEmpty(THT4) Then DotNetTHTArray.Add THT4
If Not IsEmpty(THT5) Then DotNetTHTArray.Add THT5

THTArray = DotNetTHTArray.ToArray

FinalTHT = WorksheetFunction.Min(THTArray)

 

[dmt32]

.

I use the Public variables because i am switching between Modules and Userforms.

Generally not necessary, you can pass variables as arguments from your userform to procedures in modules & even a copy of the form object itself.

I had 2 more questions:
- What does the IIF function do? (Im not familiar with this one).

Explained in VBA Helpfile: IIf function (Visual Basic for Applications)

- I also have a piece in the routine that will determine the .Min of a list of a values.
As with the previous routine, values xxx4 and xxx5 could sometimes be empty.
To try and solve this, i create a DotNetArray which i then convert to a VBA Array after checking if the values were empty or not.
Appearantly this is not exactly working the way i anticipated because values THT4 and THT5 still seem to get included leading to a .Min result of "0".

What am i still doing wrong here?

Dim DotNetTHTArray As Object
Dim THTArray As Variant
Dim FinalTHT As String, FinalTHTWrite As String

Set DotNetTHTArray = CreateObject("System.Collections.ArrayList")
If Not IsEmpty(THT1) Then DotNetTHTArray.Add THT1
If Not IsEmpty(THT2) Then DotNetTHTArray.Add THT2
If Not IsEmpty(THT3) Then DotNetTHTArray.Add THT3
If Not IsEmpty(THT4) Then DotNetTHTArray.Add THT4
If Not IsEmpty(THT5) Then DotNetTHTArray.Add THT5

THTArray = DotNetTHTArray.ToArray

FinalTHT = WorksheetFunction.Min(THTArray)

Look into using the array solution suggested - Using a For Next Loop based on a variables value, each iteration will access required arrays elements.

Dave

 

[NessPJ]

Explained in VBA Helpfile: IIf function (Visual Basic for Applications)


Look into using the array solution suggested - Using a For Next Loop based on a variables value, each iteration will access required arrays elements.

Dave

But doesn't that mean if Values xxx1 through xxx4 are filled (and xxx5 is the only exception), this will not work?
Doesn't your routine assume that its either xxx1 through xxx3 or xxx1 through xxx5 ?

 

[dmt32]

But doesn't that mean if Values xxx1 through xxx4 are filled (and xxx5 is the only exception), this will not work?
Doesn't your routine assume that its either xxx1 through xxx3 or xxx1 through xxx5 ?

Number iterations would be based on the value of a variable

 For i = 1 to myVariable
  Msgbox myArray(i)
Next

- if your array has 5 elements but variable value is 3 then only 3 iterations would occur & read the first 3 elements of the array.


Dave

 

[NessPJ]

Number iterations would be based on the value of a variable

 For i = 1 to myVariable
  Msgbox myArray(i)
Next

- if your array has 5 elements but variable value is 3 then only 3 iterations would occur & read the first 3 elements of the array.


Dave

Okay, so now i have the following code which will iterate my array based on a variable... but my end result FinalTHT was still "0"

The AantalPallets Variable was set to 4 and THT1 through THT4 all contained valid data.
(AantalPallets and THT1 / THT4 data usually come from the userform).

Dim THTArray() As Variant
Dim AantalPallets As String
Dim THT1 As String, THT2 As String, THT3 As String, THT4 As String, THT5 As String

AantalPallets = 4

X = 1

ReDim THTArray(1 To AantalPallets)

For X = 1 To AantalPallets

THTArray(X) = THT & X

Next X

FinalTHT = WorksheetFunction.Min(THTArray)

Anything i'm not getting?

 

[dmt32]

Dim THT1 As String, THT2 As String, THT3 As String, THT4 As String, THT5 As String

If your variables are declared in procedure being called, they will be empty - If you are passing values from your userform to a module these, if they are not Public variables need to be passed as an argument.

Have look here:Pass Variables Between Macros — Excel Dashboards VBA

Dave

 

[NessPJ]

Dim THT1 As String, THT2 As String, THT3 As String, THT4 As String, THT5 As String

If your variables are declared in procedure being called, they will be empty - If you are passing values from your userform to a module these, if they are not Public variables need to be passed as an argument.

Have look here:Pass Variables Between Macros — Excel Dashboards VBA

Dave

Hello Dave,

In included the values in the code paste-in just to show they were actually declared (same goes for "AantalPallets"). Sorry for the confusion.

If i Break the code you will see that they come in with the following values:
AantalPallets = 4
THT1 = 210630
THT2 = 210630
THT3 = 210530
THT4 = 210630
THT5 = ""

Yet the result of "FinalTHT" is still 0.
It used to be 210530 (and it should be now as well).
Anything still wrong with my Array code?

 

[dmt32]

Hello Dave,

In included the values in the code paste-in just to show they were actually declared (same goes for "AantalPallets"). Sorry for the confusion.

If i Break the code you will see that they come in with the following values:
AantalPallets = 4
THT1 = 210630
THT2 = 210630
THT3 = 210530
THT4 = 210630
THT5 = ""

Yet the result of "FinalTHT" is still 0.
It used to be 210530 (and it should be now as well).
Anything still wrong with my Array code?

Depends how you are passing the values from your userform to the code in module?

For example

From your UserForm

Sub UserFormTest()
    Dim THT1 As Variant, THT2 As Variant, THT3 As Variant, THT4 As Variant, THT5 As Variant
    THT1 = 210630
    THT2 = 210630
    THT3 = 210530
    THT4 = 210630
    THT5 = 0
    
    MyCalledProcedure THT1, THT2, THT3, THT4, THT5

End Sub

Code in Standard module

Sub MyCalledProcedure(ParamArray MyArray() As Variant)
    Dim THTArray()      As Variant
    Dim x               As Long
    Dim AantalPallets   As Long, FinalTHT As Long
    
    
    AantalPallets = 4
    
    ReDim THTArray(1 To AantalPallets)
    
    For x = 1 To AantalPallets
        THTArray(x) = MyArray(x - 1)
    Next x
    
    FinalTHT = WorksheetFunction.Min(THTArray)
    
    MsgBox FinalTHT
    
End Sub

As you are passing a number of variables I have for this example, used a ParamArray as the parameter rather than specifying individual parameters for each.

If want to pursue this approach in your coding suggest research on web - plenty good material out there.

Dave