How do I Refresh Excel whilst Debugging

Question!

If I am debugging (in this case a Visual Studio assembly called by Excel) and the code updates the Excel worksheet, how do I get Excel to redraw the current sheet / window whilst paused in the debugger?



Answers

If you are calling Excel cross-process (e.g. via Automation) then Excel should automatically show any changes as the calls are made, unless you set xlApp.ScreenUpdating = False. If this is the case, then you would probably be best off to use a conditional to not set xlApp.ScreenUpdating = False if running in Debug mode when you have break points set up. Otherwise, you'd have to set xlApp.ScreenUpdating = True from some other process (using GetObject(), or something similar, so that you can grab the same Excel Application instance).

However, if your code is being called in-process (for example, if your code is running as an add-in) then your code is being triggered via a hot-key combination, CommandBar button, Ribbon control callback or the like. In this case, Excel is single-threaded and your code is halting all Excel execution. The only thing I could think of, in this case, would be to use "edit and continue", adding the line xlApp.ScreenUpdating = True and then stepping into this next line. (Remember to remove this line afterward, though!)

If you are not using xlApp.ScreenUpdating = False, then I don't really know what your issue is, and you'd have to provide more details on your code...



I think what you mean is that a room BOOKING is for at least one guest. ANSI standard SQL would allow you to express the constraint as an ASSERTION something like:

create assertion x as check
   (not exists (select * from booking b
                where not exists
                   (select * from booking_guest bg
                    where bg.booking_id = b.booking_id)));

However, I don't suppose Postgres supports that (I'm not sure any current DBMS does).

There is a way using materialized views and check constraints, but I've never seen this done in practice:

1) Create a materialised view as

select booking_id from booking b
where not exists 
   (select * from booking_guest bg 
    where bg.booking_id = b.booking_id);

2) Add a check constraint to the materialized view:

check (boooking_id is null)

This constraint will fail if ever the materialized view is not empty, i.e. if there is a booking with no associated guest. However, you would need to be careful about the performance of this approach.



Actually, if you read the question, it states booked hotel rooms. This is quite easy to do as follows:

Rooms:
    room_id primary key not null
    blah
    blah

Guests:
    guest_id primary key not null
    yada
    yada

BookedRooms:
    room_id primary key foreign key (Rooms:room_id)
    primary_guest_id foreign key (Guests:guest_id)

OtherGuestsInRooms:
    room_id foreign key (BookedRooms:room_id)
    guest_id foreign key (Guests:guest_id)

That way, you can enforce a booked room having at least one guest while the OtherGuests is a 0-or-more relationship. You can't create a booked room without a guest and you can't add other guests without the booked room.

It's the same sort of logic you follow if you want an n-to-n relationship, which should be normalized to a separate table containing a 1-to-n and an n-to-1 with the two tables.

By : paxdiablo


This video can help you solving your question :)
By: admin