[VBA] Formula To Comment

[jockey17]

Καλημέρα στην παρέα του φόρουμ.

Τον παρακάτω κώδικα για μεταφορά των τύπων από το κελί στο σχόλιο του κελιού (και αντίστοιχα ο επόμενος κώδικας για μεταφορά των τύπων από το σχόλιο στο κελί) τον εφαρμόζω σε ένα φύλλο εργασίας όπου το UsedRange είναι περίπου 70 στήλες και 1700 γραμμές. Από τις 70 στήλες οι μισές περιέχουν τύπους όπου με Match / Index γεμίζουν με στοιχεία από δύο – τρία βοηθητικά φύλλα. Ο χρόνος εκτέλεσης είναι γύρω στα 7 λεπτά. Υπάρχει τρόπος με διαφορετική σύνταξη του κώδικα να βελτιωθεί ο χρόνος εκτέλεσης;

Sub FormulaToComment()
Dim c As Range
Dim WorkRng As Range
Set WorkRng = WS_OS.UsedRange

With Application
.Calculation = xlCalculationManual
.Cursor = xlWait
.ScreenUpdating = False
.DisplayStatusBar = False
.EnableEvents = False
End With

For Each c In WorkRng
If c.HasFormula = True Then
c.AddComment c.Formula
c.Value = c.Value
Else
End If
Next

With Application
.Calculation = xlCalculationAutomatic
.Cursor = xlDefault
.ScreenUpdating = True
.DisplayStatusBar = True
.EnableEvents = True
End With

End Sub
'--------------------------------------------------------------------------------------------
Sub CommentToFormula()
Dim c As Range
Dim WorkRng As Range
Dim cComment As String
Set WorkRng = WS_OS.UsedRange

With Application
.Calculation = xlCalculationManual
.Cursor = xlWait
.ScreenUpdating = False
.DisplayStatusBar = False
.EnableEvents = False
End With

For Each c In WorkRng
If Not c.Comment Is Nothing Then
cComment = c.Comment.Text
c.Formula = cComment
c.ClearComments
Else
End If
Next

With Application
.Calculation = xlCalculationAutomatic
.Cursor = xlDefault
.ScreenUpdating = True
.DisplayStatusBar = True
.EnableEvents = True
End With

End Sub

Φιλικά
Δημήτρης

[Spirosgr]

Καλησπέρα
Στην Excel 2013 υπάρχει η =FORMULATEXT(a1) ενσωματωμένη που αποδίδει τύπο σε μορφή text
Μήπως θα ήταν πιο εξυπηρετικό αυτό από comments, VBA κλπ;

[jockey17]

Καλησπέρα Σπύρο και ευχαριστώ για την άμεση απάντηση.
Το συγκεκριμένο τέχνασμα (μεταφορά τύπου σε σχόλιο και αντίστροφα) το κάνω σαν πειραματισμό στο προαναφερθέν αρχείο μήπως και μπορέσω να βελτιώσω τις επιδόσεις τόσο κατά το άνοιγμα του αρχείου όσο και κατά την επεξεργασία του. Λόγω του πλήθους των συναρτήσεων είναι ευνόητο ότι το αρχείο αυτό (περίπου στα 7ΜΒ) αργεί τόσο στο να ανοίξει όσο και κατά την επεξεργασία όπως π.χ. εισαγωγή νέας γραμμής. Επειδή ακόμα και με τη ρύθμιση του «Μη αυτόματου υπολογισμού» υπάρχει ένα μικρό κόλλημα κατά την εισαγωγή γραμμών, σκέφτηκα πως εάν «παγώσω» τους τύπους μεταφέροντας τους στα σχόλια θα γίνεται πιο γρήγορα η επεξεργασία του αρχείου (που όντως και γίνεται) και όταν θα πρέπει να υπολογίσει τότε να τους μεταφέρω ξανά στο κελί κ.ο.κ., αλλά τελικά διαπιστώνω ότι αυτό το σκεπτικό μου δεν μπορεί να περπατήσει όχι μόνο λόγω του μεγάλου χρόνου μεταφοράς τύπων σε σχόλια και το αντίστροφο, αλλά και επειδή εάν εισαχθεί μία γραμμή (όσο οι τύποι είναι στα σχόλια) έστω με αντιγραφή της ακριβώς από επάνω γραμμής τότε ο τύπος της νέας γραμμής όταν θα μεταφερθεί στο κελί θα έχει λάθος αναφορά. Θα κοιτάζει το κωδικό του item της προηγούμενης γραμμής για να φέρει περιγραφές και όλα τα υπόλοιπα των υπολογιζόμενων στηλών.
Οπότε και με τη συνάρτηση FORMULATEXT δεν νομίζω ότι μπορώ να επιτύχω κάτι για τον παραπάνω στόχο. Παρεμπιπτόντως δεν γνώριζα ότι υπήρχε η συγκεκριμένη συνάρτηση.
Ευχαριστώ πάντως για την ανταπόκριση. Δεν ξέρω εάν έχεις κάτι να προτείνεις στην κατεύθυνση μεγάλα αρχεία, βελτιστοποίηση ταχύτητας κλπ.

Φιλικά
Δημήτρης

[Spirosgr]

Για να καταλάβω Δημήτρη το σκεπτικό ...
Κάθε φορά που ανοίγει το αρχείο ή σε κάθε υπολογισμό (λόγω όγκου όπως λες)
«αδειάζεις» τα κελιά με τύπους μεταφέροντάς τα σε σχόλια και
μετά τα «ξαναφορτώνεις» από τα σχόλια στα κελιά;

[jockey17]

Ναι περίπου έτσι σκεπτόμουν να το εφαρμόσω.
Όταν το ανοίγω το αρχείο οι τύποι βρίσκονται στα σχόλια από την τελευταία επεξεργασία του αρχείου. Το ανοίγω λοιπόν όπου παντού έχω ουσιαστικά μόνο κείμενο. Το επεξεργάζομαι όπως π.χ. σε κάποια κελιά που είναι manual καταχωρίσεις κάνω τις καταχωρίσεις μου, εάν χρειαστεί να προσθέσω νέα γραμμή την προσθέτω τότε βάζω τον κώδικα να μεταφέρει τους τύπους από τα σχόλια στα κελιά …..κάνει τους υπολογισμούς του και πριν κλείσω το αρχείο ξανά μεταφέρω τους τύπους στα σχόλια και αποθηκεύω.
Αυτό τουλάχιστον ήταν στη σκέψη μου να πραγματώσω, αλλά διαπιστώνω ότι δεν μπορεί να περπατήσει έστω και εάν η μεταφορά των τύπων στα σχόλια και αντίστροφα γινόταν σε χρόνο dt.
Επίσης για να γίνω περισσότερο κατανοητός φαντάσου σε ένα φύλλο να έχεις λίστα μιας ολόκληρης γκάμας προϊόντων ομαδοποιημένα κατά ομάδες ομοιογενών προϊόντων η οποία εκτείνεται σε 70 στήλες και 1700 γραμμές.
Οι μισές στήλες είναι στοιχεία που έρχονται από την επίσημη βάση δεδομένων η οποία δυστυχώς δεν καλύπτει όλες τις απαραίτητες πληροφορίες και γι' αυτό κάποιες συντηρούνται στο Excel.

Ομάδα Α (π.χ. κονσέρβες)
Κωδικός / Περιγραφή / Κωδ. Intrastat / Βάρος κιβωτίου / Διαστάσεις κιβ. / Κιβ. Ανά παλέτα
Manual / Formula / Formula / Formula / Manual / Formula

Ομάδα B (π.χ. κρασιά)
Κωδικός / Περιγραφή / Κωδ. Intrastat / Βάρος κιβωτίου / Διαστάσεις κιβ. / Κιβ. Ανά παλέτα
Manual / Formula / Formula / Formula / Manual / Formula

[Spirosgr]

Ναι κατάλαβα
Λύση θα ήταν νέο πρόγραμμα, μηδέν τύποι, μόνο VBA τα πάντα «έρχονται» ως τιμές και μόνο.

[jockey17]

Σπύρο έχεις απόλυτο δίκιο! Η λύση είναι να έρχονται τα πάντα ως τιμές και μόνο.
Σε ευχαριστώ για την αντοπόκριση.
Να είσαι καλά.

Φιλικά
Δημήτρης