Testing Posts, Internet, Forum Software. German Telekom

[DocAElstein]

scratch left testie

Scrolling testie...


Example
Copy table contents to VBE module Post#4 Re Paste back to spreadsheet
4) Paste range values held in VBE module back to

So the last code, having been run when a range, such as this was selected, …._ _____ Workbook: Uebersicht Aktuelle.xls ( Using Excel 2007 32 bit )

Row\Col	G	H
2503	223.38	FH
2504	194.67	FH

Worksheet: Tabelle1_... would result in the '_-commented lines like these appearing at the last rows of the code module in which that code was run

End Sub

'_-23 12 2018 Worksheets("Tabelle1").Range("$G$2503:$H$2504")
'_-223.38    | FH
'_-194.67    | FH
'_- EOF 23 12 2018

In this post a code is presented for re pasting the original data values back into the original spreadsheet range. ( The code also removes the data from the code module )

Code description.
It is a bit difficult to give a detailed walk through description as the code goes around in circles ( it does Do Loops) a bit. So here is just a general description of the full code:
It is convenient to work backwards, deleting the lines in the VB Editor as we go: Unlike in a spreadsheet we always know in a code module that the last line is the last line of data, in other words the pseudo code module sheet has a last cell at the last data entry: We don’t have empty rows. Rows only exist as data is/ was entered
__1A Main outer Loop keeps going While we are not at the End of a routine ===============
_____Immediately within that is another Loop which keeps going While we have not reached the start line of a data section ( A start of a data section will look like this sort of form '_-21 12 2018 Worksheets("Sht_1").Range("$B$15:$D$16") )

It is not obvious from the routine layout, but the main action which is done first at every loop and initially after the routine begins is that which does the reading of the next line back from the end, in other words the current last line
____Let ReedLineIn = VBIDEVBAProj.Lines(StartLine:=VBIDEVBAProj.countof lines, Count:=1)
Immediately after that, the code will end if a routine End _____ type statement was read, or Else the line is deleted. Initially when the code starts we are directed immediately to this lower part of the code by virtue of
____If ReedLineIn = "" Then
'for an empty line we do nothing apart from having already deleted it ( for all but the first time here at the code start)
At this lower routine section, a code line is read, we will quickly leave the routine If we have read an End __ type statement. This is because the Loop While condition to keep looping is no longer met because we set the Boolean variable, EndOFSub , to True:

        Let ReedLineIn = VBIDEVBAProj.Lines(StartLine:=VBIDEVBAProj.countoflines, Count:=1)
            If ReedLineIn = "End Sub" Or ReedLineIn = "End Function" Then
             Let EndOFSub = True
            Else ' after reading in any line, we delete it, unless it was the End of a routine
             VBIDEVBAProj.DeleteLines StartLine:=VBIDEVBAProj.countoflines, Count:=1
            End If
        Loop While Not EndOFSub = True And FOB = False 
    Loop While EndOFSub = False ' 
End Sub

If we do not have the ReedLineIn = "" condition then we are at the part where things are done in each inner loop, ----|

The first thing that is done is to see
____If we have got, ( as a result of looping backwards “down” the code module occupied lines), to the point of a start section of a data section which will have a code line something like '_-22 12 2018 Worksheets("Tabelle1").Range("$A$2515") If that is the case Then we are at the ' Section to prepare data for, and to do, the paste out of a data value range
Various manipulation is then done to convert the collected information so far ( in String variable arrOut ) , into a form which will paste out to a spreadsheet range from the clipboard
____The Else situation here is ( If we are not at an end line of the data range ( like '_- EOF 22 12 2018 ) ) is simply to collect all the range text as a continual string, held in variable arrOut
____ We have now arrived at the end of doing things in the inner loop, and the code goes on to reading the next line, then checking for If at an End __ of routine, Else then deleting the line and
____We are now at the point of moving to the next loop of action to analyse and possibly do something with the last copied line held now in string variable ReedLineIn

_.___________________-

4b) Multi range data
The same code will paste out many ranges held in the code module. All ranges will be pasted back to their original spreadsheet range, and be removed from the code module
So for example , with this data in the same code as the routine Sub PubProliferous_Get_Rng__AsString() , will result in the following worksheet ranges being filled, (and also the data from the code module will be removed)
Worksheet "Tabelle1" , Range F2504:I2505
Worksheet "Tabelle2" , Range B392:D394
Worksheet "Globies" , Range F24:G25

    Loop While EndOFSub = False ' ================================================================
End Sub

'_-23 12 2018 Worksheets("Tabelle2").Range("$B$392:$D$394")
'_-gramms    | Kcal      | Fett
'_-300g      | 198Kcal   | 0
'_-51g       | 183,09K   | 4,08
'_- EOF 23 12 2018

'_-23 12 2018 Worksheets("Tabelle1").Range("$F$2504:$I$2505")
'_-41.09     | 194.67    | FH        | WH
'_-55.07     | 233.32    | FH        | WH
'_- EOF 23 12 2018

'_-23 12 2018 Worksheets("Globies").Range("$F$24:$G$25")
'_-GF  F     | GG  G
'_-Eiweiß    | Koh
'_- EOF 23 12 2018

After running the routine, Sub PubProliferous_Get_Rng__AsString() , the end of that code module will then look like this:

    Loop While EndOFSub = False ' ================================================================
End Sub

_.______________

Final routine is here:



In the next post, a slightly different routine will be presented. This routine will
not remove the range value data in the code module
and
only look for data corresponding to a given date.