Use VBA functions and variables in a spreadsheet

Question

I'm new to Excel VBA. I am trying to use a VBA function I found online that enables the user to use goalseek on multiple cells at a time. How do I call the function in a spreadsheet and how do I point to the cells that are supposed to be associated with the variables in the function (e.g. Taddr, Aaddr, gval). Do I have to write the cell values and ranges in the code itself and just run it that way?

Maybe I should redefine the function so that it takes these variables as input, so I can write a formula like =GSeekA(Taddr,Aaddr,gval)

Option Explicit

Sub GSeekA()
Dim ARange As Range, TRange As Range, Aaddr As String, Taddr As String, NumEq As Long, i As Long, j As Long
Dim TSheet As String, ASheet As String, NumRows As Long, NumCols As Long
Dim GVal As Double, Acell As Range, TCell As Range, Orient As String

    ' Create the following names in the back-solver worksheet:
    ' Taddr - Cell with the address of the target range
    ' Aaddr - Cell with the address of the range to be adjusted
    ' gval - the "goal" value
    ' To reference ranges on different sheets also add:
    ' TSheet - Cell with the sheet name of the target range
    ' ASheet - Cell with the sheet name of the range to be adjusted

    Aaddr = Range("aaddr").Value
    Taddr = Range("taddr").Value

    On Error GoTo NoSheetNames
    ASheet = Range("asheet").Value
    TSheet = Range("tsheet").Value

NoSheetNames:
    On Error GoTo ExitSub
    If ASheet = Empty Or TSheet = Empty Then
        Set ARange = Range(Aaddr)
        Set TRange = Range(Taddr)
    Else
        Set ARange = Worksheets(ASheet).Range(Aaddr)
        Set TRange = Worksheets(TSheet).Range(Taddr)
    End If

    NumRows = ARange.Rows.Count
    NumCols = ARange.Columns.Count

    GVal = Range("gval").Value

    For j = 1 To NumCols
        For i = 1 To NumRows
            TRange.Cells(i, j).GoalSeek Goal:=GVal, ChangingCell:=ARange.Cells(i, j)
        Next i
    Next j
ExitSub:
End Sub

Please take a moment to review wikihow.com/Create-a-User-Defined-Function-in-Microsoft-Excel. As far as the variables you pass those in when calling the function. — Sorceri
– Sorceri, Commented Nov 4, 2013 at 18:08
I am aware of how to pass variables, but the function above does not pass variables into the function. It seems to me that if I wanted to use the function, I would have to manually enter the cell values into the code — l3win
– l3win, Commented Nov 4, 2013 at 18:16
so give the function some parameters using those variables. Sub GSeekA(Aaddr As String) — Sorceri
– Sorceri, Commented Nov 4, 2013 at 18:42

Dick Kusleika · Accepted Answer · 2013-11-04 23:43:08Z

1

GSeekA is a Subprocedure, not a Function. Subprocedures cannot be called from worksheet cells like Functions can. And you don't want to convert GSeekA into a function. Functions should be used to return values to the cell(s) from which they're called. They shouldn't (and often can't) change other things on the sheet.

You need to run GSeekA as a sub. Now the problem becomes how you get user provided information into the sub. You can use InputBox to prompt the user to enter one piece of information. If you have too many, InputBox becomes cumbersome.

You can create areas in the spreadsheet where the user must enter information, then read from that area. That's how it's set up now. It's reading cells named asheet and tsheet. As long as those named ranges are present, the code works.

Finally, you can create a UserForm that the user will fill out. That's like putting a bunch of InputBoxes on one form.

Update Here's a simple procedure that you can start with and enhance.

Public Sub GSeekA()

    Dim rAdjust As Range
    Dim rTarget As Range
    Dim dGoal As Double
    Dim i As Long

    'Set these three lines to what you want
    Set rAdjust = Sheet1.Range("I2:I322")
    Set rTarget = Sheet1.Range("J2:J322")
    dGoal = 12.1

    For i = 1 To rAdjust.Count
        rTarget.Cells(i).GoalSeek dGoal, rAdjust.Cells(i)
    Next i

End Sub

edited Nov 4, 2013 at 23:43

answered Nov 4, 2013 at 19:56

Dick Kusleika

33.3k4 gold badges56 silver badges81 bronze badges

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

3 Comments

l3win Over a year ago

Thx for this Dick. How do I define my asheet, tsheet etc cells in my spreadsheet?

l3win Over a year ago

I should be able to run the subprocedure by inserting, e.g. Aaddr = Range("I2:I322").Value Taddr = Range("J2:J322").Value in the code and run it?

Dick Kusleika Over a year ago

No, I don't think so. Aaddr and Taddr are strings that look like cell references. What you need is a simpler procedure to start with, then you can enhance. I'm updating my answer.

Collectives™ on Stack Overflow

Use VBA functions and variables in a spreadsheet

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related