VBA and Excel for Engineers and Scientists – Part 2

Creating Your Own Spreadsheet Functions

Creating a VBA function was described in a previous section. What I didn’t discuss was the fact that you can create functions in a VBA module that you can call from a worksheet cell using a similar syntax to a regular Excel formula. To help you understand what I mean, let me give you a simple and [relatively] practical example spreadsheet function.

Geotechnical practitioners will likely be familiar with the semi-quantitative terms for apparent density (Very loose, loose, medium dense, dense, very dense) and consistency (Very soft, soft, medium stiff, stiff, very stiff, hard) used to describe cohesionless soils and cohesive soils respectively. These terms are used based on the SPT N-Value for the soil. I created a simple function/formula example using the table of these values from Samtani and Nowatzki (2006).

Download the GeoPrac-VBA2-Examples.xls Spreadsheet (Excel 97-2003 format, but works with 2007 also)

Figure 3: Consistency or Relative Density Example

If you have SPT N-Value in Column B and % Passing #200 in Column C as shown in Figure 3, then your formula to return the correct descriptor might look like this:

=IF(C3>=50,IF(B3>30,”Hard”,IF(B3>15,”Very stiff”,IF(B3>8,”Stiff”,IF(B3>4,”Medium stiff”,IF(B3>2,”Soft”,”Very soft”))))),IF(B3>50,”Very dense”,IF(B3>30,”Dense”,IF(B3>10,”Medium dense”,IF(B3>4,”Loose”,”Very loose”)))))

This formula may be manageable, but imagine if there were complex calculations in there as well as the IF statements, and further imagine that after getting it all keyed in to the formula, you realized there was an error somewhere. Good luck trying to debug that. An alternative way of handling complex calculations or multiple nested-IF statements is to use a VBA function procedure such as the one below:

Listing 14: DensityOrConsistency Function Example

Function DensityOrConsistency(N60, PP200) As String    'Determines either the apparent density (for cohesionless soils) or the    'consistency (cohesive soils) based on the SPT N-Value (N60).    'Reference: Samtani, N. C and Nowatzki, E. A. (2006). Soils and Foundations    '              - Volumes I and II, Federal Highway Administration Report No.,    '              FHWA-NHI-06-088 and FHWA-NHI-06-089, Washington, D.C., P. 4-5.     Dim sResult As String 'Result to return     'First determine if we have cohesive or cohesionless soil    If PP200 >= 50 Then       'Cohesive soil       If N60 > 30 Then          sResult = "Hard"       ElseIf N60 > 15 Then          sResult = "Very stiff"       ElseIf N60 > 8 Then          sResult = "Stiff"       ElseIf N60 > 4 Then          sResult = "Medium stiff"       ElseIf N60 > 2 Then          sResult = "Soft"       ElseIf N60 >= 0 Then          sResult = "Very soft"       Else          sResult = "ERROR - Check N60"       End If    Else       'Cohesionless soil       If N60 > 50 Then          sResult = "Very dense"       ElseIf N60 > 30 Then          sResult = "Dense"       ElseIf N60 > 10 Then          sResult = "Medium dense"       ElseIf N60 > 4 Then          sResult = "Loose"       ElseIf N60 >= 0 Then          sResult = "Very loose"       Else          sResult = "ERROR - Check N60"       End If    End If     'Now return the value    DensityOrConsistency = sResult End Function 

To implement this function in your worksheet, your formula in Cell D3 would look like this:

=DensityOrConsistency(B3,C3)

Now if you want to transport your DensityOrConsistency function to another excel worksheet, you can simply copy the VBA module containing it to that spreadsheet and use it the same way.