Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multi-Dimensional Array
Hi,
I am trying to parse a SAS output text file (running into 4,00,000 lines containing multiple combinations), into a multi-dimensional array (4 dimensions). i know that only the last dimension can be increased and the middle dimensions are fixed. My question is how do i redim preserve the middle dimensions (1st, 2nd and 3rd)? i have seen people use Transpose function with 2 dimensions. but i came to know it has its limitations when it comes to size of data. so is there a function UDF to change the 1st 3 dimensions dynamically while adding / removing data? please let me know if someone has tried this before. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multi-Dimensional Array
hi noname,
here is an example, i hope this may help 'copy the table into a temporary array the same size. 'resize the table from two dimensions at once which in practice destroyed 'and recreated a table of the same name but different sizes 'pour the contents of the temporary array in the table "starting" resized. Table "start" should be resized larger than the table starting 'add new data in the table Sub RedimArray_xD() Dim Tblo() As Integer Dim Tmp() As Integer Dim a As Integer Dim i As Integer, j As Integer ReDim Tblo(3, 4) For i = 1 To 3 For j = 1 To 4 a = i * 10 + j Tblo(i, j) = a Next j Next i ReDim Tmp(3, 4) Tmp = Tblo 'depending on the version of Excel so can not work in this case using the loop below'For i = LBound(Tblo, 1) To UBound(Tblo, 1) ' For j = LBound(Tblo, 2) To UBound(Tblo, 2) ' Tmp(i, j) = Tblo(i, j) ' Next j 'Next i ReDim Tblo(5, 7) For i = LBound(Tmp, 1) To UBound(Tmp, 1) For j = LBound(Tmp, 2) To UBound(Tmp, 2) Tblo(i, j) = Tmp(i, j) Next j Next i For i = 1 To 5 For j = 5 To 7 a = i * 100 + j Tblo(i, j) = a Next j Next i For i = 4 To 5 For j = 1 To 4 a = i * 100 + j Tblo(i, j) = a Next j Next i Range("A1").Resize(UBound(Tblo), 6) = Application.Transpose(Tblo) End Sub -- isabelle |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multi-Dimensional Array
Hi Isabelle,
Is this example for a 4 dimensional array or a 2 dimensional one? Cheers! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multi-Dimensional Array
as an example, if i have an array:
Arr(0 to 5, 0 to 4, 0 to 10, 0 to 7) i can only increase the last dimension. if i want to dynamically dimension the 1st three, how do i do that? can a UDF function be written which can be passed a multi-dimensional array which will dynamically increase any of its dimensions? Arr(0 to 20, 0 to 10, 0 to 15, 0 to 7) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multi-Dimensional Array
noname wrote on 10/16/2011 :
Hi, I am trying to parse a SAS output text file (running into 4,00,000 lines containing multiple combinations), into a multi-dimensional array (4 dimensions). i know that only the last dimension can be increased and the middle dimensions are fixed. My question is how do i redim preserve the middle dimensions (1st, 2nd and 3rd)? i have seen people use Transpose function with 2 dimensions. but i came to know it has its limitations when it comes to size of data. so is there a function UDF to change the 1st 3 dimensions dynamically while adding / removing data? please let me know if someone has tried this before. I'd prefer to 'dump' the entire file into a Variant (resulting in a zero-based array) and parse each line as per need dictates. So each line in the text file is an element of the array, and each element gets parsed according to its delimiter. Example <aircode: 'Get the text Const sFilename As String = "C:\SAS.txt" Dim sFileText As String sFileText = ReadTextInFile(sFilename) '..where 'ReadTextInFile()' is a function that takes a filename arg 'Dump file contents into an array Dim vTextIn As Variant vTextIn = Split(sFileText, vbCrLf) 'Parse the array one element at a time Dim n As Long, x As Long, v As Variant Const sDelimiter As String = vbTab '//edit to suit For n = LBound(vTextIn) To UBound(vTextIn) v = Split(vTextIn(n), sDelimiter) For x = LBound(v) To UBound(v) '//do stuff code goes here Next 'x Nxt 'n This way you don't have to dimension arrays. You could even create each element in vTextIn as an array, resulting in an array of arrays. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multi-Dimensional Array
GS made a typo...:
Example <aircode: 'Get the text Const sFilename As String = "C:\SAS.txt" Dim sFileText As String sFileText = ReadTextInFile(sFilename) '..where 'ReadTextInFile()' is a function that takes a filename arg 'Dump file contents into an array Dim vTextIn As Variant vTextIn = Split(sFileText, vbCrLf) 'Parse the array one element at a time Dim n As Long, x As Long, v As Variant Const sDelimiter As String = vbTab '//edit to suit For n = LBound(vTextIn) To UBound(vTextIn) v = Split(vTextIn(n), sDelimiter) For x = LBound(v) To UBound(v) '//do stuff code goes here Next 'x Next 'n Sorry about that! -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multi-Dimensional Array
Hi Garry,
thanks for replying. what did you mean by "You could even create each element in vTextIn as an array, resulting in an array of arrays." can you explain this with an example? Thanks in advance. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multi-Dimensional Array
noname brought next idea :
Hi Garry, thanks for replying. what did you mean by "You could even create each element in vTextIn as an array, resulting in an array of arrays." can you explain this with an example? Thanks in advance. Using the example I posted, if you take each element of your vTextIn array and break it into an array using the Split() function then each element of vTextIn array is now an array in itself. So... For n = LBound(vTextIn) To UBound(vTextIn) vTextIn(n) = Split(vTextIn(n), sDelimiter) If lMaxCols < UBound(vTextIn(n)) Then lMaxCols = UBound(vTextIn) Next 'n 'Dump the array into a worksheet Range("A1").Resize(UBound(vTextIn), lMaxCols) = _ Application.WorksheetFunction.Transpose(vTextIn) -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multi-Dimensional Array
Hi Garry,
i get a type mismatch error on the following line: vTextIn(n) = Split(vTextIn(n), sDelimiter) any ideas why this is happening? i have declared vTextIn as a variant. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multi-Dimensional Array
Hi Garry,
i am getting a "Type Mismatch error" on this line: vTextIn(n) = Split(vTextIn(n), sDelimiter) i have defined vTextIn as a variant. any ideas why this is happening? |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multi-Dimensional Array
noname used his keyboard to write :
Hi Garry, i get a type mismatch error on the following line: vTextIn(n) = Split(vTextIn(n), sDelimiter) any ideas why this is happening? i have declared vTextIn as a variant. The code I posted was flagged as '<aircode' meaning it was intended to illustrate the concept of creating an array of arrays. In all probability it will be necessary to use ReDim Preserve on a new array and put each element of the source array into a new array rather than back into the source array. Also, it may require using another variable before populating the element... Dim vTemp As Variant vTemp = Split(vTextIn(n), sDelimiter) vTextIn(n) = vTemp ...where you create an array of the source array's element and place that back into the same element 'as an array'. This might be how VBA needs it to be so you don't get the 'type mismatch' error. Otherwise... Dim vNewArray() As Variant, vTemp As Variant vTemp = Split(vTextIn(n), sDelimiter) ReDim Preserve vNewArray(n): vNewArray(n) = vTemp ...which is probably the best approach. I don't have data to test this and so is why I flagged it as '<aircode'. Mostly, I dump entire ranges into a Variant and work with that because it's usually easier/faster than looping cells. For example, a range of table data ("A1:E10") results in a 10Row x 5Col 1 based array. So... myArray(1, 1) reps Cells(1, 1) [$A$1] of the range; myArray(2, 2) reps Cells(2, 2) [$B$2] of the range; myArray(1, 5) reps Cells(1, 5) [$E$1] of the range... -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multi-Dimensional Array
See my followup post regarding optional approaches to building the
array of arrays. As I said, I usually dump the contents of a range into an array and work with that, then dump the results back into the range. Note that large amounts of data are usually handled (using arrays) in comfortable sized 'blocks' rather than trying to process the entire file contents. This data appears to be XML or HTML and so yes, there's a much more efficient way to work with it. I haven't done much parsing of XML files but if you google for info to read/write XML files with VB6/VBA you'll find lots of stuff. I believe there's even a Class component available that lets you work the XML files similar to how INI files are handled. If you're pulling this from a website then you'll even find samples of how to parse the webpage source. Good luck! -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multi-Dimensional Array
Well the SAS output is in the form of an HTML file.
in Internet Explorer it looks like this: Number in Model R-Square SSE Variables in Model 1 0.6484 189.36323 x1 1 0.5855 223.25558 x2 1 0.5047 266.78526 x3 1 0.4643 288.52191 x4 2 0.6716 176.88309 x1 x2 2 0.6671 179.33269 x1 x4 2 0.6628 181.60644 x1 x3 2 0.6165 206.56222 x2 x4 2 0.6014 214.71458 x2 x3 2 0.5394 248.07512 x3 x4 3 0.6803 172.18992 x1 x2 x4 3 0.6740 175.61857 x1 x2 x3 3 0.6701 177.68955 x1 x3 x4 3 0.6183 205.57403 x2 x3 x4 4 0.6854 174.17081 x1 x2 x3 x4 ..... ..... currently this file has 16 variables and their combinations, but the variables could be more. hence the large size. i have to parse it in an array like this and then calculate the Shapeley coeff & score for each variable occurring with others & without others. so a better design is required so that i can group and sum variable combinations properly. |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multi-Dimensional Array
noname presented the following explanation :
Well the SAS output is in the form of an HTML file. in Internet Explorer it looks like this: Number in Model R-Square SSE Variables in Model 1 0.6484 189.36323 x1 1 0.5855 223.25558 x2 1 0.5047 266.78526 x3 1 0.4643 288.52191 x4 2 0.6716 176.88309 x1 x2 2 0.6671 179.33269 x1 x4 2 0.6628 181.60644 x1 x3 2 0.6165 206.56222 x2 x4 2 0.6014 214.71458 x2 x3 2 0.5394 248.07512 x3 x4 3 0.6803 172.18992 x1 x2 x4 3 0.6740 175.61857 x1 x2 x3 3 0.6701 177.68955 x1 x3 x4 3 0.6183 205.57403 x2 x3 x4 4 0.6854 174.17081 x1 x2 x3 x4 .... .... currently this file has 16 variables and their combinations, but the variables could be more. hence the large size. i have to parse it in an array like this and then calculate the Shapeley coeff & score for each variable occurring with others & without others. so a better design is required so that i can group and sum variable combinations properly. Now that I know this, you'll find what you need by googling "parse web page in VB6/VBA". Best wishes... -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multi-Dimensional Array
Hi Garry,
its a text output file containing HTML tags and not an HTML document. That is why, i am parsing it using Open File for Input As #1. instead of using arrays, can a Dictionary Object be incorporated? |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multi-Dimensional Array
On 10/19/2011 12:35 PM, noname wrote:
Hi Garry, its a text output file containing HTML tags and not an HTML document. That is why, i am parsing it using Open File for Input As #1. instead of using arrays, can a Dictionary Object be incorporated? Is it possible to post the file online so it can be downloaded and viewed? Would a VB6 program work for your needs? Mike |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multi-Dimensional Array
Hi Mike,
Yes, its possible. Also a VB program would do. please give me ur email addy. thanks :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Populating a multi dimensional array | Excel Programming | |||
Multi Dimensional Array | Excel Programming | |||
Multi Dimensional Array | Excel Programming | |||
Multi-Dimensional Array Let & Get | Excel Programming | |||
Viewing Multi dimensional array | Excel Programming |