Excel+VBA – Ověření EAN

Potřebujete zjistit, jestli EAN je validní 13místný kód – tedy že má daný řetězec 13 číslic, z toho ta poslední je správný kontrolní checksum? Tahle funkce vám to vyřeší (vrací TRUE/FALSE).

Function checkEAN(ean)

Dim s As String
Dim cs As Integer
Dim i As Integer
Dim digit As Integer

If (TypeName(ean) = "Range") Then
 s = ean.Value
ElseIf (TypeName(ean) = "String" Or TypeName(ean) = "Integer") Then
 s = ean
End If
 
If (Len(s) <> 13) Then
checkEAN = False
Return
End If
 

cs = 0 'checksum
 
For i = 1 To 12
 digit = Mid(s, i, 1) - "0" 'get the next digit from bar code text
 If i Mod 2 = 0 Then
 cs = cs + digit * 3 'multiply each bar code digit by it's weight, 1 or 3
 Else
 cs = cs + digit * 1
 End If
Next i
 
cs = (10 - (cs Mod 10)) Mod 10 'which digit must be added to cs to make it divisible by 10

checkEAN = False
checkEAN = (Mid(s, 13, 1) = cs)


End Function

Excel+VBA – vlastní vzorec pro regulární výrazy

Tohle je asi můj nejpoužívanější Excelový hack – vlastní vzorec pro regulární výrazy. Bez toho se některé věci dělají strašně složitě přes funkce jako NAJÍT, DOSADIT či NAHRADIT, a nebo dokonce vůbec nejdou a člověk se musí uchýlit k jiným nástrojům.

Excel přitom regulární výrazy umí, ale jen ve svém VBA. Takže stačí si vytvořit vlastní funkci:

#Const LateBind = True
Function PREG_REPLACE(Pattern As String, Replacement As String, Subject As Range)
 #If Not LateBind Then
 Dim RE As RegExp
 Set RE = New RegExp
 #Else
 Dim RE As Object
 Set RE = CreateObject("vbscript.regexp")
 #End If
 RE.Pattern = Pattern
 RE.Global = True
 PREG_REPLACE = RE.Replace(Subject, Replacement)
End Function

Krátká noticka – konstanta LateBind je opravdu před funkcí a zajišťuje, aby se objekt – knihovna s regulárními výrazy, nevolala vícekrát.

Použití podobné jako třeba v PHP, jen Pattern nemusíte uvozovat. Tj. např. když do A1 napíšete „Josef Novák“ a do B1 potom =PREG_REPLACE(„^(.*)\s(.*)“;“$2 $1″;A1), tak výsledkem bude „Novák Josef“ – tedy poslední slovo jste posunuli na začátek.

Excel+VBA – odlišení buněk s vzorcem

Možná to znáte – máte Excelovou tabulku a potřebujete mít sloupec, kde hodnoty počítáte nějakým vzorcem, ale zároveň je někdy potřebujete přepsat fixní hodnotou.

Jenže pak se třeba změní kurz či něco podobného a vy potřebujete nějak snadno identifikovat buňky s fixní hodnotou – můžete je dát najít (CTRL+G / Special / Constants), ale to je někdy nepohodlné, raději byste takové buňky měli rovnou nějak odlišené.

Nebo máte nějakou sdílenou tabulku a potřebujete přehledně najednou identifikovat, které buňky jsou počítány automaticky a která jsou k ručnímu vyplnění …

Vhodným způsobem, jak naformátovat nějaké buňky automaticky na základě splnění nějaké podmínky je podmíněné formátování (Home / Conditional formating) – zde můžete nadefinovat vzorec a formát buňky, který bude použit v případě, že výsledkem vzorce je kladná odpověd.

Takže se nabízí testovat, zda-li daná buňka obsahuje vzorec, jenže Excel nemá žádnou vestavěnou funkci, která by říkala, jestli je buňka konstanta či vzorec. Proto si takovou funkci musíte nejdříve přidat, a to pomocí skriptovacího jazyka VBA (co je to VBA a jak se s ním dělá je mimo záběr tohoto článku, vysvětlím případně jindy).

Public Function HASFORMULA(ByVal cell As Range) As Boolean
 ' Returns whether the cell contains a formula.
 On Error Resume Next
 HASFORMULA = cell.HASFORMULA
End Function

Tahle krátká uživatelská funkce zajistí, že když pak napíšete třeba do buňky B1 vzorec =HASFORMULA (A1), tak vám vrátí TRUE v případě, že v A1 je vzorec a FALSE když nikoliv. Tenhle vzorec pak můžete použít třeba právě v tom podmíněném formátování.