Do while problem comcerning Cells

SupportCommunity Automation and VBA Do while problem comcerning Cells

Ansicht von 5 Beiträgen - 1 bis 5 (von insgesamt 5)
  • Autor
    Beiträge
  • #12428

    Hello again,

    could you please explain to me, why this loop only works porperly until about “do while b < 10000":

    
    Sub test()
    Dim aDataset As DataSet
    Dim b As Long
    b = 1
        
        Dim fYFirstValue
    
        Set aDataset = ThisDatabase.RootFolder.Object("Time", fpObjectTypeDataSet)
        
         Do While b < 10000
        
        
        fYFirstValue =
    aDataset.Value(fpDataComponentY, 1, b)
        
        b = b + 1
        
         Loop
        
        Debug.Print fYFirstValue
        Debug.Print b
    End Sub

    My data row consits of about 2 Mio entries. Is there a way to change my liuttle macro in order to make it work?

    Thanks!

    #8109

    Hello again,

    could you please explain to me, why this loop only works porperly until about “do while b < 10000": [code] Sub test() Dim aDataset As DataSet Dim b As Long b = 1 Dim fYFirstValue Set aDataset = ThisDatabase.RootFolder.Object("Time", fpObjectTypeDataSet) Do While b < 10000 fYFirstValue = aDataset.Value(fpDataComponentY, 1, b) b = b + 1 Loop Debug.Print fYFirstValue Debug.Print b End Sub[/code] My data row consits of about 2 Mio entries. Is there a way to change my liuttle macro in order to make it work? Thanks!

    #8711
    Bernhard KantzBernhard Kantz
    Moderator

    This sample code should work.

    Please send your FlexPr database with the macro to
    support@weisang.com

    #8712

    It actuallay works, but after about 10000-100000 entries it’s getting very slow. Thus going through 2 Mio entries means probably several hours of waiting, without even calculating anything during this period of time.Just checking the data row.

    #8713
    Bernhard KantzBernhard Kantz
    Moderator

    With every enlargement of the data set, FlexPro has to copy the values internally, which is very expensive, particularly with larger data sets. Furthermore the data volume which is transferred to the Automation interface of FlexPro per access, is very low. You can avoid these problems by first gathering the data in an array and then transferring its content as a whole.

    
    Dim Data(1000000 - 1) As Double
    
    For i =  0 to 100000 - 1
    	Data(i) = NewValue
    Next
    With DataSeries
    	.NumberOfRows = .NumberOfRows + 1000000
    	.Range(fpDataComponentY, , .NumberOfRows - 1000000 + 1, , .NumberOfRows).Value = Data
    End With
    

    See Online Help
    Working with a data set
    Support@weisang.com

Ansicht von 5 Beiträgen - 1 bis 5 (von insgesamt 5)
  • Du musst angemeldet sein, um auf dieses Thema antworten zu können.