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).

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.

TrackBack URI for this entry

Comments (1)

Subscribe to this comment's feed

Ted said:

Very nice. I have avoided vba and macros up to this point but I think I'll give it a try.

Monday June 16, 2008 10:13AM

Show/Hide comment form

Name

Email

Title

Comment

Write the displayed characters

Add Comment