Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Constant Array in VBA

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Constant Array in VBA

    You can define constants in VBA as

    Public Const a=10

    But how can you define a constant array? I need:

    a(1)=1
    a(2)=10
    a(3)=5

    or something like that.

    Many thanks

    Peter

  • #2
    Re: Constant Array in VBA

    Originally posted by neverland
    You can define constants in VBA as

    Public Const a=10

    But how can you define a constant array? I need:

    a(1)=1
    a(2)=10
    a(3)=5

    or something like that.

    Many thanks

    Peter
    Public Const a = (1, 10, 5)
    Brandtrock

    Brandtrock Consulting | Brandtrock Files | ISU Athletics | Bricktown | VBA Express

    Comment


    • #3
      Re: Constant Array in VBA

      That was indeed my first guess, but this highlights in red, hence it contains a syntactic error... I also tried with (1;10;5) { 1;10;5} and {1,10,5} and none of them worked

      Comment


      • #4
        Re: Constant Array in VBA

        Hi,

        Const arrays are not supported.
        Alternatives are individual consts with similar names,
        Code:
        Const A1=1
        Const A2=10
        Const A3=5
        Or store a string as a constant and the split it into a global array
        Code:
        Const AVALUES = "1:10:5"
        Public A(2) as Integer
        
        Public Sub MyMacro()
            A = Split(AVALUES, ":")
            Debug.Print A(0)
            Debug.Print A(1)
            Debug.Print A(2)
        End Sub
        Another alternative could be to use a class. Here you could use an array and make the values readonly.

        Cheers
        Andy

        Comment


        • #5
          Re: Constant Array in VBA

          Originally posted by Andy Pope
          Or store a string as a constant and the split it into a global array
          Code:
          Const AVALUES = "1:10:5"
          Public A(2) as Integer
          
          Public Sub MyMacro()
              A = Split(AVALUES, ":")
              Debug.Print A(0)
              Debug.Print A(1)
              Debug.Print A(2)
          End Sub
          This example seems just the thing. However if I try it, I get the following error:
          Compile Error: Can't assign to array

          What am I doing wrong?

          Comment


          • #6
            Re: Constant Array in VBA

            You do nothing wrong, my bad

            The result of the split function needs to be a variant and not a specified integer array.
            Try this mod.
            Code:
            Const AVALUES = "1:10:5"
            Public A(2) As Integer
             
            Public Sub MyMacro()
                Dim vntTemp As Variant
                Dim intIndex As Integer
                vntTemp = Split(AVALUES, ":")
                
                For intIndex = 0 To 2
                    A(intIndex) = vntTemp(intIndex)
                Next
                
                Debug.Print A(0)
                Debug.Print A(1)
                Debug.Print A(2)
            End Sub

            Cheers
            Andy

            Comment


            • #7
              Re: Constant Array in VBA

              Thanks,

              this works perfectly!!!

              Comment

              Trending

              Collapse

              There are no results that meet this criteria.

              Working...
              X