By Henrik


2016-07-10 11:44:34 8 Comments

Solved

"Global" Indexed variables and arrays has to be in a module and make sure ThisWorkbook and the different sheets do not contain any of those variables.


Hello, This is my first time asking a question here. I have only been programing in VBA for about 3 weeks and have no prior experiance of programing.

The Question:

My question is if it´s possible to declare indexed variables and arrays as Public which can be used by several subroutines and if so, how?

The problem:

I have got stuck on trying to declare an indexed variable and array as Public. When I run the first Sub "Public Sub ArrayToFinnish()" there is no problem, I ReDim the array within this sub. When I then call on another sub from withing "Public Sub ArrayToFinnish()" ,"Private Sub", the error message below appears and I get an error message saying:

"Compile error:

Constants, fixed-lengh strings, arrays, user-defined types and Declare statements not allowed as Public memebers of object modules"

The same happens with my array "arrData() As Variant"

 Option Explicit
Public wb As Workbook
Public Cell As Range
Public i1 As Long
Public A(1 To 4) As String    '<---- Indexed Variable
Public arrRow As Long
Public arrData() As Variant   '<---- Array

Public Sub ArrayToFinnish()

    Dim i2 As Long
    Dim j1 As Long, j2 As Long

    Dim Cell As String

    Dim lRow As Long
    Dim lCol As Long

    Dim rng As Range
    Dim aCell As Range

    A(1) = "String1"
    A(2) = "String1"
    A(3) = "String1"
    A(4) = "String1"

The error message looks like this:

Compile Error window

Thankful for any help you can provide!

//Henrik

As per request I will put up all the code. The error is marked with <-----------Error when calling KN

Option Explicit
Public wb As Workbook
Public Cell As Range
Public i1 As Long
Public A(1 To 4) As String
Public arrRow As Long
Public arrData() As Variant
'Public ListGroup()

Public Sub ArrayToFinnish()

    'Dim A(1 To 4) As String
    'Dim i1 As Long
    Dim i2 As Long
    Dim j1 As Long, j2 As Long

    'Dim arrData() As Variant

    Dim Cell As String

    Dim lRow As Long
    Dim lCol As Long

    Dim rng As Range
    Dim aCell As Range

    A(1) = "Ship Via Description"
    A(2) = "Speditor"
    A(3) = "Planned Ship Date/Time"
    A(4) = "Weight"

    'A(4) = "Customer Order"
    'A(5) = "Customer Number"

    Sheet1.Activate


    lRow = Sheet1.Cells.Find(What:="*", LookIn:=xlValues, SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    lCol = Sheet1.Cells.Find(What:="*", LookIn:=xlValues, SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column

    Set rng = Sheet1.Range(Sheet1.Cells(1, 1), Sheet1.Cells(1, lCol))

    ReDim arrData(1 To lRow, 1 To UBound(A, 1))
    'ListGroup = arrData(1 To lRow, 1 To Ubound(A,1))


    For i1 = 2 To lRow

        For j1 = 1 To UBound(A, 1)
            Set aCell = rng.Find(A(j1))
            Cell = Sheet1.Cells(i1, aCell.Column).Value

            Select Case Cell

                 Case Cell = "EXPRESS"

                 Case Cell = "TRUCK"

                 Case Cell = "CZ/DACHSER/Axis Communications LLC"

                 Case Cell = "DE/ASH Logistik/Abris"

                 Case Cell = "DE/EXP Cargo/RRC Cent. Asia"

                 Case Cell = "HU/Trans-Gate/IQ Trading"

                 Case Cell = "USA/Atlanta/Splitpoint"

                 Case "AIRFREIGHT"
                    arrRow = arrRow + 1
                    KN           <-----------**Error when calling KN**

                 Case Cell = "China/Shanghai/Splitpoint"

                 Case Cell = "Singapore/KN/CDP"

                 Case Cell = "US/Geodis/Miami"

                 Case Cell = "BR/Sao Paulo/Splitpoint"

                 Case Cell = "Japan / Multitek / Warehouse"
            End Select
        Next j1
    Next i1

End Sub

Private Sub KN()

    'DELETE should be global
    Dim wb As Workbook
    Set wb = ThisWorkbook
    lRow = Sheet1.Cells.Find(What:="*", LookIn:=xlValues, SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    lCol = Sheet1.Cells.Find(What:="*", LookIn:=xlValues, SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
    'DELETE

    Dim ws As Worksheet
    Dim j3 As Long
    Dim KCellD As Range, KCellW As Range
    Dim D As Date


    Set ws = wb.ActiveSheet


    Set rng = Sheet1.Range(Sheet1.Cells(1, 1), Sheet1.Cells(1, lCol))

    Set KCellD = rng.Find(A(3))
    Set KCellW = rng.Find(A(4))

    With ws


        D = Sheet1.Cells(i1, KCell.Column)

        Select Case D

            Case DateAdd("d", 1, Date)
                If .Cells(i1, KCellW.Column).Value >= 50 Then

                    For j2 = 1 To UBound(A, 1)
                        arrData(arrRow, j2) = .Cells(i1, j2).Value

                    Next j2
                End If


            Case DateAdd("d", 2, Date)
                If .Cells(i1, KCellW.Column).Value >= 1000 Then

                    For j2 = 1 To UBound(A, 1)
                        arrData(arrRow, j2) = .Cells(i1, j2).Value

                    Next j2
                End If

            Case Else
        End Select


End Sub

1 comments

@Shai Rado 2016-07-10 17:06:51

If you are using both Sub-Routines in the same Module, then it's enough just to declare them in the Option Explicit Section above all Modules.

I made some modifications to your code, and moved some variables declarations to the Option Explicit section.

Please look at the code below, I am able to get to the KN sub-routine. However, there is an error when checking KCell.Column value, as you are not setting KCell range (see code below, I marked where the error is now)

Option Explicit

Dim wb                                  As Workbook
Dim Cell, rng                           As Range
Dim A(1 To 4)                           As String
Dim arrData()                           As Variant
Dim arrRow, lRow, lCol                  As Long
Dim i1, i2, j1, j2                      As Long

'Public ListGroup()


Public Sub ArrayToFinnish()

    Dim Cell As String
    Dim aCell As Range

    A(1) = "Ship Via Description"
    A(2) = "Speditor"
    A(3) = "Planned Ship Date/Time"
    A(4) = "Weight"

    'A(4) = "Customer Order"
    'A(5) = "Customer Number"

    Sheet1.Activate

    lRow = Sheet1.Cells.Find(What:="*", LookIn:=xlValues, SearchDirection:=xlPrevious, SearchOrder:=xlByRows).row
    lCol = Sheet1.Cells.Find(What:="*", LookIn:=xlValues, SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column

    Set rng = Sheet1.Range(Sheet1.Cells(1, 1), Sheet1.Cells(1, lCol))

    ReDim arrData(1 To lRow, 1 To UBound(A, 1))
    'ListGroup = arrData(1 To lRow, 1 To Ubound(A,1))

    For i1 = 2 To lRow
        For j1 = 1 To UBound(A, 1)
            Set aCell = rng.Find(A(j1))
            Cell = Sheet1.Cells(i1, aCell.Column).Value

            Select Case Cell

                 Case Cell = "EXPRESS"

                 Case Cell = "TRUCK"

                 Case Cell = "CZ/DACHSER/Axis Communications LLC"

                 Case Cell = "DE/ASH Logistik/Abris"

                 Case Cell = "DE/EXP Cargo/RRC Cent. Asia"

                 Case Cell = "HU/Trans-Gate/IQ Trading"

                 Case Cell = "USA/Atlanta/Splitpoint"

                 Case "AIRFREIGHT"
                    arrRow = arrRow + 1
                    KN

                 Case Cell = "China/Shanghai/Splitpoint"

                 Case Cell = "Singapore/KN/CDP"

                 Case Cell = "US/Geodis/Miami"

                 Case Cell = "BR/Sao Paulo/Splitpoint"

                 Case Cell = "Japan / Multitek / Warehouse"

            End Select
        Next j1
    Next i1

End Sub

Private Sub KN()

    Dim ws                              As Worksheet
    Dim KCell, KCellD, KCellW           As Range
    'Dim j3                              As Long
    Dim D                               As Date

    Set wb = ThisWorkbook

    lRow = Sheet1.Cells.Find(What:="*", LookIn:=xlValues, SearchDirection:=xlPrevious, SearchOrder:=xlByRows).row
    lCol = Sheet1.Cells.Find(What:="*", LookIn:=xlValues, SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column


    Set ws = wb.ActiveSheet
    Set rng = Sheet1.Range(Sheet1.Cells(1, 1), Sheet1.Cells(1, lCol))

    Set KCellD = rng.Find(A(3))
    Set KCellW = rng.Find(A(4))

    With ws

        ' ****** Getting an error here , you are not setting KCell Range ******
        D = .Cells(i1, KCell.Column)
        Select Case D
            Case DateAdd("d", 1, Date)
                If .Cells(i1, KCellW.Column).Value >= 50 Then
                    For j2 = 1 To UBound(A, 1)
                        arrData(arrRow, j2) = .Cells(i1, j2).Value
                    Next j2
                End If

            Case DateAdd("d", 2, Date)
                If .Cells(i1, KCellW.Column).Value >= 1000 Then
                    For j2 = 1 To UBound(A, 1)
                        arrData(arrRow, j2) = .Cells(i1, j2).Value
                    Next j2
                End If
            Case Else ' not sure why need, you are not using it

        End Select

    End With

End Sub

Related Questions

Sponsored Content

1 Answered Questions

[SOLVED] Need to Copy Data only if Header is "Name"

  • 2019-07-19 12:41:45
  • khyati dedhia
  • 64 View
  • 2 Score
  • 1 Answer
  • Tags:   excel vba

8 Answered Questions

[SOLVED] How do I declare a global variable in VBA?

1 Answered Questions

2 Answered Questions

[SOLVED] Insert line after last row of specific text

  • 2017-10-26 17:31:03
  • B. Lee
  • 495 View
  • 0 Score
  • 2 Answer
  • Tags:   excel vba

17 Answered Questions

[SOLVED] How to declare global variables in Android?

2 Answered Questions

[SOLVED] How can I use wildcards with MyVlookup function?

2 Answered Questions

[SOLVED] access 2010 getting max row in excel 2010

Sponsored Content