Creating a Lookup Table
in [Visual Basic]
|
Prev: Datagrid problem
Next: Virtual memory increasing.
From: Webbiz on 8 Mar 2010 13:53 On Mon, 8 Mar 2010 11:37:03 -0500, "Nobody" <nobody(a)nobody.com> wrote: >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 > Thanks for taking the time to write this code example NOBODY. Much appreciated. I like the idea of using ADD and REMOVE methods as part of a class. Seems to be a 'clean' solution that is also portable if I have to do this again in another project. When it comes to Classes, I'm still a bit slow in catching on. I'm going to babble on here on what this is doing. If I'm off base, please let me know. The Form_Load() is where the object will be instantiated and data loaded into it. I can put this anywhere I want in the app, but for my purposes, the Form_Load() would fit just fine in triggering the 'SYMBOL OBJ INITIALISING' procedure. Within this procedure, I would have to load the data from a datafile to restore all the previously entered data. Q: Might be a dumb question, but is it possible to save a class object with its data to a file in one easy dump and load it as such to restore it, or do I have to loop through all the records within the class object when saving to and loading from disk? Q. Would it be wise/unwise to have the data load (from file) as part of the Class, perhaps as the Class Constructor triggered when an object is created from it? And then, to have all the data saved back to the file as part of the Class Destructor (Terminate?) ? Q. I find it interesting this technique of deliberately causing an error in order to branch to a different routine, such as the initial Redim of m_Symbols. Is this common practice? I'm thinking that my class won't need the Remove, FindChartName, FindSymbol and GetChartName routines. The GetChartName routine, if I were to keep it, would have to be modified anyway as it is possible to have more than one chart with the same symbol (although not the other way around). The only thing that needs to be returned from the object is the Symbol based on the Chartname supplied. Once I know the Symbol that was assigned to a chart, I can run the methods specific to that symbol elsewhere in code. This class will need a different type of 'Remove' routine though. I'll add this where the item is deleted and filled in with the last item (as suggested by another) since the order is not important within the array. Thanks! Webbiz
From: Webbiz on 8 Mar 2010 14:06 On Mon, 8 Mar 2010 12:17:11 -0600, "Larry Serflaten" <serflaten(a)usinternet.com> wrote: > >"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 > Don't laugh, but I'll have to read on 'Collections' as I've never used it personally. The user of the program can name his charts whatever he wants. But in order to retrieve information specific to the data loaded into the chart, the symbol needs to be associated with it. Each week, I will have a text file that can be downloaded which contains these symbols and some important data associated with it. There are 20 symbols. I have no idea how the user named his charts, so I have to leave it up to the user to assign the correct symbol to the respective chart. The app, noting there is a symbol file on the drive, will load this file into the app. When the user is viewing a chart that has been assigned to a symbol, the user can select the additional data be displayed on his chart by way of keypress, mouse click, menu or whatever. The app will load the text file that was downloaded so as to have the symbols and the associated data. The app will match the symbol assigned to the chart to the symbol in this file to get the correct additional data. This data is then applied to the chart in some way. Right now, the users already may have 10, 20 or more charts created from use over the previous months/years. When this feature is added, the user will have to update the app of course. Then the user will need to start assigning the symbols to the charts. Naturally, when the user exists the program the symbol assignments need to be saved so that the next time the app is run it will remember the assignments. If a chart is ever deleted, I will need to remove the chartname ->> symbol reference. If the user, for whatever reason, wants more than one chart to be assigned to the same symbol, this is to be allowed. Of course one cannot assign more than one symbol to a single chart though. That's it in a rather large nutshell. Can collections deal with this? I'm currently toying with NOBODY's Class suggestion and code. Thanks. Webbiz
From: Nobody on 8 Mar 2010 14:25 "Webbiz" <nospam(a)noway.com> wrote in message news:gkbap59vi85c1gpmfcb21lmsj4tv5unnrh(a)4ax.com... > Thanks for taking the time to write this code example NOBODY. Much > appreciated. > > I like the idea of using ADD and REMOVE methods as part of a class. > Seems to be a 'clean' solution that is also portable if I have to do > this again in another project. > > When it comes to Classes, I'm still a bit slow in catching on. I'm > going to babble on here on what this is doing. If I'm off base, please > let me know. > > The Form_Load() is where the object will be instantiated and data > loaded into it. I can put this anywhere I want in the app, but for my > purposes, the Form_Load() would fit just fine in triggering the > 'SYMBOL OBJ INITIALISING' procedure. Within this procedure, I would > have to load the data from a datafile to restore all the previously > entered data. > > Q: Might be a dumb question, but is it possible to save a class object > with its data to a file in one easy dump and load it as such to > restore it, or do I have to loop through all the records within the > class object when saving to and loading from disk? I haven't used this, so I don't know the details, but check the help for "PropertyBag object". You will find a topic called "Persisting a Component's Data". It probably doesn't apply to Standard EXE projects. Other than that, you can provide your own Load/Save functions that take a file name and save the information there. You can call these functions from Initialize/Terminate events, but these events don't return error codes, so it's best to call Load/Save methods explicitly. You can show a MsgBox, but I tend to avoid GUI code in classes and return an error code, and let the calling form show a MsgBox. This is just in case I use that class in a non-GUI app later, such as a command line only tool, or ActiveX in a web server, or as a service, which cannot show a GUI. > Q. Would it be wise/unwise to have the data load (from file) as part > of the Class, perhaps as the Class Constructor triggered when an > object is created from it? And then, to have all the data saved back > to the file as part of the Class Destructor (Terminate?) ? That's fine. See the above. > Q. I find it interesting this technique of deliberately causing an > error in order to branch to a different routine, such as the initial > Redim of m_Symbols. Is this common practice? That's very common and perfectly fine, especially when ReDim'ing arrays for the first time.
From: Webbiz on 8 Mar 2010 18:27 On Sun, 7 Mar 2010 20:09:27 -0500, "Jim Mack" <jmack(a)mdxi.nospam.com> wrote: >With fewer than 100 symbols, practically anything you do will feel >instantaneous. > >Since you're carrying the Symbol field in the array, the actual array >order doesn't matter and a deletion can just mean swapping the deleted >element with the last element, then ReDim Preserve the array one >element smaller. No shuffling needed. How does this look in doing that? This is a method in my CSymbol Class. Public Sub Delete(ByRef ChartName As String) Dim i As Long, j As Long Dim bResize As Boolean On Error Resume Next i = UBound(m_Symbols) If Err.number <> 0 Then Exit Sub Else If m_Symbols(i).ChartName = ChartName Then 'delete last record by resizing only bResize = True Else For j = 0 To i If m_Symbols(j).ChartName = ChartName Then 'match m_Symbols(j) = m_Symbols(i) 'Transfer last record over one to remove bResize = True Exit For End If Next j End If End If If bResize Then 'Resize to remove last element If i > 0 Then ReDim Preserve m_Symbols(i - 1) Else Erase m_Symbols End If End If End Sub
From: Webbiz on 8 Mar 2010 18:38
On Mon, 08 Mar 2010 17:27:02 -0600, Webbiz <nospam(a)noway.com> wrote: >On Sun, 7 Mar 2010 20:09:27 -0500, "Jim Mack" <jmack(a)mdxi.nospam.com> >wrote: > > >>With fewer than 100 symbols, practically anything you do will feel >>instantaneous. >> >>Since you're carrying the Symbol field in the array, the actual array >>order doesn't matter and a deletion can just mean swapping the deleted >>element with the last element, then ReDim Preserve the array one >>element smaller. No shuffling needed. > > >How does this look in doing that? This is a method in my CSymbol >Class. > >Public Sub Delete(ByRef ChartName As String) > >Dim i As Long, j As Long >Dim bResize As Boolean > > On Error Resume Next > > i = UBound(m_Symbols) > > If Err.number <> 0 Then > Exit Sub > Else > > If m_Symbols(i).ChartName = ChartName Then 'delete last record >by resizing only > bResize = True > Else > For j = 0 To i > If m_Symbols(j).ChartName = ChartName Then 'match > m_Symbols(j) = m_Symbols(i) 'Transfer last record >over one to remove > bResize = True > Exit For > End If > Next j > End If > > End If > > If bResize Then 'Resize to remove last element > If i > 0 Then > ReDim Preserve m_Symbols(i - 1) > Else > Erase m_Symbols > End If > End If > >End Sub I think maybe this one is better? Public Sub Delete(ByRef ChartName As String) Dim i As Long, j As Long Dim bResize As Boolean If m_SymbolsCount > 0 Then If m_Symbols(m_SymbolsCount).ChartName = ChartName Then 'delete last record by resizing only If m_SymbolsCount = 1 Then Erase m_Symbols mSymbolsCount = 0 Exit Sub End If Else For j = 1 To m_SymbolsCount If m_Symbols(j).ChartName = ChartName Then 'match m_Symbols(j) = m_Symbols(m_SymbolsCount) 'Transfer last record over one to remove ReDim Preserve m_Symbols(m_SymbolsCount - 1) mSymbolsCount = mSymbolsCount - 1 Exit Sub End If Next j End If End If End Sub Webbiz |