Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 7 of 7

Thread: Constant Array in VBA

  1. #1
    Join Date
    18th February 2005
    Posts
    58

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    20th May 2003
    Location
    Mesa, Az
    Posts
    1,110

    Re: Constant Array in VBA

    Quote 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)

  3. #3
    Join Date
    18th February 2005
    Posts
    58

    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

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,310

    Re: Constant Array in VBA

    Hi,

    Const arrays are not supported.
    Alternatives are individual consts with similar names,
    VB:
    Const A1=1 
    Const A2=10 
    Const A3=5 
    
    
    Or store a string as a constant and the split it into a global array
    VB:
    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


  5. #5
    Join Date
    18th February 2005
    Posts
    58

    Re: Constant Array in VBA

    Quote Originally Posted by Andy Pope
    Or store a string as a constant and the split it into a global array
    VB:
    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?

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,310

    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.
    VB:
    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


  7. #7
    Join Date
    18th February 2005
    Posts
    58

    Re: Constant Array in VBA

    Thanks,

    this works perfectly!!!

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Array Constant From Cell Values
    By ertuoc in forum EXCEL HELP
    Replies: 4
    Last Post: August 27th, 2008, 06:44
  2. Array Constant Composed Of Named Constants
    By Stéphane Ernst in forum EXCEL HELP
    Replies: 12
    Last Post: June 25th, 2008, 17:33
  3. Use Cells For Array Formula Constant
    By Michael Avidan in forum EXCEL HELP
    Replies: 7
    Last Post: March 2nd, 2008, 01:40
  4. Replies: 2
    Last Post: October 5th, 2006, 22:33
  5. Replies: 6
    Last Post: November 1st, 2005, 05:33

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno