Results 1 to 13 of 13
Thread: Update (VBA/Excel/97)

20040625, 15:36 #1
 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?

20040625, 16:40 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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)"

20040625, 16:54 #3
 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.

20040625, 17:02 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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.

20040625, 17:42 #5
 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.

20040625, 17:45 #6
 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?

20040625, 18:10 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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.

20040625, 18:37 #8
 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.

20040625, 18:56 #9
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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.

20040628, 15:08 #10
 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.

20040628, 15:16 #11
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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.

20040628, 15:24 #12
 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

20040628, 15:53 #13
 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?