Doing Hydraulics in Excel

1. Excel Hydraulics

Many SNAP routines can be called from other programs by linking to the hydra.dll, gaslift.dll, and IPR.dll calculation libraries.

Several third-party programs call the SNAP hydraulics library and use the embedded calculations to make complex calculations available without re-writing that code for each program.

This workbook has samples of simple direct calls to the SNAP hydraulics routines. It also has samples of more complex calls based on Excel reading a dataset, then modifying rate and other parameters within the worksheet macro.

The first tab of the spreadsheet has a few simple cases where the input is only TVD, MD, Diameter, Roughness, BHT, Correlation, and Gas lift depth. In these cases all other possible input values are assumed to match a set of typical defaults. The example shows the values calculated from wellhead to bottom-hole, then back the other direction to test the result using the DPPIPE function call as in the following image.

2. More Complex Calls to the SNAP Hydraulics Libraries

The more complex features allow a complete set of variables to be loaded from a SNAP dataset, then selected values can be changed from within Excel as in the example shown below from the second tab in the sample spreadsheet.

In this example, the dataset C:\program files\snap\data\1a2oilDarcGP.snp is loaded into the Visual Basic memory parameters using the macro LoadDataset. This internal macro is launched when the following button is pressed:

From that point, until the next dataset is loaded, any calls to the more detailed hydraulics routines will use the values loaded from the dataset, unless replaced through one of the hydraulics function calls or through a Visual Basic macro.

Pressing the FBHP button will launch a macro runWithLoadedVariables() that pulls in the values to the left of the FBHP column, calls the macro function hydrlicFullMPNoOutput(MP, Out), and returns a value to the spreadsheet. The program listing is included below.

Filling values down on the C, D, E, F, and G columns will increase the number of items run through the macro. It is recommended that this spreadsheet be stored as a sample and then saved as your own working copy. This will not affect the macro calls or links to the libraries.

2.1. VBA Macro

Public Sub runWithLoadedVariables()

   Dim ref As String

   If LoadedDataset = "" Then

       ret = MsgBox("no dataset reference is loaded", vbCritical)

       Exit Sub

   End If



   Range("b6").Value = ""

   Range("h3").Value = "WORKING....."

   Range("h3").Font.Color = RGB(255, 0, 0)

   For Row = 7 To 10000

       ref = "H" & Row

       If Val(Range(ref).Value) = 0 Then Exit For

       Range(ref).Value = ""

   Next Row

' loop down through values and post

   MP.glVolOpt = 1 ' always assume gas rate provided

   For Row = 7 To 10000

       ref = "C" & Row

       If Val(Range(ref).Value) = 0 Then GoTo ENDSUB

       MP.pressure = Range(ref).Value

       If (MP.PrimaryPhase = 1) Then

           MP.gas = Range("E" & Row).Value

           If (MP.gas = 0) Then GoTo FAIL

           liq = Range("D" & Row).Value + Range("F" & Row).Value

           MP.liquid = liq / MP.gas * 1000 ' this is actually yeald for gas wells

           If (liq > 0) Then

               MP.WCut = Range("F" & Row).Value / liq * 100

           Else

               MP.WCut = 0#

           End If

           MP.GLRate(0) = Range("g" & Row).Value

       Else ' must be oil

           MP.liquid = Range("D" & Row).Value + Range("F" & Row).Value

           oil = Range("D" & Row).Value  ' this is actually yeald for gas wells

           MP.gas = Range("E" & Row).Value ' holding area.  need to load GOR

           If (oil = 0) Then

               MP.GOR = 99999# ' FAIL

           Else

               MP.GOR = MP.gas / oil * 1000

           End If



           If (MP.liquid > 0) Then

               MP.WCut = Range("F" & Row).Value / MP.liquid * 100

           Else

               MP.WCut = 0#

           End If

           MP.GLRate(0) = Range("g" & Row).Value

       End If



       retval = hydrlicFullMPNoOutput(MP, Out)

       Range("h" & Row).Value = retval

       GoTo ENDLOOP

FAIL:

       Range("h" & Row).Value = "Error"

ENDLOOP:

   Next

ENDSUB:

   Range("h3").Font.ColorIndex = 10

   Range("h3").Value = "finished"

End Sub