VBA coding worksheet calculate ?

[Fixador]

hi all,

I have a macro running on sheet1 , which dumps data into a range - occassionally

What i would like to do - with a seperate macro, is detect when a change has occurred in that range , and then run code onward

Is there someway WorkSheet_Calculate() can be configured to respond only if changes occur in a specific range ?

I want to keep the response tight to a range, as there is other stuff happening elsewhere in the sheet !

At the moment, using Sub CommandButton , the code works fine, but soon as i run it with WorkSheet_Change, it just loops , and loops and loops ! Whoops !

cheers

[dermag]

a great line of code that I first came across on here thanks to Mitch, is ...

If Target.Columns.Count <> 16 Then Exit Sub

This "Target.Columns.Count <> 16 " in this example stops code running when it isn't a proper BA data dump, you would need to modify that line to only run the code when part of your (unkown) data range is changed.

Also the next line should be ...

Application.EnableEvents = False

as this will stop the code calling itself if the code changes a value in your target range, this is usually what makes it go into a fatal loop.

obviously finish the routine by switching EnableEvents back on, otherwise the next change won't be picked up.

Application.EnableEvents = True

hope that helps a bit

[Ian]

dermag - I think that's for a Worksheet_Change Event rather than a Calculate Event. I don't think there is an equivalent for Calculate Event.

[dermag]

Ian,

fixador asked if there was a way to only fire code when a certain part of a range is changed, I gave him an answer.

Whatever he wants to do can be done with worksheet_change and restricting the target range.

[Fixador]

Cheers guys,

I have just tried into Worksheet_Calculate :-

Application.EnableEvents = False

with

Application.EnableEvents = True

at the end

Works a treat - i thought it was ONLY for WorkSheet_ Change as well !

Anyway - my code has stopped looping and looping now - hurrah !

I reckon that is base one hit successfully - thank you !

Now this matter of picking up a change in the Range ......... I am puzzling over this . It seems it would be more efficient if the Application.EnableEvents = False was activated - AND the Sub was run , if there has been a change in my range, or more specifically - an addition to my range- so - if there is no change in the range ( no addition ) - i just EXIT SUB

As an example , say my Range that i am looking for changes in ( an addition to ) is BB10:BB50

This range is an increasing list of race names , additions are appended down the list , so if BB10 = "Stratford 3.45 wet and windy stakes", and is the last - and in this case ownly item in the list - ie, BB11 = "" as do all of the other cells =""

So . what i would like to do, is when text first appears in BB10 - is run my SUB, but not run it again ( in entirety ) - until BB11, changes from ="" to = "some text"

Must be something neat and tight to do this , my brain cell , has over excerised today

[Fixador]

This is what i had in mind re detecting a new addition to the range ............ if the range i want to monitor for changes ( an addition to list ) is cells(myrow,100) . I use cells(1,105) as a store between calls of the Sub ..........but surely there is a better way than this ????????????

Private Sub Worksheet_calculate()
Application.EnableEvents = False
For myrow = 1 To 10 Step 1
If Cells(myrow, 100) <> "" Then nosrows = 1 + nosrows
If Cells(myrow, 100) = "" Then Exit For
Next myrow
If nosrows = Cells(1, 105).Value Then ' no addition has occurred to range contents since last examination
Application.EnableEvents = True: Debug.Print "early exit"
Exit Sub
Else
Cells(1, 105).Value = nosrows ' an addition to range contents has occurred
'- so run main code from here
End If
Application.EnableEvents = True
End Sub

[dermag]

Hi Fixador,

As you seem to have already mastered the the actual copying of data into your range, and you seem to have it working so as it doesn't go haywire and repeat, then why not run the code that you want to do on the range, at the exact time that you actually copy the new data there?

It seems to me you have your answer already, instead of...

copy data to range....(works ok)

run code on range change....(doesn't work so well)

Why not just go for...

copy data to range.....run code on range...(all in the same routine)

[Fixador]

hello dermag,

i understand what you mean... the code that preceeds this 'data into cell range' is almost all VBA, what comes afterwards is ( currently ) sheet calculations - this is intended as a development milestone ! ie, the later code to be converted to VBA - later - much later !

I think it slows matters down considerably by writing to cells - and this does worry me !

And as for using a single cell as a memory device between calls of the SUB - is quaint ! ha ha ! almost embrassingly so !

But it works ! And it is much easier to debug sheet calculations than numbers stashed away in an array

I say " it works " - i have been pasting data into the range , and watching the effect - i actually havent linked to BA as yet

fixador

[Fixador]

And blow me down, my quaint code is working when hooked up to BA !

ok - its working - so far - after 1 selection.............

[Mitch]

Running a small section of code which writes to cells won't take enough time to worry about.

If you want to see how long it takes to run the code then try this.....

After your Application.EnableEvents = False line, put tmr = Timer

Before your Application.EnableEvents = True line, put Range("Z1") = Timer - tmr

You can change Z1 to whatever cell you like. The number of seconds taken will be put in it. I'd be very surprised if it were more than 0.1

[Fixador]

Hello Mitch, good to hear from you again

An interesting tool that - i will try it .

After 5 hrs runtime, i had - a stack overflow on an integer variable = 56,000 ! whoops , did i forget to 'zero' it ??? ha ha !

Unfortunately , it will be friday before i can put the blinkers on - and shove my coding head on .

I did notice , that sheet cells not involved with thre macro , were failing to display there contents - bearing in mind , this is a rather large sheet - now into CA columns, i wonder, was it just a case, that at 1 sec refresh from BA the display was unable to refresh or something ??? any ideas

Oh rats - i just noticed workSheetFunction. only calls certain sheet functions - and WONT call my own functions ! I spend ages getting those functions to work - now i have got to rehash them - to call then in code only - damnation !