Hello all,

I do alot of VBA in Access but I am giving a try with VBA in Excel. I realise that Userforms and Access Forms are ... slightly different.

I am trying to create a Userform with a Listbox. I read in the help file that AddItem is fine if I want to populate a Listbox with one column but using List or Column (apparently with an array) is the way to go with a multi-column Listbox.

I need a push in the right direction to create the array that will contain a letter number and its corresponding letter. For example:

1, A
3, C
7, G

I am familiar with arrays but only one dimentional arrays. So to populate a Listbox with that data I need to create the array first right? A hint would be appreciated.

Thanks

Daniel

__________________
Daniel

When you dim an array, they are all numeric or string or variant without making a type.
Okay, not sure why you want to store the number when it would be the same that you'd use to call the letter(s) up with, but the example populates a table as follows:
1-26 in the first dimension is the alphabet positions
0-2 of second dimension tells what data is stored for that 'row' of the 'table'
value of 0 for the second dimension stores that number (1,2,3, etc...)
value of 1 for the second dimension stores the lower case value for that number (a,b,c, etc...)
value of 2 for the second dimension stores the upper case value for that number (A,B,C, etc...)

Code after populating table shows 2nd dimension values of the 12th 'row':
-----------------------------------------------------------------------
  Dim aLetterTable(26, 2) As Variant, iCnt As Integer
  For iCnt = 1 To 26
    aLetterTable(iCnt, 0) = iCnt '1 for a, A; 2 for b, B; ...26 for z, Z
    aLetterTable(iCnt, 1) = Chr(iCnt + 96) '97 is start of lower case letters (a).
    aLetterTable(iCnt, 2) = Chr(iCnt + 64) '65 is start of capital letters (A).
  Next
  MsgBox "Letter " & aLetterTable(12, 0) & " is:" & vbCrLf & vbCrLf _
    & "Lower Case: " & aLetterTable(12, 1) & vbCrLf _
    & "Upper Case: " & aLetterTable(12, 2)
-----------------------------------------------------------------------
Arrays can be very complex the more dimensions you add. You could, for instance, have a third dimension which could depict font/character set/whatever.

Not sure why you're taking up space making an array storing 1 for a and 2 for b considering array(1) is a and array(2) is b (etc.) but hopefully this gives you the insight you need for whatever project you need it for.

Hi allenm,
Well you were right. Array are abstract sometimes and after meditating for a while I realised that the 1 to 26 could very well be the array's index. So I decided to use a one dimension array and store the letter. Now, as you suggested:

aryLetters(1) = "a"
aryLetters(5) = "e"

to 26.

The purpose of this request has to do with Excel columns. As you know Excel numbers the columns from 1 to 256 (in VBA that is). In my project I needed to display to the user that his/her choices was in column M or R or whatever.

So I needed a function that returned the letter for any column number. Now you might wonder why not use the CHR() function simply? Well I wanted to display the list of columns affected in a Listbox control in a form therefore the array.

I succeded in my project and I further push it to include ALL excel column (from A to IV). So now if the situation is in column 50 I can return AX to the user instead of 50 or for column 232 it will be HX.

Thanks for your help allenm

Daniel

Hmm... this would be easier than using an array:
---------------------------------------------------
Private Sub CommandButton1_Click()

'BB is column 54. Here is an easy way to get column's letter value from it's number and vice versa
  Dim sAddr As String, sCol As String
  sAddr = Cells(5, 54).Address(True, False)
  sCol = Left(sAddr, InStr(1, sAddr, "$") - 1)
  MsgBox "Passed in column #54 and it's letter is: " & sCol 'Gets the column letters for given cell or range
  MsgBox "Passed in Column BB and it's column number is: " & Range("BB1").Column

End Sub
---------------------------------------------------
Would this help simplify your code?