Databases
StackOverflow2013
Postsdbo
POExcel Cell/Range Logic As Array Logic
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POExcel Cell/Range Logic As Array Logic
    text
    copied!<p>I am working with alphanumeric data from a mainframe. Due to the nature of the access point, the GetString method is used within a webbrowser interface to pull data from the mainframe. I am refactoring my code as well as older code to make use of data structures instead of merely range objects, as range object code takes far longer with large data sets. </p> <p>As a part of <a href="http://www.cpearson.com/excel/optimize.htm" rel="nofollow noreferrer">general optimization practice</a>, I run all large data set macros with <code>Application.ScreenUpdating = False</code> and <code>Application.Calculation = xlCalculationManual</code> active. To time it, I use <a href="https://stackoverflow.com/questions/198409/how-do-you-test-running-time-of-vba-code">QueryPerformanceCounter</a> with a DoEvents after using the Counter in conjunction with the statusbar, so that it provides me the time it takes to complete a particular macro. The QueryPerformanceCounter is located in a Class Module and has played no direct role in executing the <a href="http://en.wikipedia.org/wiki/Business_logic" rel="nofollow noreferrer">domain logic / business logic</a> of my code.</p> <p>For instance, I recently refactored code that pulled 10,000 or so strings from the mainframe screen and placed them into a worksheet via a loop. When refactored into a datastructure loop, the code takes around 70 seconds when shucking the strings into an array. The code is also more portable, in that those strings could as easily be shifted/placed to a dictionary for sorting or a collection for parsing. I am therefore switching all my VBA code from range-based to datastructures, and this is the lead-in/background for my question.</p> <p>I came across some older code during an analysis project that has some interesting logic for pulling content from the mainframe. In essence, the code pulls content from the server in this layout form:</p> <p><img src="https://i.stack.imgur.com/y6I4Q.png" alt="Raw Data Pulled From Server Into Excel Sheet"></p> <p>And then parses the the content into this form in an excel sheet using Worksheet/Cell logic as a framework:</p> <p><img src="https://i.stack.imgur.com/nDl4q.png" alt="Data Parsed from Server into Excel Sheet"></p> <p>The code, sans the login/access logic as well as sans subroutine declarations, is as follows:</p> <pre><code>Sub AcquireData() CurrentServerRow = 13 WhileLoopHolder = 1 If Trim(CurrentSession.Screen.Getstring(CurrentServerRow, 9, 7)) &lt;&gt; "" Then NewWorksheetLine_Sub End If Do While WhileLoopHolder = 1 If CurrentSession.Screen.Getstring(CurrentServerRow, 9, 1) = "-" Then If Trim(CurrentSession.Screen.Getstring(CurrentServerRow + 1, 15, 1)) &lt;&gt; "" Then NewWorksheetLine_Sub End If ElseIf Trim(CurrentSession.Screen.Getstring(CurrentServerRow, 9, 7)) = "" Then If Trim(CurrentSession.Screen.Getstring(CurrentServerRow, 58, 14)) &lt;&gt; "" Then Cells(WorksheetRow, ValueSets) = Trim(CurrentSession.Screen.Getstring(CurrentServerRow, 58, 14)) ValueSets = ValueSets + 1 End If Else If CurrentSession.Screen.Getstring(CurrentServerRow, 5, 1) = "" Then Cells(WorksheetRow, WorksheetColumn) = "X" Else Cells(WorksheetRow, WorksheetColumn) = CurrentSession.Screen.Getstring(CurrentServerRow, 5, 1) End If Cells(WorksheetRow, WorksheetColumn + 1) = CurrentSession.Screen.Getstring(CurrentServerRow, 9, 7) Cells(WorksheetRow, WorksheetColumn + 2) = Trim(CurrentSession.Screen.Getstring(CurrentServerRow, 17, 39)) Cells(WorksheetRow, ValueSets) = Trim(CurrentSession.Screen.Getstring(CurrentServerRow, 58, 14)) WorksheetColumn = WorksheetColumn + 3 ValueSets = ValueSets + 1 End If CurrentServerRow = CurrentServerRow + 1 If CurrentServerRow &gt; 41 Then WhileLoopHolder = 0 End If Loop End Sub Sub NewWorksheetLine_Sub() WorksheetRow = WorksheetRow + 1 WorksheetColumn = 1 ValueSets = 10 End Sub </code></pre> <p>This code is nested in a loop within another program, and thereby pulls thousands of lines and organizes them neatly. It also takes hours and wastes valuable time that could be used analyzing the data acquired from the server. I managed to refactor the basic code into a data structure, and used my learning to refactor other code as well. Unfortunately, I refactored this particularly code incorrectly, as I am unable to mimic the business logic correctly. My snippet is as follows:</p> <pre><code>Sub AcquireData() 'This code refactors the data into a datastructure from a range object, but does not really capture the logic. 'Also, There is an error in attempting to insert a variant array into a collection/dictionary data structure. CurrentServerRow = 13 ReDim SourceDataArray(10) WhileLoopHolder = 1 If Trim(CurrentSession.Screen.Getstring(CurrentServerRow, 9, 7)) &lt;&gt; "" Then NewWorksheetLine_Sub End If Do While WhileLoopHolder = 1 If CurrentSession.Screen.Getstring(CurrentServerRow, 9, 1) = "-" Then If Trim(CurrentSession.Screen.Getstring(CurrentServerRow + 1, 15, 1)) &lt;&gt; "" Then NewWorksheetLine_Sub End If ElseIf Trim(CurrentSession.Screen.Getstring(CurrentServerRow, 9, 7)) = "" Then If Trim(CurrentSession.Screen.Getstring(CurrentServerRow, 58, 14)) &lt;&gt; "" Then ReDim Preserve SourceDataArray(ValueSets) SourceDataArray(ValueSets) = Trim(CurrentSession.Screen.Getstring(CurrentServerRow, 58, 14)) ValueSets = ValueSets + 1 ReDim Preserve SourceDataArray(ValueSets) End If Else If CurrentSession.Screen.Getstring(CurrentServerRow, 5, 1) = "" Then ReDim Preserve SourceDataArray(WorkSheetColumn) SourceDataArray(WorkSheetColumn) = "X" Else SourceDataArray(WorkSheetColumn) = CurrentSession.Screen.Getstring(CurrentServerRow, 5, 1) End If SourceDataArray(WorkSheetColumn + 1) = CurrentSession.Screen.Getstring(CurrentServerRow, 9, 7) SourceDataArray(WorkSheetColumn + 2) = Trim(CurrentSession.Screen.Getstring(CurrentServerRow, 17, 39)) SourceDataArray(ValueSets) = Trim(CurrentSession.Screen.Getstring(CurrentServerRow, 58, 14)) WorkSheetColumn = WorkSheetColumn + 3 ValueSets = ValueSets + 1 ReDim Preserve SourceDataArray(ValueSets) End If CurrentServerRow = CurrentServerRow + 1 If CurrentServerRow &gt; 41 Then WhileLoopHolder = 0 End If Loop End Sub Sub NewWorksheetLine_Sub() SourceIndexAsString = SourceCollectionIndex SourceDataCollection.Add SourceDataArray(), SourceIndexAsString SourceCollectionIndex = SourceCollectionIndex + 1 WorkSheetColumn = 1 ValueSets = 10 End Sub </code></pre> <p>I have considered that in order to use the same type of "cell" logic, I may want to use arrays nested within an array, and then transpose that to a worksheet. However, I have been thus far unsuccessful in implementing any such solution these past few weeks. Also, there may be a superior method of refactoring the logic to a datastructure form. However, I have been unable to determine how to do so successfully.</p> <p>To summarize, my questions are as follows: In what way(s) can I shift "cell"-based logic to data structure logic? What is the best data structure for doing so? In this particular case, how can I implement the use of data structure logic with the this business logic?</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload