#042 Мързеливо въвеждане на дроби

Беше ми зададен въпрос как при въвеждане потребителя да не се налага да въвежда дробен знак. Т.е. автоматично когато въвежда цели числа те да се преобразуват в дробни. Примерно с 4 знака след десетичната точка. Примерно при въвеждане на 10 да се появи 0.0010 при въвеждане на 12345 -> 1.2345 при 123456789  -> 12345.6789 и т.н.

Вариант 1: Без "фокуси" :):) Excel си има такава настройка. Влизате в менюто File/Options (или Офис бутон/Options за Excel 2007) и там в секцията Advanced намирате това което ви трябва:

Автоматично преобразуване на цели числа в дробни

За да ви подскаже, че сте включили този режим, Excel  ви показва в статус лентата "Fixed Decimal" (т.е. ако някой иска да се бъзика с вас ако видите този надпис да знаете от къде е активиран:))))

Надпис на статус лентата

И готово:)

Плюсове: Няма код, няма допълнителна колонка.

Минуси: Проблемът е, че тази настройка е валидна за Excel а не за конкретната работна книга! Т.е. ако изпратите вашата работна книга на ваш познат при който тази настройка не е включена "магията" ще изчезне:( Друго досадно нещо е, че тези настройки важат за всички цели числа които въвеждате (независимо от работна книга, работен лист или зона от клетки).... Т.е. ако искате наистина да въведете 123 трябва да напишете 123. (точка след 123!) . При дробните числа няма проблем. Ако вие въведете 123.4 това число ще бъде въведено в клетката без промяна! Върши работа но е прекалено глобално като действие и не е обвързано с конкретна работна книга.

Вариант 2: "Бабешки" начин:) Въвеждаме помощна колонка:) Примерно ако потребителя въвежда данните в колонка А правим колонка B съдържаща следната формула:

  =A1/10^4  (В интерес на истината варианта =A1/10000 ще е по-бърз, но нали трябва да ви припомням и странични неща. Примерно, че ^ е степенуване ):):)

Плюсове: Локално за работна книга. Което означава, че когато я изпратите на ваш познат ще си работи и при него. Няма код.

Минус: Ми от всякъде погледнато тази допълнителна колонка е грозна. Т.е. не тя ами колонката в която потребителя въвежда данните. Това може да се избегне по два  налична. Или колонката B е скрита (или изобщо е няма и във всяка формула съдържанието на A се дели на 10000). Или колонка А се слага на лист примерно "Въвеждане", а на друг лист се вижда в целия блясък коригираните стойности (т.е. "работната" колонка и останалите колонки които са производни от нея")!

Вариант 3: С "фокус" (разбирайте код):):) Натискате десен бутон на мишката върху името на листа изпълнявате командата View Code и в редактора слагате следния код:

' ------------- Начало на кода -------

Private Sub Worksheet_Change(ByVal Target As Range)
Const div = 10000
If Target.Count > 1 Then Exit Sub
If Not (Intersect(Target, Range("A1:A22")) Is Nothing) Then
   If IsNumeric(Target.Value) Then
   Application.Calculation = xlCalculationManual
   Application.EnableEvents = False
   Target.Value = Target.Value / div
   pplication.Calculation = xlCalculationAutomatic
   Application.EnableEvents = True
   End If
End If
End Sub
' ----- Край на кода ------

В различен цвят съм дал нещата които подлежат на настройка. Както виждате съм изнесъл като константа 10000 за да може лесно да пипате и променяте числото на което делите. Променяте и зоната в зависимост от вашите нужди и сте готови!:) Сега при промяна на клетките от посочената от вас зона ще действа "магията" за останалите клетки и работни листи няма да действа. За да е по-разбираем примера е направен само за промяна на единични клетки в посочената зона! За повече информация вижте другите теми за Target  (#037 и #036)!

Важна особеност е блокирането на събитията при промяна на стойността на клетката от самия макрос (target.value=.........). Защото може да изпаднете във вечен цикъл (в интерес на истината просто Excel при мен "гръмна" когато бях забравил този момент:):) За това е важна двойката оператори:

Application.EnableEvents = False и Application.EnableEvents = True "заграждащи" самото действие. Така ние временно "приспиваме" Excel да не отчита промяната на стойността на клетката от макроса!

Плюсове: Пълен контрол. Свързано е с работната книга и конкретни клетки от нея.  Книгата ще си "носи" със себе си тази функционалност. Няма излишни колонки. Промяна на данните в мястото на въвеждане.

Минус: Наличието на код. Т.е. потребителя трябва да разреши макросите. Което си е досадно. Ако трябва да се ползват новите формати на офис 2007/20010 книгата трябва да бъде записана в XLSM а не XLSX!

Успех:)

#37 Параметър Target

Както вече обещах ето ви малко детайли за параметъра Target. При събитията свързани с даден работен лист (промяна на данни, избор на клетки, натискане на десен бутон и т.н.) към подпрограмата се изпраща параметър Target който съдържа клетките/клетките които се променят, избират и т.н. Ето някой трикове за работа с този параметър.  N.B! Примерите в тази глава се въвеждат в частта за код на съответния лист (десен бутон върху името на листа и View Code).

1. Проверка за броя на клетките. Най-лесно е просто когато параметъра съдържа повече от една клетка да се излезе от програмата. За целта в началото на подпрограмата се поставя следния код : If target.count>1 then exit sub. Това върши работа в голяма част от случаите,  но когато искаме да работим с повече от една клетка трябва да се направи цикъл от вида:

Dim c As Range
 For Each c In Target
   Код за работа със променливата C
 Next 

Пример 1: Да се направи програма за оцветяване на променяните клетки в зелено и на изтритите клетки в жълто. За целта се програмира събитието за промяна на клетка.

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim c As Range
For Each c In Target 'За всяка клетка в зоната
 If IsEmpty(c.Value) Then 'Ако клетката е празна
   c.Interior.Color = RGB(255, 255, 0) 'жълт цвят = червено + зелено
 Else
   c.Interior.Color = RGB(0, 255, 0) ' Зелен
 End If
 Next

End Sub

2.Действия само върху избрани клетки. Много често се налага дадено действие да се извършва само ако става дума за определени клетки от работния лист. Има различни начини за проверка дали зоната target (или клетките от нея) са подходящи.

Вариант 1: Цвят на клетката. Това е много "бабешки" начин, но не изисква промяната на кода (освен ако не искаме друг цвят). Просто потребителя оцветява нужните клетки в даден цвят в работната книга и само за тези клетки даденото събитие се активира. За целта се използва конструкция:

if Target.Interior.Color = xxx then

end if

Пример 2: При натискане на десен бутон върху жълтите клетки (само върху тях!) в избраната клетка да се въвежда текущата дата. В случая се програмира събитието което настъпва при натискане на десен бутон на мишката:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
If Target.Count > 1 Then Exit Sub ' Изход ако са повече от една клетка
If Target.Interior.Color = RGB(255, 255, 0) Then ' Ако цветът на клетката е жълт
  Target.Value = Now() ' Записва датата
  Target.NumberFormat = "dd/mm/yyyy" 'Оформя се клетката като дата
  Cancel = True ' Излиза се от подпрограмата без да се показва стандартното меню за десен бутон
End If
End Sub

Вариант 2:  Трикът със цветовете винаги ми е вършил работа, но има и по "научен" начин чрез използване на функцията Intersect. Тази функция връща пресечната точка (сечението) на няколко зони. Форматът и е Intersect(зона1, зона2....). Ако няма нито една пресечна точка функцията връща Nothing. За нас е важно да НЕ е Nothing. Ето конструкцията която се използва.

If NOT (Intersect(target,range(Зона)) is Nothing) then

end if

В зона се посочва или адреси на конкретни клетки (например Range("а1:а99")) или име на зона (например Range("nomera"). Недостатък на метода е че трябва да се пипа кода (или името на зоната) за да се промени обхватът. Но тук предимството че потребителя не е обвързан с определен цвят.

Пример 3: При избор на клетка от зоната B1:B22 да се изведе предупреждение. За целта се програмира събитието за избор на клетки.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not (Intersect(Target, Range("B1:B22")) Is Nothing) Then
   MsgBox ("Данните са важни!")
End If

#36 Статична дата и/или час

Спокойно можех да кръстя темата "Как да не използваме Now()":)) Проблемът се крие в "умните" функции Now() и Today() които се самообновяват при преизчисляване (натискане на F9 или някой от другите комбинации за преизчисляване). Това е доста досадно и съм виждал много некадърно направени таблици в които датите (например дата на фактура) е с помощта на Today() или Now()!  И в резултат на което получавате дата която се мени постоянно:):) Когато искаме датата да не се променя най-бързия начин за въвеждане е с помощта на комбинациите Ctrl+; (точка и запетая) за въвеждане на текущата дата и Ctrl+Shift+; за въвеждане на текущия час. Така въведените стойности няма да се променят!!

По сложен е въпросът ако искаме датата (или часът) да се въвеждат автоматично при промяна или попълване на дадена клетка. 

Задача:

Да се направи таблица в която автоматично да се попълва дата и час при промяна на дадена клетка:

Автоматична дата

В посочения пример се иска когато потребителя въвежда данни в колонка А автоматично да се въведе текущата дата и час в колонка В и то без да се променя при всяко преизчисляване на листа!

Вариант едно: Явно варианта с формула в клетка В2 =IF(A2="","", now()) е примамлив но неприложим по простата причина, че при всяко преизчисляване ще се променя дата и часа:( !

Да   опитаме да се направим на умни. =IF(A2="","",IF(B2="",NOW(),B2)). Тази формула има лееек дефект:):) Проблемът е, че самата тя зависи от себе си (казано на кратко получава се зацикляне (circular reference!) на формулата. Колкото и да се мъчим (например с помощта на други клетки) винаги формулата се самозацикля:(  Но от това също има изход. Малко груб, но работи. Excel позволява самозациклянето на формули в определени ситуации. Това са така наречени итерационни изчисления. Нашия случай не е точно такъв, но ще "излъжем" Excel че искаме той да не обръща внимание на самозациклените формули. Настройките се извършват от Excel/Options.

Включване на итерациите

Това е. Но по принцип е трик от типа "не правете така в къщи":) Хубавото му е, че няма нужда от макроси и свързани с това промени в сигурността и т.н.

Вариант две: С помощта на макрос. Натискане десен бутон на името на листа и показвате редактора на код чрез View Code. В прозорецът вкарвате следния код (Copy/Paste). В кодът съм сложил доста коментари, но обещавам следваща тема да бъде за параметъра Target и как се използва той.

'Събитие при промяна на клетки
Private Sub Worksheet_Change(ByVal Target As Range)

' Ако се прави групова промяна на клетки се излиза от подпрограмата
' Функцията не работи с промяна/изтриване на повече от една клетка!

If Target.Count > 1 Then Exit Sub

' Ако променяната клетка е в зоната A2:A999

If Not (Intersect(Target.Cells, Range("A2:A999")) Is Nothing) Then
   'Ако НЕ изтриваме клетката
   If Not IsEmpty(Target.Value) Then
   ' Ако съседната клетка все още е празна
   If Target.Offset(0, 1) = "" Then
      ' В съседната и клетка записваме текущата дата
      Target.Offset(0, 1) = Now()
      ' Оформяме съседната клетка като дата и час
      Target.Offset(0, 1).NumberFormat = "dd.mm.yyyy HH:MM"
    End If
    Else ' Ако изтриваме клетката
    ' Премахваме съдържанието и на съседната клетка
      Target.Offset(0, 1) = ""
    End If
End If
End Sub

Както казах втория вариант има недостатък, че трябва да е книга от тип XLSM , но има по-големи възможности и не се пипат настройките за калкулиране. Както споменах, ще направя няколко теми да разясня нещата в този код в детайли. Успех и внимателно със датите:)