Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Write to Module While Macro is Running
I have a macro that allows the user to study vocabulary words. At the
outset the array is filled with integers, one for each word. A number is randomly selected from the array and the word corresponding to that number is examined. That number is then removed from the array. Only after all words have been examined is the array is repopulated with all of the numbers. This allows to the user to examine all words before a specific word is encountered again. On exiting the workbook, whatever numbers remain in the array are written to a worksheet, so that the array can be filled with only these remaining numbers the next time the user opens the workbook. This way the user can resume where he or she left off last time. My question, rather than writing the current array to a worksheet, is there a way to write the actual array components (remaining integers) to the module? Everything works fine as is, I'm just wondering if this can be done and eliminate the need for the worksheet to store the array components on exit...TIA, Ron |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Write to Module While Macro is Running
You can visit Chip Pearson's site to see how to write code that writes code:
http://www.cpearson.com/excel/vbe.aspx But I wouldn't do it. I like your "save to a worksheet" much, much better. (Hide that sheet if haven't already!) ron wrote: I have a macro that allows the user to study vocabulary words. At the outset the array is filled with integers, one for each word. A number is randomly selected from the array and the word corresponding to that number is examined. That number is then removed from the array. Only after all words have been examined is the array is repopulated with all of the numbers. This allows to the user to examine all words before a specific word is encountered again. On exiting the workbook, whatever numbers remain in the array are written to a worksheet, so that the array can be filled with only these remaining numbers the next time the user opens the workbook. This way the user can resume where he or she left off last time. My question, rather than writing the current array to a worksheet, is there a way to write the actual array components (remaining integers) to the module? Everything works fine as is, I'm just wondering if this can be done and eliminate the need for the worksheet to store the array components on exit...TIA, Ron -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Write to Module While Macro is Running
Or save the array data in a database with ADO? The user can then
retrieve it from anywhere on the network. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Write to Module While Macro is Running
The following code saves an array of Longs to a non-visible defined
name and then gets the values out of that name and into an array. Sub SaveArray(Arr() As Long) Dim S As String Dim N As Long For N = LBound(Arr) To UBound(Arr) S = S & CStr(Arr(N)) & ";" Next N S = Left(S, Len(S) - 1) ' omit last ';' char ThisWorkbook.Names.Add "SaveArray", S, False End Sub Sub GetArray(Arr() As Long) Dim S As String Dim V() As String Dim N As Long S = ThisWorkbook.Names("SaveArray").RefersTo S = Replace(Mid(S, 3), Chr(34), vbNullString) V = Split(S, ";") ReDim Arr(LBound(V) To UBound(V)) For N = LBound(V) To UBound(V) Arr(N) = V(N) Next N End Sub As an example of how to use these functions, examine the following: Sub AAA() Dim A() As Long Dim N As Long ' initialize A with some arbitrary values. ReDim A(1 To 3) As Long A(1) = 100 A(2) = 200 A(3) = 300 ' save array to defined name "SaveArray" SaveArray A ' destroy existing array Erase A ' fill A with contents of defined name GetArray A ' list contents For N = LBound(A) To UBound(A) Debug.Print N, CStr(A(N)) Next N End Sub The SaveArray procedure saves the contents of the passed array to a string containing all the items in the array, separated by a ';' character. The GetArray gets the value of the defined name and puts the values back into the array. Cordially, Chip Pearson Microsoft MVP 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com [email on web site] On Wed, 27 Jan 2010 07:08:47 -0800 (PST), ron wrote: I have a macro that allows the user to study vocabulary words. At the outset the array is filled with integers, one for each word. A number is randomly selected from the array and the word corresponding to that number is examined. That number is then removed from the array. Only after all words have been examined is the array is repopulated with all of the numbers. This allows to the user to examine all words before a specific word is encountered again. On exiting the workbook, whatever numbers remain in the array are written to a worksheet, so that the array can be filled with only these remaining numbers the next time the user opens the workbook. This way the user can resume where he or she left off last time. My question, rather than writing the current array to a worksheet, is there a way to write the actual array components (remaining integers) to the module? Everything works fine as is, I'm just wondering if this can be done and eliminate the need for the worksheet to store the array components on exit...TIA, Ron |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Write to Module While Macro is Running
Thanks to all who responded. I'm going to take a closer look at
Chip's suggested approach, it combines a certain elegance and economy...Thanks again, Ron |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Write to Module While Macro is Running
I have around 1,000 words in the list. The "name" approach only holds
the first 66 numbers if I enter them as a string (as was suggested), or the first 82 numbers if I attempt to put the array directly into the name. Any thoughts Chip or others?..Ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Running a module from within a UserForm | Excel Programming | |||
2 Macros running on same module | Excel Worksheet Functions | |||
Running Excel module within Access | Excel Programming | |||
is it possible to execute write to the fields in another .xsl form a macro in another .xsl? e.g. some way to load another .xsl into an .xsl macro and write to its data? | Excel Worksheet Functions | |||
Write VBA code into a sheet module using VBA | Excel Programming |