Visual2000 · Архив статей А.Колесова & О.Павловой

Советы тем, кто программирует на VB & VBA

Андрей Колесов, Ольга Павлова

• Совет 251. Как копировать ячейки между рабочими книгами
• Совет 252. Как прервать вычислительный процесс
• Совет 253. Как решить проблему с сохранением проектов с цифровой подписью
• Совет 254. Как автоматически определить кодовую таблицу для русских текстов
  • Листинг 254. Процедуры CodeTableTest и NumberTableTest для совета 254
• Совет 255. Как определить кодировку текста: еще один вариант
  • Листинг 255. Код утилиты RusCode2 для Совета 255

Кстати, появление этого цикла статей именно в КомпьютерПресс было совсем не случайным: еще за четыре года до этого, в 1991 году, также в мартовском номере журнала появилась статья "QuickBASIC — это то, что вам нужно", которая стала нашей первой публикацией в только зарождавшейся тогда отечественной компьютерной прессе.

Совет 251. Как копировать ячейки между рабочими книгами

Наш читатель прислал письмо, в котором рассказывает о следующей проблеме. Необходимо программным образом скопировать содержимое ячеек из таблицы одной рабочей книги в таблицы другой. Для этого он написал такую макрокоманду в рабочей книге Macros.xls:

Sub Макро1()
    Workbooks.Open Filename:="Source.xls" ' открываем книгу Source.xls
    Cells.Select       ' выделяем все ячейки в активной таблице
    Selection.Copy                        ' копируем в буфер обмена
    ActiveWindow.Close                    ' закрываем активную таблицу
    ' теперь активной стала текущая таблица книги Macros.xls
    Range("A1").Select                    ' начальная позиция для вставки
    ActiveSheet.Paste                     ' вставка из буфера обмена
End Sub

Однако результат выполнения копирования из таблицы книги Source.xls с помощью этой макрокоманды отличался от ожидаемого как в среде Excel 97, так и в Excel 2000 (с помощью команд в среде пакета эта операция выполняется верно):

	Содержимое ячеек
Исходное содержимое в книге Source	12,12	5,559	5,7777	5.559
Результат после выполнения Макро1 (Excel 97)	12,12	5 559	57 777	5,559
Результат после выполнения Макро1 (Excel 2000)	12,12	5,559	5,7777	5,559

Из этих данных видно, что ошибка копирования каким-то образом связана с неверным использованием региональных установок (хотя работа велась в среде русскоязычных Windows и Office) — очевидна путаница русских и английских установок в Excel 97.

Так 12,12 с точки зрения RegionalSetting = 1033 (США) является просто символьной строкой. И в данном случае она копируется как символьная строка (обратите внимание, что после выполнения Макро1 в Excel 97 ячейка стала выровнена по левому краю). А 5,559 представляет (для установок США) целое число с точкой в качестве разделителя триад. При вставке же этого числа используется русский разделитель триад - пробел. Нечто аналогичное, но плохо поддающееся логическому объяснению, происходит с числом 5,7777. Содержимое же четвертой ячейки — 5.559 — в Source.xls является символьной строкой, выровненной по левому краю. Но для установок США это число с десятичной точкой, которая в русской языке меняется на запятую (и, соответственно, выравнивается по правому краю). Эта же ошибка имеет место в Excel 2000.

Механизм ошибки понятен, но что же делать? Как копировать ячейки?

По нашей просьбе служба технической поддержки в России занялась этой проблемой и после запроса в европейский центр получила такой ответ:

"Переменная копируется как текстовая строка в буфер обмена, после чего исходная книга закрывается и выполняется вставка во вторую книгу. Excel старается преобразовать ее в вид по своему усмотрению и делает это неверно.

Но все будет работать нормально, если держать исходную рабочую книгу открытой до окончания вставки данных".

И действительно, все работает без ошибок, если использовать для копирования такой вариант макроса:

Sub Макро2()
    Workbooks.Open FileName:="c:\Source.xls"
    Cells.Select
    Selection.Copy
    ' делаем активной книгу Macros.xls
    Workbooks("Macros.xls").Activate
    Range("A1").Select
    ActiveSheet.Paste ' вставка
    ' только теперь закрываем исходную книгу
    Workbooks("Source.xls").Close
End Sub

Еще один совет из службы поддержки Microsoft: без особой нужды не следует копировать таблицу целиком — используйте только тот диапазон, который вам действительно нужен. То есть вместо

Cells.Select

в данном случае лучше написать:

Range("A1:E2").Select

При тестировании прилагаемых программных примеров необходимо файл Source.xls скопировать в каталог C:\ или указать другой путь к файлу в коде макросов.

В начало статьи

Совет 252. Как прервать вычислительный процесс

Если вы создаете программы с достаточно длительными вычислительными процедурами, то бывает очень полезным обеспечить возможность аварийного прерывания этих процессов. Например, щелкнуть мышью на кнопке в форме. Рассмотрим один из вариантов решения этой задачи.

Предположим, у вас в модуле Process.bas есть некая вычислительная процедура. Идея реализации механизма ее прерывания заключается в использовании некоторого флага (глобальная переменная), состояние которого отслеживается внутри процедуры и который может изменяться другой внешней процедурой.

В этом случае модуль Process.bas может выглядеть приблизительно так:

Global ProcessFlag% ' флаг управления процессом, глобальная переменная

Public Sub Process(Result%)
    ' имитация некоего процесса
    ' Result возвращает флаг окончания процесса
    ' 1 - закончился сам, 0 - внешнее аварийное завершение
    '
    ProcessFlag = 1 ' флаг начала процесса
    For i& = 1 To 1000000
        ' увеличить длину цикла, если слишком малое время задержки
        If ProcessFlag = 0 Then Exit For 'проверка флага
        Value# = 100 / 0.3 * 1.5 / 2.3
    Next
    Result = ProcessFlag
End Sub

Теперь создадим форму frmInterrupt, на которой поместим кнопку с названием "Щелкни здесь, чтобы прервать процесс Process". Для кнопки напишем такую процедуру:

Private Sub Command1_Click()
    ProcessFlag = 0 ' очистка глобального флага
End Sub

Далее нужно написать главную процедуру нашего проекта в отдельном модуле MainSub.bas:

Public Sub Main()
    ' процедура для демонстрации механизма прерывания
    ' некоего вычислительного процесса
    frmInterrupt.Show 0   ' открываем форму в немодальном режиме
    Call Process(Result%) ' запускаем форму
    Unload frmInterrupt   ' выгружаем форму
    ' анализ кода завершения процедуры
    MsgBox "Результат завершения процедуры = " & Result%
End Sub

Внешне все выглядит правильно, но, запустив проект на выполнение, мы обнаружим, что произвести прерывание процесса не удается. Более того, даже форма не прорисовывается до конца. Подобный вопрос мы обсуждали месяц назад в советах 246-247: дело в том, что вычислительный цикл съедает все ресурсы компьютера, не давая выполняться другим процессам приложения. Для решения этой проблемы необходимо внутрь вычислительного цикла вставить функцию DoEvents, которая передает управление операционной системе для выполнения других процессов.

Однако вариант

For i = 1 To 1000000
    DoEvents ' передача управления другим процессам
    If ProcessFlag = 0 Then Exit For 'проверка флага
    Value# = 100 / 0.3 * 1.5 / 2.3
Next

также не очень хорош - выполнение DoEvents требует очень много времени. В нашем простом вычислительном примере мы увидим, что быстродействие процедуры Process упадет в 1000 раз (то есть время выполнения DoEvents в 1000 раз больше, чем полезное вычисление по формуле). Чтобы избежать таких непроизводительных затрат, можно написать следующий код:

For i = 1 To 1000000
    If i Mod 100000 Then   ' проверка один раз на 100 000 циклов
        DoEvents           ' передача управления другим процессам
        If ProcessFlag = 0 Then Exit For ' проверка флага
    End If
    Value# = 100 / 0.3 * 1.5 / 2.3
Next

В этом случае DoEvents будет занимать лишь 1% от полезных вычислений. К сожалению, такой вариант решения задачи отслеживания двух параллельных процессов представляется не самым лучшим, но таковы уж реалии Windows.

Короче говоря, совет такой: для обеспечения прерывания вычислительных задач по ходу программы расставляйте (но не очень часто) подобные конструкции:

  DoEvents
  If ProcessFlag = 0 Then Exit something

В начало статьи

Совет 253. Как решить проблему с сохранением проектов с цифровой подписью

В ряде случаев в Office 2000 возникает ситуация, когда пользователь не может сохранить проект (документ с макрокодом) с электронной подписью - запись документа возможна только без подписи. При этом выдается неверная диагностика о нехватке места на диске.

В данном случае имеет место программная ошибка, которая, как мы надеемся, будет исправлена. На самом же деле она проявляется только в тех проектах, которые содержат внутренние синтаксические ошибки в коде. Поэтому, встретив подобное сообщение о невозможности сохранения документа с электронной подписью, мы рекомендуем вам, прежде чем отменять подпись, проверить работоспособность вашего кода. Довольно часто ошибка связана с отсутствием описания переменной или ссылки на внешний объект. Чтобы лучше понять суть ситуации, сделайте такой простой пример в Word. Создайте новый документ и перейдите в среду VBA. Там создайте макрокоманду Test1:

Sub Test1 ()
    Avar = 1
End If

Разумеется, сначала должен быть задан режим Option Explicit (обязательное объявление переменных).

Теперь установите электронную подпись и попробуйте сохранить документ. Скорее всего, у вас появится сообщение о нехватке места на диске для записи файла.

Запустите макрокоманду Test1 на выполнение - транслятор выдаст сообщение о синтаксической ошибке (не определена переменная Avar). Добавьте в процедуру описание:

Dim Avar As Integer

Теперь макрокоманда станет выполняться (правда, не делая ничего полезного), и документ тоже без проблем сохранится с электронной подписью.

В начало статьи

Совет 254. Как автоматически определить кодовую таблицу для русских текстов

Проблема известна: для русского языка применяется несколько различных кодовых таблиц, и поэтому актуальной задачей является преобразование текстов из одной кодировки в другую. Для чего нужно автоматически определять используемую кодовую таблицу? Тут есть два очевидных примера:

1. Это необходимо при загрузке Web-страниц в браузер, текстовых файлов — в Word или при работе с почтовыми программами (гораздо лучше поступать так, чем заниматься перебором разных вариантов кодировок).

2. Это нужно для дополнительного контроля при преобразовании кодов файлов. Например, мы постоянно осуществляем преобразование HTML-страниц из Windows в KOI8 (наш Web-сервер работает под UNIX), но при этом порой из-за невнимательности либо делаем двойную перекодировку, либо неправильно устанавливаем исходный код, либо вообще пропускаем файлы.

Идея автоматического определения кодировки русских текстов достаточно очевидна: необходимо определить частоту попадания кодов 128-255 (&h80-&hFF) в различные числовые диапазоны. Понятно, что основное количество этих кодов приходится именно на русские буквы (в старшей половине кодовой таблицы находится также ряд специальных символов типа "№", открывающих-закрывающих кавычек, Copyright и др.), причем строчных букв гораздо больше, чем прописных.

В таблице 254 приведены результаты подсчета такой статистики для довольно типичного русскоязычного текста, которые получены с помощью подпрограммы CodeTableTest (листинг 254).

Таблица 254. Анализ частоты распределения кодов 128-255 для типичного русскоязычного текста для разлиных кодовых таблиц

Диапазон кодов (шестнабцатиричная система)	Процент попадания кода в соответствующий диапазон
	Windows, cp1251	KOI8-R	DOS, cp866 (альтернативная)	ISO8859-5 (основная)	Macintosh
80-8F	0,00	0,00	1,24	0,00	1,24
90-9F	0,20	0,20	0,63	0,20	0,43
A0-AF	0,01	0,01	67,96	0,01	0,01
B0-BF	0,01	0,01	0,01	1,24	0,01
C0-CF	1,24	61,3	0,00	0,43	0,00
D0-DF	0,43	36,8	0,00	67,96	2,28
E0-EF	67,9	0,80	30,16	30,16	67,9
F0-FF	30,2	0,8	0,01	0,01	28,08

Тут хорошо видно, что строчные буквы попадают в разные числовые диапазоны для разных кодовых таблиц. Любопытно также отметить, что частота появления букв первой половины русского алфавита (от "а" до "п") в два раза выше, чем частота букв от "р" до "я". Соответственно, если принять за условие, что процент строчных русских букв среди кодов 128-255 превышает заданную величину, например, Lpercent = 0,70, то критерий определения кодовой таблицы будет выглядеть таким образом:

Windows	частота (&hE0-&hFF) > Lpercent
KOI8-R	частота (&hC0-&hDF) > Lpercent
DOS	частота (&hA0-&hAF + &hE0-&EF) > Lpercent
ISO	частота (&hD0-&hEF) > Lpercent

Единственная проблема здесь заключается в идентификации различных кодировок Windows и Macintosh, у которых диапазоны кодов русских строчных букв почти совпадают. Но тут можно осуществить дополнительную проверку, которая основана на том, что в Windows практически не используются коды в диапазоне &h80-&h8F, а прописные буквы от "А" до "П" (&hC0-&CF) составляют значительную величину, тогда как в Macintosh ситуация для этих же диапазонов диаметрально противоположная.

Функция NumberTableTest, реализующая описанный выше алгоритм, приведена в листинге 254.

Обратите внимание еще на один важный аспект — для хранения кодов символов мы используем не строковую переменную, а байтовый массив. Соответственно, если нужно прочитать исходный файл, необходимо применять следующую конструкцию:

Open SourceFile$ For Binary As #1
    LenS = LOF(1)
    ReDim bytSourceArray(1 To LenS) As Byte
    Get #1, , bytSourceArray
Close #1

Если же вам нужно преобразовать данные, изначально представленные в виде строковой переменной, то следует выполнить такое преобразование: BytSourceArray() = StrConv (SourceString$, vbFromUniCode, &h419)

В начало статьи

Листинг 254. Процедуры CodeTableTest и NumberTableTest для совета 254

Public Sub CodeTableTest _
    (bytSourceArray() As Byte, TableTest() As Single)
    '
    ' Вычисление частоты различных кодов
    ' (для старшей части таблицы 128-255)
    '
    ' ВХОД: bytSourceArray() - байтовый массив кодов символов
    ' ВЫХОД: TableTest(-1) - общее число символов
    ' (без учета кодов перевода строки)
    ' TableTest(0) - общее число кодов 128-255
    ' TableTest(1) - частота в диапазоне &H80-&H8F
    ' ...
    ' TableTest(8) - частота в диапазоне &HF0-&HFF
    '===================================
    Dim i%, k&, Code%
    For i = 0 To 8: TableTest(i) = 0: Next
    For k = LBound(bytSourceArray) To UBound(bytSourceArray)
        Code = bytSourceArray(k)
        If Code <> 10 And Code <> 13 Then
            TableTest(-1) = TableTest(-1) + 1
        End If
        If Code > 127 Then
            TableTest(0) = TableTest(0) + 1
            i = (Code - 128) \ 16 + 1
            TableTest(i) = TableTest(i) + 1
        End If
    Next
    For i = 1 To 8:
        TableTest(i) = TableTest(i) / TableTest(0)
    Next
End Sub

Public Function NumberTableTest _
    (TableTest() As Single, Lpercent As Single) As Integer
    '
    ' Определение номера кодовой таблицы
    ' ВХОД: TableTest () - см. описание процедуры CodeTableTest
    ' Lpercent - минимальная доля строчных русских букв
    ' ВЫХОД: NumberTableTest = 0 - неопределено
    ' = 1 - DOS (cp866)
    ' = 2 - Windows (cp1251)
    ' = 3 - UNIX (KOI-8)
    ' = 4 - General(ISO 8859-5)
    ' = 5 - Macintosh

    '===================================
    NumberTableTest = 0 ' неопределена

    If TableTest(7) + TableTest(8) > Lpercent Then
        If TableTest(1) > TableTest(5) Then
            NumberTableTest = 5 ' Macintosh
        Else
            NumberTableTest = 2 ' Windows, cp1251
        End If
    ElseIf TableTest(5) + TableTest(6) > Lpercent Then
        NumberTableTest = 3 ' KOI8 (Unix)
    ElseIf TableTest(7) + TableTest(3) > Lpercent Then
        NumberTableTest = 1 ' DOS (cp866)
    ElseIf TableTest(6) + TableTest(7) > Lpercent Then
        NumberTableTest = 4 ' ISO 8859-5/General
    End If
End Function

В начало статьи

Совет 255. Как определить кодировку текста: еще один вариант

Алгоритм, приведенный в предыдущем совете, не работает в случае неверной перекодировки текста, то есть когда исходные данные уже не соответствуют ни одной из кодовых таблиц. (Когда, например, текст, записанный в Windows, ошибочно преобразуется "из DOS в любой другой".) В таких случаях восстановление текста порой бывает вообще невозможно, но в любом случае подобную ситуацию полезно идентифицировать.

С проблемой перекодировки мы чаще всего сталкиваемся при создании собственных HTML-страниц, предназначенных для размещения на Web-сервере. Для контроля реальной кодовой таблицы в данном файле можно использовать следующий прием. (Мы сами выполняем сканирование обновлений локального варианта нашего сервера перед их записью на удаленный компьютер в целях обнаружения подобных ошибок.)

В каждую новую HTML-страницу вставляется строка комментария, в которую включены все буквы русского алфавита, кроме "ё" (такая строка автоматически создается нашим простеньким генератором страниц):

<!CodePage=А...Яа...я>

Конечно, можно минимизировать число используемых символов, но полный их набор точно гарантирует нужное решение.

Программный код утилиты TstCode2, которая проверяет код отдельного файла, приведен в листинге 255. Ключевой процедурой здесь является NumberTableTestKey, выполняющая идентификацию символьного кода. В свой реальной работе для проверки кодовой таблицы текстовых и HTML-файлов мы пользуемся утилитой TestCode (рис. 255), которая применяет комбинацию двух описанных выше методов.

Рис. 255

В начало статьи

Листинг 255. Код утилиты RusCode2 для Совета 255

Public Sub Main()
    ' Стартовая процедура
    ' для утилиты RusCode2 -
    ' определение кодовой таблицы текста по ключевому параметру
    '==========================
    Const KeyCode$ = "!Codepage=" ' ключ поиска
    Const Password$ = "Кодовая таблица" ' идентификатор

    Dim SourceFile$, LenS&, NumByte&, Source$

    Dim TableName$(-1 To 5) ' названия таблиц
    TableName$(-1) = "Нет ключа-идентификатора"
    TableName$(0) = "Неопределена"
    TableName$(1) = "DOS (cp866)"
    TableName$(2) = "Windows (cp1251)"
    TableName$(3) = "UNIX (KOI-8)"
    TableName$(4) = "General(ISO 8859-5)"
    TableName$(5) = "Mac"

    SourceFile$ = App.Path + "\" + "TestWin.htm"
    ' чтение исходного текстового файла
    Open SourceFile$ For Binary As #1
        LenS = LOF(1)
        Source$ = Space$(LenS)
        Get #1, , Source$
    Close #1
    ' поиск ключа
    NumByte = NumberTableTestKey(Source$, KeyCode$, Password$)

    MsgBox "Кодовая таблица файла " & vbCrLf & _
        SourceFile$ & vbCrLf & _
        "= " & TableName$(NumByte)
End Sub

Public Function NumberTableTestKey%(Source$, KeyCode$, Password$)
    ' Проверка кодовой таблицы по ключевым параметрам
    'ВХОД: Source$ - содержимое исходного файла
    ' KeyCode$ - ключ для поиска
    ' Password$ - идентификатор
    'ВЫХОД: значение функции (см. содержимое TableName())
    '
    Dim NumByte&, StrWord$, i%

    NumByte = InStr(Source$, KeyCode$)
    If NumByte <= 0 Then ' нет записи с ключом
        NumberTableTestKey = -1
        Exit Function
    End If

    'выделяем идентификатор
    StrWord$ = Mid$(Source$, NumByte + Len(KeyCode$),     Len(Password$))

    If StrWord$ = Password$ Then 'Windows, cp1251
    NumberTableTestKey = 2: Exit Function
    End If
    NumByte = 0
    For i = 1 To 5 ' сравнение перебором
        If i <> 2 Then '
            If StrWord$ = RusDosOther$(Password$, 2, i) Then
                NumByte = i: Exit For
            End If
        End If
    Next
    NumberTableTestKey = NumByte
End Function

Все программные примеры, которые использовались в приведенных здесь советах, можно найти по адресу: www.visual.2000.ru/develop/vb/source/.

В начало статьи