Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jan 2007
    Posts
    123
    Thanks
    13
    Thanked 1 Time in 1 Post

    Results written to cells, not message boxes (Excel 2002)

    I am learning VBA from a book (J. Walkenbach's For Dummies...) which is very good but so far it only is using message boxes to display answers to things like functions. I'd like to know how to display the answer to a function in a specified cell. How can you do this?

    For example:

    I've written the following function; don't worry about what it means except that it divides the input argument ("mmcfd") by 6. Here is the code for this function:

    Function MmcfdGasToMboed(mmcfd)
    MmcfdGasToMboed = mmcfd / 6
    End Function

    I've also written this caller function:

    Sub CallerForGasConversionFunction_GasValueInNamedCell ()

    'Here the function takes an argument from a named cell, "GasVolume"

    MsgBox MmcfdGasToMboed(Range("GasVolume"))

    End Sub

    It works fine; if the named cell "GasVolume" contains 12,000, then a message box correctly displays 2,000.

    But getting answers only on a message box is not very useful; how can I get the answer written to a specified cell, for example, G251?

    I'd be grateful for any advice.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Results written to cells, not message boxes (Excel 2002)

    You can replace the line
    <code>
    MsgBox MmcfdGasToMboed(Range("GasVolume"))
    </code>
    with
    <code>
    Range("G251") = MmcfdGasToMboed(Range("GasVolume"))
    </code>
    This assumes that cell G251 is in the active worksheet. If you want to store the result in cell G251 on a sheet named Results, you can use
    <code>
    Worksheets("Results").Range("G251") = MmcfdGasToMboed(Range("GasVolume"))
    </code>
    and if the Results sheet is stored in another open workbook Test.xls, use
    <code>
    Workbooks("Test.xls").Worksheets("Results").Range( "G251") = MmcfdGasToMboed(Range("GasVolume"))</code>

  3. #3
    2 Star Lounger
    Join Date
    Jan 2007
    Posts
    123
    Thanks
    13
    Thanked 1 Time in 1 Post

    Re: Results written to cells, not message boxes (Excel 2002)

    Thank you Hans.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Results written to cells, not message boxes (Excel 2002)

    Hans has given you the VBA code to put the result into a cell, which was probably what you wandted since you asked the question in the VBA forum. However, there is another way to do this that has the advantage of automatically updating the result cell if the data cell is changed. You could put this formula into the cell where you want the result:

    <code>
    =MmcfdGasToMboed(GasVolume)
    </code>
    Legare Coleman

  5. #5
    2 Star Lounger
    Join Date
    Jan 2007
    Posts
    123
    Thanks
    13
    Thanked 1 Time in 1 Post

    Re: Results written to cells, not message boxes (Excel 2002)

    Thanks Legare. You're quite right, this is how I would do it normally. I was just using this as an example; my main problem, while still in my VBA "Diapers", was how to make Mr Walkenbach's examples more useful than merely showing message boxes. But I appreciate you taking the time to answer, thanks again!

    Regards,

    Lingyai

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •