VBA UDF multi-cell reference

Question

I am trying to create a UDF that takes the values from 4 selected cells and performs a function with it. However I don't know how to write the function to recognize the cell values. The way I have it currently written I need to input a value. Can anyone help me with this relatively simple problem? This is what I have so far.

Function solubility(anion As String, cation As String, carbon1 As String, carbon2 As String)

EDIT

So for example I am trying to select cells A, C, E, and G and they contain values of Tf, Mi, Ch, and Ch. I will then use these cells refer to corresponding values in a range to calculate and display a final value. THe whole code is provided below.

Function solubility(anion As String, cation As String, carbon1 As String, carbon2 As String)

Dim sum As Double
Dim ncavalue As Long, nanvalue As Long, ncb1value As Long, ncb2value As Long
Dim nca As Long, nan As Long, ncab As Long
Dim coeff As Range, groups As Range

'solubility groups range
groups = Worksheets("Solubility").Range("A2:A33")

'group coefficients range
coeff = Worksheets("Solubility").Range("B2:B33")

'number of groups for each group
ncavalue = Range("AE" & cation.Row)
nanvalue = Range("AC" & anion.Row)
ncb1value = Range("AG" & carbon1.Row)
ncb2value = Range("AI" & carbon2.Row)

j = 0

    For j = 0 To UBound(groups)
        If UCase(anion.Value) = UCase(coeff(j).Value) Then
            'coefficient value
            anvalue = coeff(j).Value
        End If
        If UCase(cation.Value) = UCase(coeff(j).Value) Then
            'coefficient value
            cavalue = coeff(j).Value
        End If
        If cation.Value = "[MIm]" Then
            cavalue = Range("B2")
            ncb1value = ncb1value.Value + 1
        End If
        If UCase(carbon1.Value) = UCase(coeff(j).Value) Then
            'coefficient value
            cb1value = coeff(j).Value
        End If
        If UCase(carbon2.Value) = UCase(coeff(j).Value) Then
            'coefficient value
            cb2value = coeff(j).Value
        End If
    Next j
    sum = anvalue * nanvalue + cavalue * ncavalue + cb1value * ncb1value + cb2value * ncb2value
    solubility = sum + Range("B34").Value
End Function

What do you mean '4 selected cells'? Are you truly selecting them with a control-click, or do you just mean 4 specific cells? If the latter, then which cells do you want to use, and how are you trying to use them in your code? — Lance Roberts
– Lance Roberts, Commented Jul 22, 2011 at 8:29
So are they always the same cells or are you selecting them differently? Also A is not a cell, but A1 is, so what row are your cells in? — Lance Roberts
– Lance Roberts, Commented Jul 22, 2011 at 8:40
@ Lance Roberts - Yes, sorry about the confusion but cells A1, C1, E1, and G1. And the cells can be selected differently and not limited to the same row. — user816845
– user816845, Commented Jul 22, 2011 at 8:44
Well, I guess you can change the references in the UDF formula, or easier is to pick 4 other cells, and put in them a reference to the 4 you're selecting. If you had just one cell to select or a contiguous range, then you could trigger a change event off them, but otherwise it's not really trackable. — Lance Roberts
– Lance Roberts, Commented Jul 22, 2011 at 8:48

Lance Roberts · Accepted Answer · 2011-07-22 08:41:57Z

1

Assuming Row 1, call the Function like this:

=solubility(A1, C1, E1, G1)

answered Jul 22, 2011 at 8:41

Lance Roberts

22.9k32 gold badges115 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gaijinhunter Over a year ago

As Lance said, you're already set. When you pass cell numbers in as paramters, you are actually passing in the values of those cells.

Collectives™ on Stack Overflow

VBA UDF multi-cell reference

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related