Εισαγάγετε χρόνο χωρίς άνω και κάτω τελεία - Συμβουλές για το Excel

Η ερώτηση Excel αυτής της εβδομάδας προέρχεται από τον John που σταθμεύει στην Οκινάουα.

Δημιουργώ ένα υπολογιστικό φύλλο Excel για να αντικατοπτρίζω τις αναχωρήσεις και τις αφίξεις. Θα υπάρχουν βασικά τρία κελιά: Πραγματική ώρα αναχώρησης, εκτιμώμενη ώρα εισόδου και εκτιμώμενη ώρα άφιξης. Θα ήθελα το άτομο να είναι σε θέση να εισέλθει (για παράδειγμα) 2345 και να κάνει το κελί να μορφοποιήσει αυτόματα την οθόνη ώστε να δείχνει 23:45. Αυτό που παίρνω αντί είναι 0:00, ανεξάρτητα από τον τύπο ή τη μορφοποίηση. Και, ο υπολογισμός δεν θα εμφανίζει τίποτα εκτός από το 0:00, εάν ο χρήστης δεν αλλάξει το πλήκτρο και το άνω και κάτω τελεία. Γνωρίζω ότι φαίνεται απλό να το κάνουμε, ωστόσο, κάθε μικρό δευτερόλεπτο αποθηκεύεται, ειδικά όταν εισάγετε παρόμοια δεδομένα ξανά και ξανά στο Excel.

Για να το κάνετε αυτό, πρέπει να χρησιμοποιήσετε ένα πρόγραμμα χειρισμού συμβάντων. Οι χειριστές συμβάντων ήταν καινούργιοι στο Excel 97 και συζητήθηκαν ξανά στο Run a Macro Every Time a Cell Value Change in Excel. Ωστόσο, πίσω σε αυτήν την άκρη, ο χειριστής συμβάντων εφάρμοζε διαφορετική μορφή σε ορισμένα κελιά. Αυτή η εφαρμογή είναι ελαφρώς διαφορετική, οπότε ας επανεξετάσουμε το πρόγραμμα διαχείρισης συμβάντων.

Ο χειριστής συμβάντων είναι ένα μικρό κώδικα μακροεντολών που εκτελείται κάθε φορά που συμβαίνει ένα συγκεκριμένο συμβάν. Σε αυτήν την περίπτωση, θέλουμε να εκτελείται η μακροεντολή κάθε φορά που αλλάζετε ένα κελί. Για να ρυθμίσετε ένα πρόγραμμα χειρισμού συμβάντων, ακολουθήστε τα εξής βήματα:

• Ένας χειριστής συμβάντων συσχετίζεται με ένα μόνο φύλλο εργασίας. Ξεκινήστε από αυτό το φύλλο εργασίας και πατήστε alt-F11 για να ανοίξετε το πρόγραμμα επεξεργασίας VB.
• Στο επάνω αριστερό παράθυρο (Project - VBA Project) κάντε διπλό κλικ στο όνομα του φύλλου εργασίας σας.
• Στο δεξιό τμήμα του παραθύρου, κάντε κλικ στο αριστερό αναπτυσσόμενο μενού και αλλάξτε το γενικό σε φύλλο εργασίας.
• Στο δεξί αναπτυσσόμενο μενού, επιλέξτε Αλλαγή.

Αυτό θα κάνει το Excel να εισαγάγει το ακόλουθο κέλυφος μακροεντολών για εσάς:

Private Sub Worksheet_Change(ByVal Target As Range) UserInput = Target.Value If UserInput> 1 Then NewInput = Left(UserInput, Len(UserInput) - 2) & ":" & Right(UserInput, 2) Application.EnableEvents = False Target = NewInput Application.EnableEvents = True End If End Sub

Κάθε φορά που αλλάζει ένα κελί, το κελί που άλλαξε μεταβιβάζεται σε αυτό το πρόγραμμα στη μεταβλητή που ονομάζεται "Στόχος". Όταν κάποιος εισαγάγει μια ώρα με άνω και κάτω τελεία στο φύλλο εργασίας, θα αξιολογηθεί σε αριθμό μικρότερο από ένα. Το μπλοκ If φροντίζει να αλλάζει κελιά μόνο εάν είναι μεγαλύτερα από ένα. Χρησιμοποιώ τις λειτουργίες αριστερά () και δεξιά () για να σπάσω την είσοδο του χρήστη σε ώρες και λεπτά και να εισαγάγω ένα άνω και κάτω τελεία.

Κάθε φορά που ο χρήστης μπαίνει στο "2345", το πρόγραμμα αλλάζει αυτήν την καταχώρηση σε 23:45.

Πιθανές βελτιώσεις

Εάν θέλετε να περιορίσετε το πρόγραμμα να λειτουργεί μόνο στις στήλες A & B, μπορείτε να ελέγξετε την τιμή του Target.Column και να εκτελέσετε το μπλοκ κώδικα μόνο εάν βρίσκεστε στις δύο πρώτες στήλες:

Private Sub Worksheet_Change(ByVal Target As Range) ThisColumn = Target.Column If ThisColumn 1 Then NewInput = Left(UserInput, Len(UserInput) - 2) & ":" & Right(UserInput, 2) Application.EnableEvents = False Target = NewInput Application.EnableEvents = True End If End If End Sub

Εάν θέλετε να κάνετε αλλαγές στο φύλλο εργασίας χωρίς να εισαγάγετε άνω και κάτω τελεία (για παράδειγμα, πρέπει να προσθέσετε τύπους ή να αλλάξετε επικεφαλίδες κ.λπ.) μπορείτε να γυρίσετε τον χειριστή συμβάντων με αυτήν τη σύντομη μακροεντολή

Sub TurnEventHanderOff() Application.EnableEvents = False End Sub You can turn event handlers back on with this macro: Sub TurnEventHanderOff() Application.EnableEvents = True End Sub

Εάν λάβετε αυτήν την ιδέα και την αλλάξετε, υπάρχει μια σημαντική ιδέα που πρέπει να γνωρίζετε. Όταν η μακροεντολή χειριστή συμβάντων εκχωρεί μια νέα τιμή στο κελί που αναφέρεται από το Target, το Excel το μετρά ως αλλαγή φύλλου εργασίας. Εάν δεν γυρίσετε για λίγο τους χειριστές συμβάντων, τότε το Excel θα ξεκινήσει αναδρομικά να καλεί τον διαχειριστή συμβάντων και θα λάβετε απροσδόκητα αποτελέσματα. Πριν κάνετε μια αλλαγή σε ένα φύλλο εργασίας σε έναν χειρισμό συμβάντων αλλαγής, φροντίστε να αναστείλετε προσωρινά τη διαχείριση συμβάντων με τη γραμμή Application.EnableEvents