#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!

Успех:)

#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 , но има по-големи възможности и не се пипат настройките за калкулиране. Както споменах, ще направя няколко теми да разясня нещата в този код в детайли. Успех и внимателно със датите:)

#035 Заключване на клетки

Беше ми зададена следната задача. В таблица след като се пипне някоя клетка да се заключи. Т.е. нещо като правилото в шаха "пипнато-местено":) Разбира се при необходимост да може да се сваля защитата, но за обикновения потребител попълващ таблицата да не е възможно да се връща след като въвел нещо. Ето как се реализира това:

1. Избират се клетките в които по начало е разрешено въвеждането и се "отключват". С командата Format (Лента Home)/Lock Cell (да се махне "катинарчето"). Или "класическия начин" чрез Format Cells (Ctrl+1)/ Protection и махане на Lock отметката.

"Отключване" на клетките за въвеждане

2. Активиране на защитата. Използва се командата Format/Protect Sheet. NB! Добре е да се разреши само избора на разрешените за въвеждане клетки. Не поставяйте парола! (Това ще коментираме малко по-късно!)

Включване на защитата на листа

3. Поставяне на макроса.
3.1. Активизиране на кода на листа. Заставате долу на името на листа/десен бутон/View Code.

Активиране на редактора

3.2 Присвояване на код на събитието на работния лист "Change". За целта в левия списък над редактора се избира "Worksheet" а в десния "Change".

Създаване на подпрограма за обработка на събитие

3.3 Поставяне на кода в създаденото събитието. В отворената процедура напишете следния код:

If Me.ProtectContents Then
  Me.Unprotect
  Target.Locked = True
  Me.Protect
End If

Код към събитието

Готово:)

Таблицата трябва да я запишете като XLSM или като стария съвместим със старите версии XLS формат. Припомням, че XLSX формата не поддържа макроси!

Няколко думи за кода. Събитието Change настъпва когато променяте съдържането на клетка. Кодът проверява дали е активираната защитата на листа. Това позволява при деактивирана защита да не се извършва заключване при промяна! Изключва защитата, защитава променената клетка (параметър Target се предава на събитието и съдържа адресът на променяната клетка) и отново активира защитата. Просто:)

Както беше споменато по начало ние не сложихме парола при активиране на защитата. Ако искаме да има парола кодът трябва да изглежда по следния начин:

If Me.ProtectContents Then
   Me.Unprotect ("123456")
   Target.Locked = True
   Me.Protect ("123456")
End If

Където 123456 е използваната парола! (NB! не разчитайте много на тази парола:) Има прекалено много инструменти за нейното премахване). При промяна на паролата се налага и промяна на кода!

#026 Макроси и функции (основни понятия)

Това е първата тема която се явява преход към макросите и функциите. Ще има няколко такива. От гледна точка на потребителите се разграничават две основни понятия:

1. Макрос. Това е пакет от действия който трябва да се активизира по някакъв начин (ще разгледаме начините за активизиране в отделна тема). Макросът позволява да се извършват действия за промяна съдържанието и оформянето на група клетки. Може да се създават и  изтриват редове, колони и работни листи и т.н. Т.е. може да се прави всичко с компонентите на една работна книга!

2. Потребителска функция. Това е функция която връща резултат в текущата клетка. НЕ МОЖЕ ДА ПРОМЕНЯ СТОЙНОСТТА И ОФОРМЛЕНИЕТО НА ДРУГА КЛЕТКА! Това е много важно да се запомни. Много хора не правят разлика между макрос и потребителска функция.

Проблемът на макросите е че някой/нещо трябва да ги активира, а проблемът при функциите е че връщат стойност само в текущата клетка и не могат да правят нищо друго!

От програмистка гледна точка нещата са леко по-сложни:) Първо за да е кашата пълна в ранните версии на Excel съществуваше специален език за писане на макроси. Това са така наречените XLM (да не се бърка с XML!) макроси. Остатъци от тях може да намерите още. (например ако от контекстното меню което се появява върху името на лист изпълните Insert ще видите три вида работни листи които са свързани с този анахронизъм).

Стария вид макроси

В следващите версии Microsft вградиха в продуктите си езика Visual Basic For Application (ще използвам популярното съкращение VBA) който е много по-мощен и стандартен. Няма да се учудя обаче, ако лека полека вградят .Net езиците (сега има възможност за програмиране, но не е вградено в самия офис пакет). Така че в момента като препоръчителна среда за програмиране е VBA.

Във  VBA понятието Макрос не съществува! Може да обособим четири важни понятия върху които ще се концентрирам в различни теми:

1. Процедура (Sub). Това е аналог на Макроса. В крайна сметка всяка процедура се появява в списъкът с макроси. За това аз ще говоря за процедури в следващите теми и ще забравя понятието макрос! 

2. Процедура-Събитие (Sub). Това е особена процедура, която се активира при настъпване на дадено събитие. Например промяна съдържанието на клетка, натискане на десен бутон на мишката и т.н. При тези процедури в зависимост от събитието се подават различни параметри (зоната която е избрана, клетката която е променена и т.н.).

3. Функция (Function). Функциите за разлика от процедурите освен извършваните от тях действия връщат резултат (стойност) в мястото на тяхното извикване.

4. Функции за работа в работни листи (Function). Това са функции които могат да се използват като останалите функции в работните листи. За да може една функция да стане такава тя трябва да отговаря на определени условия. Най-важното от което е в тялото си да не променя обекти на Еxcel!

Два вида процедури и два вида функции.Това е по-добро разграничаване от понятията Макрос и Потребителска функция в началото!

Начин на съхранение. Във версиите на Excel преди 2007 XLS файловете позволяват съхранение в себе си на процедури и функции. При новите формати за по-лесен контрол на съдържанието се дефинират два основни формата XLSX който НЕ МОЖЕ да съдържа процедури и функции и XLSM който МОЖЕ да съдържа процедури и функции. Това означава, че когато правите примери с програмен код ще се наложи да записвате в XLSM или в стария XLS формат (който не поддържа всички визуални възможности на новите версии на Excel)!

Сигурност. Office 2007 и 2010 имат сложна система за предпазване на потребителите и няма да ни стигне цяла тема. Ако не сте пипали настройките по подразбиране те блокират кода във вашата работна книга, но ви позволяват да го активирате. Просто следете информационното съобщение. Понякога е в отделен прозорец понякога е като лента!

Отделен прозорец за разрешаване на код

Информационна лента

Както обърнах внимание това са настройките по подразбиране за 2007 и 2010, но при Excel 2003 настройката по подразбиране е да бъдат блокирани всички макроси без предупреждение и възможност за активиране! За целта се налага да изпълните командата Tools/Macro/Security и да активирате предупреждаващото съобщение.

Настройки на Excel 2003