愚者の経験

「また今度」はほとんどこない

月別アーカイブ: 3月 2013

[VBA]作っておくと若干便利な関数

1.Application.Echoの改良

Public Sub DisplayLock(ByVal OnOff As Boolean)
    Static cnt As Long
    
    cnt = cnt + IIf(OnOff, 1, -1)
    
    If ((cnt = 1) And (OnOff)) Then
        Application.Echo False
    End If
    
    If ((cnt = 0) And (Not OnOff)) Then
        Application.Echo True
    End If
    
    If (cnt < 0) Then
        cnt = 0
    End If
End Sub

Echoは入れ子にすると解除のタイミングが早まってしまうので
ものぐさな私はこんなことしてます。

2.VBAでInみたいな比較をしたい。Orは書きにくい

Public Function Exists(Source As Variant, ParamArray Params() As Variant) As Boolean
    Exists = Eval(CStr(Source) & " in(" & Join(Params, ",") & ")")
End Function

速度は度外視でぐうたらに使います。

3.色の指定がめんどくさい。

Public Function StrRGB(ByVal Color As String) As Long
    StrRGB = RGB( _
        val("&H" & Right$("00" & Mid$(Color, 1, 2), 2)), _
        val("&H" & Right$("00" & Mid$(Color, 3, 2), 2)), _
        val("&H" & Right$("00" & Mid$(Color, 5, 2), 2)))
End Function

この方が私はわかりやすいです。

Fix関数落とし穴[Access][VBA]

小数部を切り取る関数の2大筆頭です。もう一つは「int関数」
さて私のパソコンだとイミディエイトウィンドウで以下を実行すると

?fix(113)/100
?fix(1.13*100)/100
?fix(1.13*10^2)/100

次のようになりました。
?fix(113)/100
1.13
?fix(1.13*100)/100
1.12
?fix(1.13*10^2)/100
1.12

はい、ずれました~orz
「Fix関数」は小数部を切ってくれますが第一引数の時点で丸められて
しまうとどうしようもないですので多分こんなことになります。

?1.13*100=113
False

?1.13*100<113
True

上記のように出ますので1.13*100は整数部112なわけです。
浮動小数点型で誤差が発生するのが分かります。

端数処理関数を作ってみました。
「切り上げ」は絶対値基準で行なっています。

Option Compare Database
Option Explicit

Public Enum RoundOperationEnum
    RoundUp             '切り上げ
    RoundDown           '切り捨て
    RoundOff            '四捨五入
End Enum

'端数処理
Public Function Round(Value As Variant, ByVal Digits As Long, Operation As RoundOperationEnum) As Variant
On Error GoTo exit1
    Dim dec As Variant
    dec = CDec(Value)
    
    Select Case Operation
        Case RoundOff
            Round = Fix(dec * (10 ^ Digits) + 0.5 * Sgn(dec)) / (10 ^ Digits)
        Case RoundDown
            Round = Fix(dec * (10 ^ Digits)) / (10 ^ Digits)
        Case RoundUp
            Round = -Int(-Abs(dec) * (10 ^ Digits)) * Sgn(dec) / (10 ^ Digits)
    End Select
    Exit Function
exit1:
    Round = 0
End Function

CurrencyやDoubleでデメリット背負うよりはDecimal型かむしろString型のほうが
計算そのものには適しているんじゃないかとさえ思います。

見かけの文字列長で切り取る[SQL Server]

もうこの問題かなりめんどくさいです。なんで日本語って全部全角じゃないんですかね。
そもそもアルファベットとか数字とか記号を見かけ上半分の幅にしたか、
それ以外の文字を倍の幅にしたかしてしまった時点で大きなハンデを背負っている
と思います。人生のパソコン時間でアルファベット、数字、記号の半角全角変換の
時間を集約したら下手したら一年くらいかかってるんじゃないかって思います。

文書の校正で数字の変な全角半角混じりがないかとか…色々時間がかかります。
言いたいこと多々はありますがこいつのお陰で「文字列の終点を揃える」という行為に
多大な労力を割かれることになります。

なんだかんだ言ってサンプルです。

declare @var varchar(30)
set @var='是はtestですYO'
select @var as n0
	,'['+cast(substring(CAST(@var as varbinary(100)),1,1) as varchar(30)) +']' as n1
	,'['+cast(substring(CAST(@var as varbinary(100)),1,2) as varchar(30)) +']' as n2
	,'['+cast(substring(CAST(@var as varbinary(100)),1,3) as varchar(30)) +']' as n3
	,'['+cast(substring(CAST(@var as varbinary(100)),1,4) as varchar(30)) +']' as n4
	,'['+cast(substring(CAST(@var as varbinary(100)),1,5) as varchar(30)) +']' as n5
	,'['+cast(substring(CAST(@var as varbinary(100)),1,6) as varchar(30)) +']' as n6
	,'['+cast(substring(CAST(@var as varbinary(100)),1,7) as varchar(30)) +']' as n7
	,'['+cast(substring(CAST(@var as varbinary(100)),1,8) as varchar(30)) +']' as n8
	,'['+cast(substring(CAST(@var as varbinary(100)),1,9) as varchar(30)) +']' as n9
	,'['+cast(substring(CAST(@var as varbinary(100)),1,10) as varchar(30)) +']' as n10
	,'['+cast(substring(CAST(@var as varbinary(100)),1,11) as varchar(30)) +']' as n11
	,'['+cast(substring(CAST(@var as varbinary(100)),1,12) as varchar(30)) +']' as n12
	,'['+cast(substring(CAST(@var as varbinary(100)),1,13) as varchar(30)) +']' as n13
	,'['+cast(substring(CAST(@var as varbinary(100)),1,14) as varchar(30)) +']' as n14
	,'['+cast(substring(CAST(@var as varbinary(100)),1,15) as varchar(30)) +']' as n15
	,'['+cast(substring(CAST(@var as varbinary(100)),1,16) as varchar(30)) +']' as n16

重要なのは一旦
varchar型varbinary型にcastする」ことです。
元の文字列をnvarchar型の場合は前準備としてvarchar型にcastする必要があります。

text型にしても同様にできますが、将来削除予定のデータ型で使うなと言われているので
あえてvarbinary型にしています。また文字列の切り取りは「substring関数」しか
まともに使えそうなのがないです。「left」や「right」といった関数はvarbinary型のまま文字列を切れないので
暗黙的にvarchar型に変換して切ってしまい、元の木阿弥です。

文字切れに対しては勝手に表示しないようにしてくれます。

VBA版はこっちで。

Group Byでの文字列連結[SQL Server]

「SQLCLR」で集計関数使ってもいいんですがもう少し手軽に
扱いたいなら「FOR XML PATH(”),TYPE」でselectの結果を
XML型にしValueメソッド(?)でvarcharやnvarcharに変換すると可能です。

サンプルSQL(グループの文字列はサブクエリになります)

create table #tmp(
	ID int identity(1,1) not null ,
	NUM int null,
	TXT varchar(50) null
)

insert into #tmp(NUM,TXT)
values(1,'fjak;lj')
	,(2,'fakdj')
	,(1,'f;laj')
	,(2,'dfjoji')
	,(3,'roa')
	,(2,'jfdosk')
	,(1,'ajfasj')
	,(2,'dfoaif')
	,(2,'djljfl')
	,(1,'kajl')
	,(3,'f')

select T.NUM
	,(
		select '##'+X.TXT
		from #tmp as X
		where X.NUM=T.NUM
		order by X.ID
		for xml path(''),type).value('.','nvarchar(max)'
	) as gpjoin
from #tmp as T
group by NUM

drop table #tmp

実際Valueメソッドは使わなくてもいいのですが
xmlのマークアップに使われる特殊文字は変換されますので
Valueメソッドを使うほうが安全です。<>→&lt;&gt;みたいな。