Excel/VBA formula

Advertisement


Question
QUESTION: Hi Tom

Could you tell me which module to place this code into?  I have tried several, including the "calculation" sheet but it does not seem to work.

Dim sh as worksheet
Set sh = Worksheets("calculation")
if sh.Range("B1").Value >= 5 then
sh.Range("D4").Value = "Not Available"
else
sh.Range("D4").Value = sh.Range("C1").value * 2
end if

Thanks

Chris Mitchell

ANSWER: Christopher Mitchell,

It depends on how you want it to operate.  If you want it to act like the formula is in the cell, then you would make it part of your calculate event which fires everytime the worksheet is calculated.  So you would right click on the sheet tab of the calculation sheet and select view code.  Then put this in the resulting module.  

Private Sub Worksheet_Calculate()
Dim sh as worksheet
Set sh = Worksheets("calculation")
if sh.Range("B1").Value >= 5 then
sh.Range("D4").Value = "Not Available"
else
sh.Range("D4").Value = sh.Range("C1").value * 2
end if
End Sub

As long as events are enabled and you code can run, then this should fire everytime the calculation sheet is recalculated.

--
Regards,
Tom Ogilvy


---------- FOLLOW-UP ----------

QUESTION: Hi Tom

I think that I am almost there.  The code is still not working for me probably because I don't know how to enable events.  Could you help me with this?

Chris Mitchell

Answer
Christopher Mitchell,

The code needs to be enhanced to avoid recursive calls.  So I have tested this routine and it worked for me:

Private Sub Worksheet_Calculate()
Dim sh As Worksheet
On Err GoTo errHandler
Application.EnableEvents = False
Set sh = Worksheets("calculation")
If sh.Range("B1").Value >= 5 Then
 sh.Range("D4").Value = "Not Available"
Else
 sh.Range("D4").Value = sh.Range("C1").Value * 2
End If
errHandler:
Application.EnableEvents = True
End Sub

Once you place that in the sheet code module, you can go back to the sheet and hit F9 and it should work.  If it doesn't, then one possibility is that there are no formulas in that sheet.

If there are no formulas that need calculation, then the sheet won't calculate and the event won't be called.  You can go to a blank cell and put in

=rand()
and make the font for that cell white if you wish.  Rand is a volatile function and should make the worksheet calculate anytime there is an action that would cause a calculation.

The other reason could be that macros are disabled (close the workbook, then open it and choose to have macros enabled).  The other cause could be that events are disabled.  If you close the workbook and reopen it and enable macros, then by default events should be enabled.  With the new code I gave you, if it had an error after it disabled events, then it could be problematic - but I have set it up to go to the error handler if an error occurs and the error handler enables events.  If there is no error, it still processed through the error handler and events are enabled.  The reason they must be disabled in the code is because I did put in a rand() function in my worksheet, so every change to the sheet triggers a calculate.  Since the code makes a change to the sheet, then it triggers a calculate and then you get an out of stackspace error because it continues to call itself.  Disabling events before the code makes a change to the sheet avoids this.  

So use the new code, save the workbook.  Close the workbook.  Reopen the workbook and enable macros.   Then it should work.

One other note.  If you have security set to disable macros without prompt, then you need to change you security settings to prompt before disabling macros.

--
Regards,
Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.