Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old October 20th, 2007, 05:35 AM
Authorized User
 
Join Date: Feb 2005
Location: Frankston South, Victoria, Australia.
Posts: 34
Thanks: 0
Thanked 0 Times in 0 Posts
Default Populating a listbox

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
Reply With Quote
  #2 (permalink)  
Old October 26th, 2007, 03:13 PM
Friend of Wrox
Points: 513, Level: 8
Points: 513, Level: 8 Points: 513, Level: 8 Points: 513, Level: 8
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Feb 2007
Location: Davenport, IA, USA.
Posts: 163
Thanks: 0
Thanked 2 Times in 2 Posts
Default

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.


Reply With Quote
  #3 (permalink)  
Old November 1st, 2007, 01:51 PM
Authorized User
 
Join Date: Feb 2005
Location: Frankston South, Victoria, Australia.
Posts: 34
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #4 (permalink)  
Old November 1st, 2007, 02:13 PM
Friend of Wrox
Points: 513, Level: 8
Points: 513, Level: 8 Points: 513, Level: 8 Points: 513, Level: 8
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Feb 2007
Location: Davenport, IA, USA.
Posts: 163
Thanks: 0
Thanked 2 Times in 2 Posts
Default

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?

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
populating a listbox with a field contains comma bjcountry Access 3 February 17th, 2016 05:07 PM
multiple Listbox values in another listbox terryv Excel VBA 0 June 27th, 2007 07:01 AM
populating data into listbox adn datagrid madhava_ram Flash (all versions) 1 February 25th, 2005 11:46 PM
drop down is not populating spm74 ASP.NET 1.0 and 1.1 Basics 4 March 16th, 2004 12:36 PM
I'm back :) Listbox var from listbox MichaelTJ .NET Web Services 2 October 21st, 2003 07:06 PM



All times are GMT -4. The time now is 08:52 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.