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:
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.
Very nice. I have avoided vba and macros up to this point but I think I’ll give it a try.