Обработка в Ехеле - LUHN и MOD43

[Littlesnake]

Задачка

Требуется обработка а Ехеле. В моей конторе два раза в месяц нужно делать вот такую фичу.
Файлы отдаются в типографию, там печатают на их основе штрих-коды (вроде код39),
которые мы клеим на продукцию.
Как и в чем их считал мой предшественник - не представляю, ничего не осталось.
Умные люди подсказали про макросы.

Суть такая - в результате этой обработки имеем файл Ехеля, в котором:

колонка 1 - числовой ряд - последовательность 15-значных чисел
колонка 2 - контрольная сумма от числа колонки 1 по алгоритму LUHN - одна цифра
колонка 3 - 16-значное число, полученное слиянием колонок 1 и 2
колонка 4 - контрольная цифра-буква от числа колонки 3 по алгоритму MOD43
колонка 5 - 17-значное число, полученное слиянием колонок 3 и 4

Задача - просчитать колонки 2-5

пример:
колонка 1 - 964377409000004
колонка 2 - 7
колонка 3 - 9643774090000047
колонка 4 - H
колонка 5 - 9643774090000047H

колонка 1 - числовой ряд - 500 номеров (от ...0001 до ...0500), просчет 3 раза по 500
(изменение ряда будет только в 9-ом разряде, в приведенном примере это цифра "9")
Меняю 9-ый разряд - заново запускаю макрос.

Макросов может быть несколько - для отдельных колонок.

Как создать первую колонку - не существенно
Как поменять в первой колонке 9-ый разряд - не существенно.
Цифры числового ряда от месяца к месяцу меняются.

Поискал я в Инете -

Что такое LUHN - ГОСТ 50809-95, или тут http://planety-hackeram.ru/showthread.php?t=10440
Пример макроса LUHN тут - Http://www.beachnet.com/~hstiles/cardtype.html (...Here is a Microsoft Excel worksheet)
Любопытно - http://www.infostart.ru/profile/22185/projects/2270/
Чекер - http://www.ee.unb.ca/cgi-bin/tervo/luhn.pl

Что такое MOD43 - http://en.wikipedia.org/wiki/Code_39 с примером
Пример обработки - http://www.dreamincode.net/forums/showtopic25941.htm

[Serge_Bliznykov]

а можно вопрос? А зачем Вам отдельно столбцы 2, 3 и 4 ???
ведь Вас интересует только столбец 5 ?
так его в макросе и формировать! а ещё лучше - в пользовательской функции...

Алгоритмы элементарные, а работу по поиску информации Вы провели просто отличную!!!!
у Вас же уже всё необходимое для расчёта есть!!

результат во вложении.


если кому интересно, реализовано через пользовательскую функцию:

Public Function LUHN_MOD43(r As Range) As String
  Dim i%, s As String, sSum%, One%, CurMultiply%
  ' Application.Volatile True ' пересчитывать функцию, если что-то на листе изменилось
  s = Trim(CStr(r))
  If Len(s) = 0 Then
    LUHN_MOD43 = ""
    Exit Function
  End If
  s = s & "0" ' добавили контрольный разряд
  sSum = 0
  CurMultiply = 1
  For i = Len(s) To 1 Step -1
    One = CInt(Mid(s, i, 1))
    sSum = sSum + (One * CurMultiply)
    If (One * CurMultiply) >= 10 Then
       sSum = sSum - 9
    End If
    CurMultiply = CurMultiply + 1
    If CurMultiply = 3 Then CurMultiply = 1
  Next i
  
  ' теперь в S число с контрольным разрядом LUHN
  If ((sSum Mod 10) <> 0) Then
    s = Trim(CStr(r)) & Trim(Str(10 - (sSum Mod 10)))
  End If
  
  ' подсчитаем MOD43
  sSum = 0
  Const charSet As String _
    = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-. $/+%"
  For i = 1 To Len(s)
    sSum = sSum + InStr(charSet, Mid(s, i, 1)) - 1
  Next i
  LUHN_MOD43 = s & Mid$(charSet, (sSum Mod 43 + 1), 1)
End Function

[Littlesnake]

Нужны именно ВСЕ 5 колонок - именно так
Изюм в том - что полиграфисты при печати почемуто используют ДВЕ колонки - штрих-код колбасят по колонке ПЯТЬ,
а хьюман редабле текст - по колонке ТРИ
т.е результат - стикерс со штрих-кодом, ниже которого (на том же стикерсе) чепятается число - хьюман редабле текст (человекочитаемый текст).
Хотя, в принципе, без колонок 2 и 4 можно обойтись (промежуточные данные, однако), я думаю.

За обработку спасибо - как все просто, оказывается

[Serge_Bliznykov]

дальше сами разберётесь?

я бы рекомендовал колонки 2, 3, 4 получать из колонки 5 простыми строковыми формулами (через =ПСТР(...) )

[Littlesnake]

Чтото я не въезжаю - сижу смотрю и тихо тупею
Все ж не та у меня квалификация

а в Ехеле-2007 это будет работать ? - вопрос глупый, снимается
А как запустить эту функцию ?
Чего ткнуть?

[Littlesnake]

Сообщение от Serge_Bliznykov

дальше сами разберётесь?

я бы рекомендовал колонки 2, 3, 4 получать из колонки 5 простыми строковыми формулами (через =ПСТР(...) )

С этим поколдую сам

[Littlesnake]

Только что - не понял, что сделал - в колонке В появился правильный результат.
При перезагрузке файла LUHN_MOD_example.xls - пишет про "Остановка макроса. Синтаксическая ошибка"

[Littlesnake]

Так - работает