Developing.ru

Код: Выделить всё

With Charts.Add
    .ChartType = xlXYScatterSmooth
    .SetSourceData Source:=Worksheets(1).Range("A1")
    .SeriesCollection(1).XValues = DT_Range
    .SeriesCollection(1).Values = Pr_Range
End With

Код: Выделить всё

Private Function RefEdit2Array(ByRef RE As RefEdit.RefEdit, ByRef Arr As Variant) As Long
Dim n As Long, Cell As Range
On Error GoTo load_err
  n = 1
  For Each Cell In Range(RE.Value)
    ReDim Preserve Arr(n)
    Arr(n) = Round(Cell.Value)
    n = n + 1
  Next
  RefEdit2Array = n - 1
  Exit Function
load_err:
  RefEdit2Array = 0
End Function

Private Sub CommandButton1_Click()
Dim DT_range() As Integer
Dim Pr_range() As Integer
Dim i As Long, j As Long
  i = RefEdit2Array(Me.RefEdit1, DT_range)
  j = RefEdit2Array(Me.RefEdit2, Pr_range)
  If (i > 0) And (i = j) Then
    With Charts.Add
      .ChartType = xlXYScatter
      With .SeriesCollection.NewSeries
        .XValues = DT_range 'Array(1, 3, 5, 2, 2, 4, 5)
        .Values = Pr_range
        .Name = "=""Pws vs. log((T+dT)/dT)"""
      End With
      .Location Where:=xlLocationAsNewSheet, Name:="Pws_Dt correlation"
    End With
  Else
    MsgBox "Что-то криво навыбирали, батенька! Все сначала! Pleeeease :)"
  End If
End Sub

Код: Выделить всё

Private Sub CommandButton1_Click()
    ' DT_Range - Range of Date.Time for each measurement of pressure
    ' Pr_Range - Range of measured pressure
    Dim DT_Range() As Variant
    Dim PR_Range() As Variant
    
    Dim Tp As Variant
    production = ActiveSheet.Range(RefEdit3.Value).Value
    shutin = ActiveSheet.Range(RefEdit5.Value).Value
    Tp = 24 * (Val(shutin - production))
    
    'In this section we fill DT_Range with data from RefEdit control on form
    ' Then we substract shut-in time from current time (delta t)
    ' Also we are converting values from date.time format to number
    'For Each c In ActiveSheet.Range(RefEdit4.Value)
    '    If c.Value > 0 Then
    '        n = n + 1
    '        'DT_Range(n) = Val(c.Value) - Val(RefEdit5.Value)
    '        xx = 24 * (c.Value - ActiveSheet.Range(RefEdit5.Value).Value)
    '        xx = Log10((xx + Tp) / xx)
    '        DT_Range(n) = xx
    '        MsgBox "DT_Range(" & n & ")" & DT_Range(n)
    '    End If
    'Next c
    
    n = -1
    
    For Each c In ActiveSheet.Range(RefEdit4.Value)
        If c.Value > 0 Then
            n = n + 1
            'DT_Range(n) = Val(c.Value) - Val(RefEdit5.Value)
            DT_Range(n) = 24 * (c.Value - ActiveSheet.Range(RefEdit5.Value).Value)
            
        End If
    Next c
    
    For k = 0 To n
        DT_Range(k) = Log10((Tp + DT_Range(k)) / DT_Range(k))
    Next k
    
    
      
    ReDim Preserve DT_Range(n) As Variant

    
    'In this section we fill Pr_Range with data from RefEdit2 control on form
    n = -1
    For Each c In ActiveSheet.Range(RefEdit2.Value)
        If c.Value > 0 Then
            n = n + 1
            'DT_Range(n) = Val(c.Value) - Val(RefEdit5.Value)
            PR_Range(n) = c.Value
            'MsgBox "DT_Range(" & n & ")" & DT_Range(n)
        End If
    Next c
    ReDim Preserve PR_Range(n) As Variant
    
    'We have to define other parameters for our calculations (in conditions of JOGMEC.TRC exercize)
    'Par_H - thikness of tested layer, feet     on paper
    'Par_Mu - viscosity of oil, cp              on paper
    'Par_B - formation volume factor os oil     on paper
    'Par_Fi - porosity, percents                on paper
    'Par_C - compressibility (total)            on paper
    'Par_Rw - raduws of flow, feet              on paper
    '
    'Par_Q - oil production rate, STB/day       in table
    'Par_Pwf - pressure just before shut-in,psi in table
    '
    'Par_M - slope of trendline
    '(plot P vs. log((Tp+dT)/dT)after
    'excluding wellbore storage effect          will be derived             slope of curve
    '
    'Par_P1hr - pressure after 1 hour of staying will be calculated         P1hr=
    
    'Output data we want to get from this fucking form
    '
    'Out_K - permeability, md
    'Out_S - skin factor
    'Out_dPsk - delta P skin, psi
    'Out_Jact - productivity index actual
    'Out_Jid - productivity index ideal
    'Out_Fef - flow efficiency
    
    

   
    A_Sheet = ActiveSheet.Name
    ActiveSheet.Range("IQ1").Select
    For k = 0 To n
        ActiveCell.Value = DT_Range(k)
        ActiveCell.Offset(1, 0).Range("A1").Select
    Next k
    ActiveSheet.Range("IQ1").Select
    e2 = "IQ1:IQ" & (n + 1)
    'ActiveSheet.Range(e2).Select
    ActiveSheet.Range("a1").Select
    Charts.Add
    ActiveChart.ChartType = xlXYScatter
    ActiveChart.SetSourceData Source:=Sheets(A_Sheet).Range("D5")
    'ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).Values = PR_Range
    ActiveChart.SeriesCollection(1).XValues = Sheets(A_Sheet).Range(e2) 'Zdes' prishlos' ubrat' DT_Range
    ActiveChart.SeriesCollection(1).Name = "=""Pws vs. T+dT/dT"""
    ActiveChart.Location Where:=xlLocationAsNewSheet, Name:= _
        "Pws_Dt correlation"
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "Pws vs. T+dT/dT"
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "t+dt"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Pws"
        .HasLegend = False
    End With
    With ActiveChart.Axes(xlCategory)
        .HasMajorGridlines = True
        .HasMinorGridlines = False
    End With
    With ActiveChart.Axes(xlValue)
        .HasMajorGridlines = True
        .HasMinorGridlines = False
    End With
    
    'Trying to define min & max values of scale
    
    
        ActiveChart.Axes(xlValue).Select
    With ActiveChart.Axes(xlValue)
        '.MinimumScale = PR_Range(0) * 0.9
        .MinimumScaleIsAuto = True
        .MaximumScaleIsAuto = True
        .MinorUnitIsAuto = True
        .MajorUnitIsAuto = True
        .Crosses = xlAutomatic
        .ReversePlotOrder = False
        .ScaleType = xlLinear
        .DisplayUnit = xlNone
    End With
    'MsgBox R_2(DT_Range, PR_Range, n)
    'MsgBox M_Slope(DT_Range, PR_Range, n)
    'MsgBox B_Intersect(DT_Range, PR_Range, n)
    
    'Finding optimal value of correlation
    'rop = 0
    For cp = 0 To n
        r1 = R_2(DT_Range, PR_Range, cp, n)
        If r1 >= 0.999 Then
           Exit For
        End If
        'If (rop > r1) Then
        '  Exit For
        'Else
        '  rop = r1
        'End If
    Next cp
    
    'MsgBox r1 & " c= " & c
    Sheets(A_Sheet).Activate
    Sheets(A_Sheet).Cells(1, 1).Value = "M_slope=" & M_Slope(DT_Range, PR_Range, cp, n)
    Sheets(A_Sheet).Cells(2, 1).Value = "B_Intersect=" & B_Intersect(DT_Range, PR_Range, cp, n)
    Sheets(A_Sheet).Cells(3, 1).Value = "R_2=" & r1
    Sheets(A_Sheet).Cells(4, 1).Value = "np=" & (cp + 1)
    'ActiveWorkbook.Charts.Delete
    
    ActiveSheet.Cells(1, 2).Select
    For X = 0 To cp
    ActiveCell.Value = DT_Range(X)
    ActiveCell.Offset(1, 0).Range("A1").Select
    Next X
    
    ActiveSheet.Cells(1, 3).Select
    
    For X = 0 To cp
    ActiveCell.Value = PR_Range(X)
    ActiveCell.Offset(1, 0).Range("A1").Select
    Next X
     
     
     'Sheets(A_Sheet).Cells(1, 3).Value = PR_Range(cp)
    
    Unload frmWell_Test
    
End Sub