Creating a Lookup Table
in [Visual Basic]
|
Prev: Datagrid problem
Next: Virtual memory increasing.
From: Nobody on 8 Mar 2010 11:37 Like others suggested, you can use a Dictionary object, but if you need to do more, you would have to make some workaround like using a separator character, but implementing your own would give you more control. Here is a class version of the code that I posted earlier: Output: Symbols Count = 5 Item4 is at 4 'D' is at 4 ChartName for 'C' is Item3 Symbol for chart name 'Item3' is C Symbols Count = 4 Item4 is at 0 'D' is at 0 ' Form1 code Option Explicit Private oSymbols As New CSymbols Private Sub Form_Load() oSymbols.Add "Item1", "A" oSymbols.Add "Item2", "B" oSymbols.Add "Item3", "C" oSymbols.Add "Item4", "D" oSymbols.Add "Item5", "E" Debug.Print "Symbols Count = " & oSymbols.Count Debug.Print "Item4 is at " & oSymbols.FindChartName("Item4") Debug.Print "'D' is at " & oSymbols.FindSymbol("D") Debug.Print "ChartName for 'C' is " & oSymbols.GetChartName("C") Debug.Print "Symbol for chart name 'Item3' is " & _ oSymbols.GetSymbol("Item3") oSymbols.Remove 4 Debug.Print "Symbols Count = " & oSymbols.Count Debug.Print "Item4 is at " & oSymbols.FindChartName("Item4") Debug.Print "'D' is at " & oSymbols.FindSymbol("D") End Sub ' CSymbols Class module code Option Explicit Private Type TSymbols ChartName As String Symbol As String End Type Private m_Symbols() As TSymbols Private m_SymbolsCount As Long ' Returns True when item successfully added, False when out of memory Public Function Add(ByRef ChartName As String, _ ByRef Symbol As String) As Boolean Dim i As Long On Error Resume Next i = UBound(m_Symbols) If Err.Number <> 0 Then ' Not Redimmed before Err.Clear ReDim Preserve m_Symbols(1 To 100) If Err.Number <> 0 Then ' Out of memory Add = False Exit Function End If End If If m_SymbolsCount = UBound(m_Symbols) Then ' Time to increase array size ReDim Preserve m_Symbols(1 To UBound(m_Symbols) + 100) If Err.Number <> 0 Then ' Out of memory Add = False Exit Function End If End If ' Add item to the array m_SymbolsCount = m_SymbolsCount + 1 m_Symbols(m_SymbolsCount).ChartName = ChartName m_Symbols(m_SymbolsCount).Symbol = Symbol Add = True ' Success End Function Public Sub Remove(ByVal Start As Long) Dim i As Long If Start >= 1 And Start <= m_SymbolsCount Then For i = Start To m_SymbolsCount - 1 m_Symbols(i) = m_Symbols(i + 1) Next m_SymbolsCount = m_SymbolsCount - 1 End If End Sub Public Property Get Count() As Long Count = m_SymbolsCount End Property ' Returns the index in m_Symbols() array, 0 if not found Public Function FindChartName(ByRef ChartName As String) As Long Dim i As Long For i = 1 To m_SymbolsCount If m_Symbols(i).ChartName = ChartName Then FindChartName = i Exit Function End If Next End Function ' Returns the index in m_Symbols() array, 0 if not found Public Function FindSymbol(ByRef Symbol As String) As Long Dim i As Long For i = 1 To m_SymbolsCount If m_Symbols(i).Symbol = Symbol Then FindSymbol = i Exit Function End If Next End Function ' Returns the corrosponding Symbol name, empty string if not found Public Function GetSymbol(ByRef ChartName As String) As String Dim i As Long For i = 1 To m_SymbolsCount If m_Symbols(i).ChartName = ChartName Then GetSymbol = m_Symbols(i).Symbol Exit Function End If Next End Function ' Returns the corrosponding ChartName, empty string if not found Public Function GetChartName(ByRef Symbol As String) As String Dim i As Long For i = 1 To m_SymbolsCount If m_Symbols(i).Symbol = Symbol Then GetChartName = m_Symbols(i).ChartName Exit Function End If Next End Function Private Sub Class_Terminate() Erase m_Symbols End Sub
From: Webbiz on 8 Mar 2010 11:58 On Mon, 8 Mar 2010 10:34:00 -0500, "Jim Mack" <jmack(a)mdxi.nospam.com> wrote: >Larry Serflaten wrote: >> "Jim Mack" wrote... >> >>> Posn = Instr(Search$, "|C ") \ 3 ' -1 if 0-based array >> >> Shouldn't that be: >> >> idx = InStr(Names, "|C ") \ 3 ' + 1 if 1-based array > >Yes. The problem with off-the-cuff code... > >Of course, we'd still have the issue of a not-found item. You probably >want to separate the InStr from the division by 3 -- or 4 (-: -- and >insert a test for 0. In this particular lookup, it's the long-winded description string that gets searched on and the two-char string that is returned. As to Larry's comment, I don't understand the 3-char suggestion. The symbols have always been 2-char. What would the extra char serve? Thanks. :-) Webbiz
From: Larry Serflaten on 8 Mar 2010 13:10 "Webbiz" <nospam(a)noway.com> wrote > As to Larry's comment, I don't understand the 3-char suggestion. The > symbols have always been 2-char. What would the extra char serve? Very early on, the year 'had always' been represented using 2 digits.... ....Until the Y2K scare made everyone rethink that idea. Some may not agree, but I advocate planning ahead on the assumption you will need to expand your designs, some time down the road. In other words, try to leave yourself a little extra room to grow, especially if the cost to do so now is minimal. Have you ever seen an SDK structure with extra bytes 'reserved'? Examples: BITMAPFILEHEADER COMMCONFIG DCB MCI_WAVE_SET_PARMS MIDIHDR OFSTRUCT Now some may be using bytes to help align the structure, while others might be put to use by the service that uses the structure and what not, but there are some there, where technological advancement may necessitate the need for more information in the structure and such space has already been alloted. In other words, they expected more improvements to come, and planned a little extra room in from the start. I call that keeping an eye on the future. No one has a working crystal ball, but adding a little foresight doesn't hurt, and usually doesn't cost that much in terms of time and resources. LFS
From: Jim Mack on 8 Mar 2010 13:11 Webbiz wrote: > > In this particular lookup, it's the long-winded description string > that gets searched on and the two-char string that is returned. Then it gets easier because your choice are limited. You just run Instr (or whatever) on each one it turn. It would still be very fast. > As to Larry's comment, I don't understand the 3-char suggestion. The > symbols have always been 2-char. What would the extra char serve? Just that if you hard-code a limit, you may find it insufficient later on -- it's a caution not to code yourself into a corner. Just because it's always been one way doesn't mean it won't need to change. The upfront cost is minimal, compared to the cost of mods. -- Jim Mack Twisted tees at http://www.cafepress.com/2050inc "We sew confusion"
From: Larry Serflaten on 8 Mar 2010 13:17
"Webbiz" <nospam(a)noway.com> wrote > In this particular lookup, it's the long-winded description string > that gets searched on and the two-char string that is returned. So would a Collection be the suitable vehicle? Set Symbols = New Collection Symbols.Add "CT", "May Cotton 2010" Symbols.Add "CT", "Cotton May 2010" Symbols.Add "CT", "My Cotton Chart 0510" Symbols.Add "S", "Soybeans" Symbols.Add "LC", "Apr Live Cattle 2009" ... etc... Such that you get the symbols back when you know the long name: sym = Symbols("Soybeans") ??? LFS |