7/25/2019 Varios Programas, Codigo y Trucos
1/5
Generate QR code with google chart API using UDF in Excel
posted Jun 4, 2014, 6:21 AM by roberto mensa [ updated Jun 4, 2014, 8:23 AM ]
by The FranensTeam
!t " #ou$d " %or&
'e %ou$d $(e to share a tr(#y )*F to +enerate - #odeson your %orsheet.The te#hn(/ue (s s(mp$e: you #an #reate a ne% - p(#ture or update an e(st(n+ one by
us(n+ )-/rode5-!57 ormu$a. The p(#ture (tse$ (s +enerated by 9oo+$e #hart
A!, so your system needs to ha;e (nternet #onne#t(on.The p(#ture %($$ be updated %hen the ormu$a (s #a$#u$ated, so %hen you #han+e the ;a$ue
(n the #e$$ %(th $(n to be en#oded A< on the be$o% p(#ture7 you %($$ (mmed(ate$y see the
p(#ture.
*o%n$oad the ($e.
Mandatory arguments:
http://goo.gl/fv3bphttp://en.wikipedia.org/wiki/QR_codehttps://sites.google.com/site/e90e50fx/home/generate-qrcode-with-excel/qr_code_udf.xlsm?attredirects=0&d=1http://en.wikipedia.org/wiki/QR_codehttps://sites.google.com/site/e90e50fx/home/generate-qrcode-with-excel/qr_code_udf.xlsm?attredirects=0&d=1http://goo.gl/fv3bp7/25/2019 Varios Programas, Codigo y Trucos
2/5
=ame o the p(#ture (s mandatory, ( you set a p(#ture name %h(#h a$ready e(sts on
the sheet, (t %($$ be updated. *e$eted and re"added.7 ! you #hoose a ne% name, the
p(#ture %($$ be pos(t(oned net to the #a$$er #e$$.
- ;a$ues (s a$so mandatory, usua$$y (t (s an )- address you %ant to en#ode tothe - p(#ture.
!tional arguments:
(>e o the p(#ture on$y one measure, be#ause the shape %($$ be re#tan+$e7.
*(sp$ayTet (s the return ;a$ue o the ormu$a. !t %($$ be ;(s(b$e (n the #e$$ you %r(te
the )F* #a$$er #e$$7.
)pdateab$e ar+ument #an be set to Fa$se ( the p(#ture shou$d not be updated %hen
the reerred #e$$ ;a$ues #han+e. !t #ou$d be useu$ ( you use the too$ o$(ne %(thout(nternet #onne#t(on7.
"echnology:
The base #on#ept o th(s )*F te#hn(/ue (s the a#t that )*Fs #ou$d mod(y, de$ete and add
shapes to %orsheet.
'e use 9oo+$e #hart A! to +enerate the - #ode (tse$. ?ur )*F #omposes the )- orthe A!, and use th(s )- as the (rst ar+ument o the hapes.Add(#ture method.
7/25/2019 Varios Programas, Codigo y Trucos
3/5
@ou #an use our eamp$e ($e, or you #an #opy the #ode to your o%n ($es.
Option Explicit'other technical specifications about google chart API:'https://developers.google.com/chart/infographics/docs/qrcodes
!unction "#$%#&odeE#IE( )*+al Picture,ame As tring- )*+al %#+alue As tring- Optional )*+al Pictureie As $ong 012- Optional )*+al 3ispla*4ext As tring 55- Optional )*+al "pdateable As )oolean 4rue6 As +ariant
3im oPic As hape- o#ng As Excel.#ange3im v$eft As +ariant- v4op As +ariant3im s"#$ As tring
&onst s#oot"#$ As tring 5https://chart.googleapis.com/chart75&onst sieParameter As tring 5chs5
&onst s4*pe&hart As tring 5chtqr5&onst s3ataParameter As tring 5chl5&onst s8oin&9# As tring 55
If "pdateable !alse 4hen "#$%#&odeE#IE 5outdated5 Exit !unctionEnd If
et o#ng Application.&aller.Offset(- 06On Error #esume ,extet oPic o#ng.Parent.hapes(Picture,ame6If Err 4hen
Err.&lear v$eft o#ng.$eft ; < v4op o#ng.4opElse v$eft oPic.$eft v4op oPic.4op Pictureie Int(oPic.=idth6 oPic.3eleteEnd IfOn Error >o4o 2
If $en(%#+alue6 2 4hen "#$%#&odeE#IE &+Err(xlErr+alue6
Exit !unctionEnd If
s"#$ s#oot"#$ sieParameter Pictureie 5x5 Pictureie s8oin&9# s4*pe&hart s8oin&9# s3ataParameter "4!?"#$Encode(+)A.#eplace(%#+alue- 5 5- 5;566
et oPic o#ng.Parent.hapes.AddPicture(s"#$- 4rue- 4rue- v$eft- v4op-Pictureie- Pictureie6
https://sites.google.com/site/e90e50fx/home/generate-qrcode-with-excel/qr_code_udf.xlsm?attredirects=0&d=1https://sites.google.com/site/e90e50fx/home/generate-qrcode-with-excel/qr_code_udf.xlsm?attredirects=0&d=17/25/2019 Varios Programas, Codigo y Trucos
4/5
oPic.,ame Picture,ame"#$%#&odeE#IE 3ispla*4extEnd !unction
!unction "4!?"#$Encode()*+al str As tring6
'http://@@@.nonhostile.com/ho@toconvertb*tearra*utf?stringvbB.asp 3im i As $ong 3im a As $ong 3im res As tring 3im code As tring
res 55 !or i 0 4o $en(str6 a Asc=(Cid(str- i- 066 If a D 0? 4hen code Cid(str- i- 06 ElseIf ((a F 0G6 And (a D 2
7/25/2019 Varios Programas, Codigo y Trucos
5/5