Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update (VBA/Excel/97)

    I have an Excel VBA routine that I use to create a report. However, I have to enter some data into the report after the routine has run. I would like to be able to set certain formulas (e.g., sum) in the VBA routine, for example

    with activecell
    .value = "sum()"
    end with,

    so that when I enter the additional data, the formulas will update automatically. Can this be done?

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

    Re: Update (VBA/Excel/97)

    1. As you might guess, you shouldn't use the Value property of a cell to set its formula, but (surprise surprise) its Formula property.
    2. Since formulas begin with =, you must include the = in the code too.
    3. In a SUM formula, you must specify the range it will sum.
    4. Try to avoid selecting cells and using ActiveCell in code. Instead of

    Range("B37").Select
    ActiveCell.Formula = "=SUM(B2:B36)"

    use

    Range("B37").Formula = "=SUM(B2:B36)"

  3. #3
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update (VBA/Excel/97)

    Now this should update the cells after the routine has run and I add data to the spreadsheet, correct? For example, if "=sum(d2:d34)" is located in cell b25 and I input a number into a cell in the range d2:d25, the value in b25 should adjust to that entry.

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

    Re: Update (VBA/Excel/97)

    Yes, that is the idea of using a formula in Excel: if you enter data in the cells the formula refers to, the result of the formula will change.

  5. #5
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update (VBA/Excel/97)

    Yes, I understand that. But and the reason I made the post to begin with is that the above code doesn't work.

  6. #6
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update (VBA/Excel/97)

    Is there something in Excel that I'm missing? Is there a command that forces Excel to automatically update a formula's result if the underlying data is changed?

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

    Re: Update (VBA/Excel/97)

    Select Tools | Options... and activate the Calculate tab. Is calculation set to Manual? If so, set it to Automatic.

  8. #8
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update (VBA/Excel/97)

    Thanks. I had just run across that information in a past post on the Excel page. That's what I was missing.

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

    Re: Update (VBA/Excel/97)

    If you want to ensure that calculation is set to Automatic, you can include

    Application.Calculation =xlCalculationAutomatic

    in your code.

  10. #10
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update (VBA/Excel/97)

    If I create a function, the function that is called by the VBA routine should update automatically as well, correct? If I try

    activecell.formula = "=sum(b2:b36)"

    this works and updates when I change the values in the b range. However, I have a function that I created that doesn't update. It works if there is data inputed in the range when the routine executes, but not after the fact.

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

    Re: Update (VBA/Excel/97)

    Without seeing the spreadsheet and knowing what other code there is, I have no way of knowing what causes this.

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

    Re: Update (VBA/Excel/97)

    Excel keeps a list of all cells that are dependant on each cell. So, when you change a cells value, Excell uses that list to update only the cells that are dependant on the cell being changed. Therefore, if you have a formula in a cell that is not dependant on any cell in the range B2:B36 or on another cell that is debendant on a cell in that range, that cell will not be recalculated if a cell in B2:B36 is changed.
    Legare Coleman

  13. #13
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update (VBA/Excel/97)

    Here is the Function. "Counter" is a public variable; its value is determined in the Sub routine.

    Function BankCount()

    Dim j As Long
    Dim IDCRange As range
    Dim IDCBankCount As Double

    Set IDCRange = range(Cells(2, 4), Cells(Counter + 1, 4))

    For j = 1 To Counter
    If IDCRange(j) > 200 Then
    IDCBankCount = IDCBankCount + 1
    End If
    Next j

    BankCount = IDCBankCount

    End Function

    Would putting ActiveCell.formula = " =BankCount()" in the Sub routine not work?

Posting Permissions

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