I expect the student to do their homework and find all the errrors I leeve in.
Please take the time to read the Forum FAQ
There's been numerous 'discussions' about using Option Explicit and explicitly Dim-ing all variables with a specific Type when ever possible
Option Explicit tell VBA that all variable have to be Dim-ed and to me that helps eliminate silly errors
With out Option Explicit this will return nothing when you run the macro since 'asn' is NOT 'ans'
With Option Explicit that would generate an error that could be fixed before running the macroans = 2 x A(1,2) MsgBox asn
Option Explicit Dim ans as Long ans = 2 x A(1,2) MsgBox asn ' <<<<<< Compile time error MsgBox ans ' <<<<<< OK
Same error proofing applies to always Dim-ing variables with the type that the are
It's easy to just Dim everything as Variant, or just leave off the 'As String' part, but if the macro is relying on a variable being a number and you accidently assign a string to it, many lines later
In 'One' the error doesn't show up until many likes after the bad input
In 'Two' the error shows up on the line that generated the error
In 'Three' the code works the first time, but fails the second time
To me, it's easier to debug a problem with Option Explicit and accurate Dim-ing. Maybe eventually I'll get so good I don't make errors like that but intel then, I like to use them
Option Explicit Sub One() Dim a, b, c As Variant a = "asfsadf" b = 123 'many. many, many lines c = b * a ' run time Type mis-match error MsgBox c End Sub Sub Two() Dim a As Long, b As Long, c As Long a = "asfsadf" ' run time Type mis-match error b = 123 'many. many, many lines c = b * a MsgBox c End Sub Sub Three() Dim a As Long, b As Long, c As Long Range("A1").Value = 10 a = Range("A1").Value ' No run time Type mis-match error b = 123 'many. many, many lines c = b * a MsgBox c 'later Range("A1").Value = "asdfasdf" a = Range("A1").Value ' run time Type mis-match error b = 123 'many. many, many lines c = b * a MsgBox c End Sub
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
That was really helpful.
Now I understand better about Option Explicit, Dim-ing, Variants
Thanks a ton Paul.
Select the entire sheet.option to recompute entire sheet?
I expect the student to do their homework and find all the errrors I leeve in.
Please take the time to read the Forum FAQ
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
I thought "re-compute" meant re-process and your code processed the Selection.
I expect the student to do their homework and find all the errrors I leeve in.
Please take the time to read the Forum FAQ
I mean that I don't want to select the range and excel should automatically check last used row and fill in the results
Let us call what Paul's code does a "Process," or "Processing" so we can avoid confusion.
Applicable Range is that Range that was Processed, and/or needs Processing. I think what you meant when you said, "Entire sheet."
As I understand, the Process converts formulas to values.
One can set up Paul's code to Process: A Selection; The entire applicable Range; Or only the unprocessed, (new,) Cells in the applicable Range.
Set Start = Range("A:A").Find(What:="=", LookIn:=xlFormulas)
I expect the student to do their homework and find all the errrors I leeve in.
Please take the time to read the Forum FAQ
Fabulous!!!
Marking as Solved