How to Use Excel Sheet as function?

Question

I am trying to find a way to use one excel sheet as a function.

I have a macro on a sheet in my file, i want to insert the result of the macro in another sheet for multiple combinations of input in the first one.

As suggested in this very old question: Excel: using a worksheet as a function?, I am trying to use Excel VBA, by making the same thing with my worksheet but I am receiving back a "#VALUE" error, what could it be?

This is the code

Function TEXTURE(sand, clay)
Sheets("Texture Calculator").Range("B4").Value = sand
Sheets("Texture Calculator").Range("D4").Value = clay
TEXTURE = Sheets("Texture Calculator").Range("G4").Value
End Function

I am new to VBA and I am just trying to find a solution by trial from the old post

Best, Giulio

Here's the file for more info https://www.dropbox.com/s/ha3cr4c9vi2ssb7/tab_suoli_2.xls?dl=0 — giulio, Oct 15 '16 at 20:09
Doing `?TEXTURE(0.7,0.1)` in VBA works fine... But: using it like a worksheet-function (UDF) will fail, because UDF can't "do" something. So changing a cell value or open a file or whatever, will error out the function. — Dirk Reichel, Oct 15 '16 at 20:29
You can't with `function` because user defined functions (`UDF`s) cannot modify a sheet. Is there a way to convert the sheet into VBA? — John Alexiou, Oct 16 '16 at 00:10

score 0 · Answer 1 · answered Oct 15 '16 at 23:36

Not sure I completely understood your question but it sounds like you want to build off the inputs for sand, silt and clay % and the subsequent soil type determination within your macro.

I changed the formatting of your input cells from 'Percentage' to 'General' and input percentages so that 20 is 20%, 15 is 15%, etc. The inequalities on Sheet ("Calculations") for basic categorization of soil following the soil texture triangle were hard coded in an if/elseif statement. See if this helps:

Sub Test()
Dim i As Integer: Dim sand_percent As Long: Dim clay_percent As Long: 
Dim silt_percent As Long:
Dim usda_texture As Variant:

For i = 4 To 7
  With Sheets("Texture Calculator")
      sand_percent = Range("B" & i).Value
      clay_percent = Range("D" & i).Value
      silt_percent = Range("F" & i).Value

      usda_texture = Texture_Determination(sand_percent, clay_percent, silt_percent)
      Range("G" & i).Value = usda_texture
  End With
Next i

End Sub

Function Texture_Determination(Sand As Long, Clay As Long, Silt As Long) As Variant

If Sand + Clay + Silt <> 100 Then
   MsgBox "Please ensure your inputs add up to 100%"
   Exit Function
End If

   'Determine the kind of soil texture using if/elseifs

If (Silt + 1.5 * Clay) < 15 Then
   soil = "Sand"
ElseIf Silt + 1.5 * Clay >= 15 And (Silt + 2 * Clay < 30) Then
   soil = "Loamy Sand"
ElseIf ((Clay >= 7 And Clay < 20) And (Sand > 52) And ((Silt + 2 * Clay) >= 30) Or (Clay < 7 And Silt < 50 And (Silt + 2 * Clay) >= 30)) Then
   soil = "Sandy Loam"
ElseIf ((Clay >= 7 And Clay < 27) And (Silt >= 28 And Silt < 50) And (Sand <= 52)) Then
   soil = "Loam"
ElseIf ((Silt >= 50 And (Clay >= 12 And Clay < 27)) Or ((Silt >= 50 And Silt < 80) And Clay < 12)) Then
   soil = "Silt"
ElseIf ((Clay >= 20 And Clay < 35) And (Silt < 28) And (Sand > 45)) Then
   soil = "Sandy Clay Loam"
ElseIf ((Clay >= 27 And Clay < 40) And (Sand > 20 And Sand <= 45)) Then
   soil = "Clay Loam"
ElseIf ((Clay >= 27 And Clay < 40) And (Sand <= 20)) Then
   soil = "Silty Clay Loam"
ElseIf (Clay >= 35 And Sand > 45) Then
   soil = "Sandy Clay"
ElseIf (Clay >= 40 And Silt >= 40) Then
   soil = "Silty Clay"
ElseIf (Clay >= 40 And Sand <= 45 And Silt < 40) Then
   soil = "Clay"
Else
soil = "Could not determine soil texture"
End If

Texture_Determination = soil

End Function

I have tried writing a function with the last part of your code and it works really fine. As I told you, I am new to excel VBA, but this seems really the best solution. Actually I need to convert silt, clay and sand percentages in the soil type. With this I don't need the sheet with the MACRO — giulio, Oct 17 '16 at 12:56

score 0 · Answer 2 · answered Oct 16 '16 at 13:14

As you've already been told, UDF can't change the environment.

But there are some workarounds

for instance

change your function as follows:

Function TEXTURE(sand, clay)
    TEXTURE = "Texture Calculator|" & sand & "|" & clay
End Function

place the following code in the worksheet code pane where you want to use your TEXTURE() UDT:

Private Sub Worksheet_Change(ByVal Target As Range)
    If InStr(Target.Value, "Texture Calculator|") > 0 Then
        Application.EnableEvents = False
        With Worksheets("Texture Calculator")
            .Range("B4").Value = Split(Target.Value, "|")(1)
            .Range("D4").Value = Split(Target.Value, "|")(2)
            Target.Value = .Range("G4").Value
        End With
        Application.EnableEvents = True
    End If
End Sub

this will leave the result of your original TEXTURE() function in the cell whenever you'd type =TEXTURE(number1,numnber2)

This is working, but Excel seems not to keep TEXTURE() function in the formula bar, but it directly plots CLAY. Also If I try to drag and drop it gives back a run-type error (13- type mismatch) — giulio, Oct 17 '16 at 12:52

How to Use Excel Sheet as function?

2 Answers2