-
In suppot of this Thread
http://www.eileenslounge.com/viewtopic.php?f=30&t=35600
Code:
' http://www.eileenslounge.com/viewtopic.php?f=30&t=35600
Sub ConvertBytesToString1()
Dim Ay() As Variant: Let Ay() = Array(89, 97, 115, 115, 101, 114)
Dim Es As String
Dim Spt() As String
Let Spt() = Split(ChrW(1) & "Sp" & ChrW(2) & "Sp" & ChrW(3) & "Sp" & ChrW(4) & "Sp" & ChrW(5) & "Sp" & ChrW(6) & "Sp" & ChrW(7) & "Sp" & ChrW(8) & "Sp" & ChrW(9) & "Sp" & ChrW(10) & "Sp" & ChrW(11) & "Sp" & ChrW(12) & "Sp" & ChrW(13) & "Sp" & ChrW(14) & "Sp" & ChrW(15) & "Sp" & ChrW(16) & "Sp" & ChrW(17) & "Sp" & ChrW(18) & "Sp" & ChrW(19) & "Sp" & ChrW(20) & "Sp" & ChrW(21) & "Sp" & ChrW(22) & "Sp" & ChrW(23) & "Sp" & ChrW(24) & "Sp" & ChrW(25) & "Sp" & ChrW(26) & "Sp" & ChrW(27) & "Sp" & ChrW(28) & "Sp" & ChrW(29) & "Sp" & ChrW(30) & "Sp" & ChrW(31) & "Sp" & ChrW(32) & "Sp" & ChrW(33) & "Sp" & ChrW(34) & "Sp" & ChrW(35) & "Sp" & ChrW(36) & "Sp" & ChrW(37) & "Sp" & ChrW(38) & "Sp" & ChrW(39) & "Sp" & ChrW(40) & "Sp" & ChrW(41) & "Sp" & ChrW(42) & "Sp" & ChrW(43) & "Sp" & ChrW(44) & "Sp" & ChrW(45) & "Sp" & ChrW(46) & "Sp" & ChrW(47) & "Sp" & ChrW(48) & "Sp" & ChrW(49) & "Sp" & ChrW(50) & _
"Sp" & ChrW(51) & "Sp" & ChrW(52) & "Sp" & ChrW(53) & "Sp" & ChrW(54) & "Sp" & ChrW(55) & "Sp" & ChrW(56) & "Sp" & ChrW(57) & "Sp" & ChrW(58) & "Sp" & ChrW(59) & "Sp" & ChrW(60) & "Sp" & ChrW(61) & "Sp" & ChrW(62) & "Sp" & ChrW(63) & "Sp" & ChrW(64) & "Sp" & ChrW(65) & "Sp" & ChrW(66) & "Sp" & ChrW(67) & "Sp" & ChrW(68) & "Sp" & ChrW(69) & "Sp" & ChrW(70) & "Sp" & ChrW(71) & "Sp" & ChrW(72) & "Sp" & ChrW(73) & "Sp" & ChrW(74) & "Sp" & ChrW(75) & "Sp" & ChrW(76) & "Sp" & ChrW(77) & "Sp" & ChrW(78) & "Sp" & ChrW(79) & "Sp" & ChrW(80) & "Sp" & ChrW(81) & "Sp" & ChrW(82) & "Sp" & ChrW(83) & "Sp" & ChrW(84) & "Sp" & ChrW(85) & "Sp" & ChrW(86) & "Sp" & ChrW(87) & "Sp" & ChrW(88) & "Sp" & ChrW(89) & "Sp" & ChrW(90) & "Sp" & ChrW(91) & "Sp" & ChrW(92) & "Sp" & ChrW(93) & "Sp" & ChrW(94) & "Sp" & ChrW(95) & "Sp" & ChrW(96) & "Sp" & ChrW(97) & "Sp" & ChrW(98) & "Sp" & ChrW(99) & "Sp" & ChrW(100) & _
"Sp" & ChrW(101) & "Sp" & ChrW(102) & "Sp" & ChrW(103) & "Sp" & ChrW(104) & "Sp" & ChrW(105) & "Sp" & ChrW(106) & "Sp" & ChrW(107) & "Sp" & ChrW(108) & "Sp" & ChrW(109) & "Sp" & ChrW(110) & "Sp" & ChrW(111) & "Sp" & ChrW(112) & "Sp" & ChrW(113) & "Sp" & ChrW(114) & "Sp" & ChrW(115) & "Sp" & ChrW(116) & "Sp" & ChrW(117) & "Sp" & ChrW(118) & "Sp" & ChrW(119) & "Sp" & ChrW(120) & "Sp" & ChrW(121) & "Sp" & ChrW(122) & "Sp" & ChrW(123) & "Sp" & ChrW(124) & "Sp" & ChrW(125) & "Sp" & ChrW(126) & "Sp" & ChrW(127) & "Sp" & ChrW(128) & "Sp" & ChrW(129) & "Sp" & ChrW(130) & "Sp" & ChrW(131) & "Sp" & ChrW(132) & "Sp" & ChrW(133) & "Sp" & ChrW(134) & "Sp" & ChrW(135) & "Sp" & ChrW(136) & "Sp" & ChrW(137) & "Sp" & ChrW(138) & "Sp" & ChrW(139) & "Sp" & ChrW(140) & "Sp" & ChrW(141) & "Sp" & ChrW(142) & "Sp" & ChrW(143) & "Sp" & ChrW(144) & "Sp" & ChrW(145) & "Sp" & ChrW(146) & "Sp" & ChrW(147) & "Sp" & ChrW(148) & "Sp" & ChrW(149) & "Sp" & ChrW(150) & _
"Sp" & ChrW(151) & "Sp" & ChrW(152) & "Sp" & ChrW(153) & "Sp" & ChrW(154) & "Sp" & ChrW(155) & "Sp" & ChrW(156) & "Sp" & ChrW(157) & "Sp" & ChrW(158) & "Sp" & ChrW(159) & "Sp" & ChrW(160) & "Sp" & ChrW(161) & "Sp" & ChrW(162) & "Sp" & ChrW(163) & "Sp" & ChrW(164) & "Sp" & ChrW(165) & "Sp" & ChrW(166) & "Sp" & ChrW(167) & "Sp" & ChrW(168) & "Sp" & ChrW(169) & "Sp" & ChrW(170) & "Sp" & ChrW(171) & "Sp" & ChrW(172) & "Sp" & ChrW(173) & "Sp" & ChrW(174) & "Sp" & ChrW(175) & "Sp" & ChrW(176) & "Sp" & ChrW(177) & "Sp" & ChrW(178) & "Sp" & ChrW(179) & "Sp" & ChrW(180) & "Sp" & ChrW(181) & "Sp" & ChrW(182) & "Sp" & ChrW(183) & "Sp" & ChrW(184) & "Sp" & ChrW(185) & "Sp" & ChrW(186) & "Sp" & ChrW(187) & "Sp" & ChrW(188) & "Sp" & ChrW(189) & "Sp" & ChrW(190) & "Sp" & ChrW(191) & "Sp" & ChrW(192) & "Sp" & ChrW(193) & "Sp" & ChrW(194) & "Sp" & ChrW(195) & "Sp" & ChrW(196) & "Sp" & ChrW(197) & "Sp" & ChrW(198) & "Sp" & ChrW(199) & "Sp" & ChrW(200), "Sp")
' Let Range("A2").Resize(1, 200) = Spt()
Let Es = Join(Application.Index(Spt(), 1, Ay), "")
' Or
Let Es = Join(Application.Index(Split(ChrW(1) & "Sp" & ChrW(2) & "Sp" & ChrW(3) & "Sp" & ChrW(4) & "Sp" & ChrW(5) & "Sp" & ChrW(6) & "Sp" & ChrW(7) & "Sp" & ChrW(8) & "Sp" & ChrW(9) & "Sp" & ChrW(10) & "Sp" & ChrW(11) & "Sp" & ChrW(12) & "Sp" & ChrW(13) & "Sp" & ChrW(14) & "Sp" & ChrW(15) & "Sp" & ChrW(16) & "Sp" & ChrW(17) & "Sp" & ChrW(18) & "Sp" & ChrW(19) & "Sp" & ChrW(20) & "Sp" & ChrW(21) & "Sp" & ChrW(22) & "Sp" & ChrW(23) & "Sp" & ChrW(24) & "Sp" & ChrW(25) & "Sp" & ChrW(26) & "Sp" & ChrW(27) & "Sp" & ChrW(28) & "Sp" & ChrW(29) & "Sp" & ChrW(30) & "Sp" & ChrW(31) & "Sp" & ChrW(32) & "Sp" & ChrW(33) & "Sp" & ChrW(34) & "Sp" & ChrW(35) & "Sp" & ChrW(36) & "Sp" & ChrW(37) & "Sp" & ChrW(38) & "Sp" & ChrW(39) & "Sp" & ChrW(40) & "Sp" & ChrW(41) & "Sp" & ChrW(42) & "Sp" & ChrW(43) & "Sp" & ChrW(44) & "Sp" & ChrW(45) & "Sp" & ChrW(46) & "Sp" & ChrW(47) & "Sp" & ChrW(48) & "Sp" & ChrW(49) & "Sp" & ChrW(50) & _
"Sp" & ChrW(51) & "Sp" & ChrW(52) & "Sp" & ChrW(53) & "Sp" & ChrW(54) & "Sp" & ChrW(55) & "Sp" & ChrW(56) & "Sp" & ChrW(57) & "Sp" & ChrW(58) & "Sp" & ChrW(59) & "Sp" & ChrW(60) & "Sp" & ChrW(61) & "Sp" & ChrW(62) & "Sp" & ChrW(63) & "Sp" & ChrW(64) & "Sp" & ChrW(65) & "Sp" & ChrW(66) & "Sp" & ChrW(67) & "Sp" & ChrW(68) & "Sp" & ChrW(69) & "Sp" & ChrW(70) & "Sp" & ChrW(71) & "Sp" & ChrW(72) & "Sp" & ChrW(73) & "Sp" & ChrW(74) & "Sp" & ChrW(75) & "Sp" & ChrW(76) & "Sp" & ChrW(77) & "Sp" & ChrW(78) & "Sp" & ChrW(79) & "Sp" & ChrW(80) & "Sp" & ChrW(81) & "Sp" & ChrW(82) & "Sp" & ChrW(83) & "Sp" & ChrW(84) & "Sp" & ChrW(85) & "Sp" & ChrW(86) & "Sp" & ChrW(87) & "Sp" & ChrW(88) & "Sp" & ChrW(89) & "Sp" & ChrW(90) & "Sp" & ChrW(91) & "Sp" & ChrW(92) & "Sp" & ChrW(93) & "Sp" & ChrW(94) & "Sp" & ChrW(95) & "Sp" & ChrW(96) & "Sp" & ChrW(97) & "Sp" & ChrW(98) & "Sp" & ChrW(99) & "Sp" & ChrW(100) & _
"Sp" & ChrW(101) & "Sp" & ChrW(102) & "Sp" & ChrW(103) & "Sp" & ChrW(104) & "Sp" & ChrW(105) & "Sp" & ChrW(106) & "Sp" & ChrW(107) & "Sp" & ChrW(108) & "Sp" & ChrW(109) & "Sp" & ChrW(110) & "Sp" & ChrW(111) & "Sp" & ChrW(112) & "Sp" & ChrW(113) & "Sp" & ChrW(114) & "Sp" & ChrW(115) & "Sp" & ChrW(116) & "Sp" & ChrW(117) & "Sp" & ChrW(118) & "Sp" & ChrW(119) & "Sp" & ChrW(120) & "Sp" & ChrW(121) & "Sp" & ChrW(122) & "Sp" & ChrW(123) & "Sp" & ChrW(124) & "Sp" & ChrW(125) & "Sp" & ChrW(126) & "Sp" & ChrW(127) & "Sp" & ChrW(128) & "Sp" & ChrW(129) & "Sp" & ChrW(130) & "Sp" & ChrW(131) & "Sp" & ChrW(132) & "Sp" & ChrW(133) & "Sp" & ChrW(134) & "Sp" & ChrW(135) & "Sp" & ChrW(136) & "Sp" & ChrW(137) & "Sp" & ChrW(138) & "Sp" & ChrW(139) & "Sp" & ChrW(140) & "Sp" & ChrW(141) & "Sp" & ChrW(142) & "Sp" & ChrW(143) & "Sp" & ChrW(144) & "Sp" & ChrW(145) & "Sp" & ChrW(146) & "Sp" & ChrW(147) & "Sp" & ChrW(148) & "Sp" & ChrW(149) & "Sp" & ChrW(150) & _
"Sp" & ChrW(151) & "Sp" & ChrW(152) & "Sp" & ChrW(153) & "Sp" & ChrW(154) & "Sp" & ChrW(155) & "Sp" & ChrW(156) & "Sp" & ChrW(157) & "Sp" & ChrW(158) & "Sp" & ChrW(159) & "Sp" & ChrW(160) & "Sp" & ChrW(161) & "Sp" & ChrW(162) & "Sp" & ChrW(163) & "Sp" & ChrW(164) & "Sp" & ChrW(165) & "Sp" & ChrW(166) & "Sp" & ChrW(167) & "Sp" & ChrW(168) & "Sp" & ChrW(169) & "Sp" & ChrW(170) & "Sp" & ChrW(171) & "Sp" & ChrW(172) & "Sp" & ChrW(173) & "Sp" & ChrW(174) & "Sp" & ChrW(175) & "Sp" & ChrW(176) & "Sp" & ChrW(177) & "Sp" & ChrW(178) & "Sp" & ChrW(179) & "Sp" & ChrW(180) & "Sp" & ChrW(181) & "Sp" & ChrW(182) & "Sp" & ChrW(183) & "Sp" & ChrW(184) & "Sp" & ChrW(185) & "Sp" & ChrW(186) & "Sp" & ChrW(187) & "Sp" & ChrW(188) & "Sp" & ChrW(189) & "Sp" & ChrW(190) & "Sp" & ChrW(191) & "Sp" & ChrW(192) & "Sp" & ChrW(193) & "Sp" & ChrW(194) & "Sp" & ChrW(195) & "Sp" & ChrW(196) & "Sp" & ChrW(197) & "Sp" & ChrW(198) & "Sp" & ChrW(199) & "Sp" & ChrW(200), "Sp"), 1, Ay), "")
End Sub
-
In support of this Thread
http://www.eileenslounge.com/viewtopic.php?f=30&t=35600
Code:
Sub ConvertBytesToString2()
Dim Es As String
Dim Spt() As String
Let Spt() = Split(ChrW(1) & "Sp" & ChrW(2) & "Sp" & ChrW(3) & "Sp" & ChrW(4) & "Sp" & ChrW(5) & "Sp" & ChrW(6) & "Sp" & ChrW(7) & "Sp" & ChrW(8) & "Sp" & ChrW(9) & "Sp" & ChrW(10) & "Sp" & ChrW(11) & "Sp" & ChrW(12) & "Sp" & ChrW(13) & "Sp" & ChrW(14) & "Sp" & ChrW(15) & "Sp" & ChrW(16) & "Sp" & ChrW(17) & "Sp" & ChrW(18) & "Sp" & ChrW(19) & "Sp" & ChrW(20) & "Sp" & ChrW(21) & "Sp" & ChrW(22) & "Sp" & ChrW(23) & "Sp" & ChrW(24) & "Sp" & ChrW(25) & "Sp" & ChrW(26) & "Sp" & ChrW(27) & "Sp" & ChrW(28) & "Sp" & ChrW(29) & "Sp" & ChrW(30) & "Sp" & ChrW(31) & "Sp" & ChrW(32) & "Sp" & ChrW(33) & "Sp" & ChrW(34) & "Sp" & ChrW(35) & "Sp" & ChrW(36) & "Sp" & ChrW(37) & "Sp" & ChrW(38) & "Sp" & ChrW(39) & "Sp" & ChrW(40) & "Sp" & ChrW(41) & "Sp" & ChrW(42) & "Sp" & ChrW(43) & "Sp" & ChrW(44) & "Sp" & ChrW(45) & "Sp" & ChrW(46) & "Sp" & ChrW(47) & "Sp" & ChrW(48) & "Sp" & ChrW(49) & "Sp" & ChrW(50) & _
"Sp" & ChrW(51) & "Sp" & ChrW(52) & "Sp" & ChrW(53) & "Sp" & ChrW(54) & "Sp" & ChrW(55) & "Sp" & ChrW(56) & "Sp" & ChrW(57) & "Sp" & ChrW(58) & "Sp" & ChrW(59) & "Sp" & ChrW(60) & "Sp" & ChrW(61) & "Sp" & ChrW(62) & "Sp" & ChrW(63) & "Sp" & ChrW(64) & "Sp" & ChrW(65) & "Sp" & ChrW(66) & "Sp" & ChrW(67) & "Sp" & ChrW(68) & "Sp" & ChrW(69) & "Sp" & ChrW(70) & "Sp" & ChrW(71) & "Sp" & ChrW(72) & "Sp" & ChrW(73) & "Sp" & ChrW(74) & "Sp" & ChrW(75) & "Sp" & ChrW(76) & "Sp" & ChrW(77) & "Sp" & ChrW(78) & "Sp" & ChrW(79) & "Sp" & ChrW(80) & "Sp" & ChrW(81) & "Sp" & ChrW(82) & "Sp" & ChrW(83) & "Sp" & ChrW(84) & "Sp" & ChrW(85) & "Sp" & ChrW(86) & "Sp" & ChrW(87) & "Sp" & ChrW(88) & "Sp" & ChrW(89) & "Sp" & ChrW(90) & "Sp" & ChrW(91) & "Sp" & ChrW(92) & "Sp" & ChrW(93) & "Sp" & ChrW(94) & "Sp" & ChrW(95) & "Sp" & ChrW(96) & "Sp" & ChrW(97) & "Sp" & ChrW(98) & "Sp" & ChrW(99) & "Sp" & ChrW(100) & _
"Sp" & ChrW(101) & "Sp" & ChrW(102) & "Sp" & ChrW(103) & "Sp" & ChrW(104) & "Sp" & ChrW(105) & "Sp" & ChrW(106) & "Sp" & ChrW(107) & "Sp" & ChrW(108) & "Sp" & ChrW(109) & "Sp" & ChrW(110) & "Sp" & ChrW(111) & "Sp" & ChrW(112) & "Sp" & ChrW(113) & "Sp" & ChrW(114) & "Sp" & ChrW(115) & "Sp" & ChrW(116) & "Sp" & ChrW(117) & "Sp" & ChrW(118) & "Sp" & ChrW(119) & "Sp" & ChrW(120) & "Sp" & ChrW(121) & "Sp" & ChrW(122) & "Sp" & ChrW(123) & "Sp" & ChrW(124) & "Sp" & ChrW(125) & "Sp" & ChrW(126) & "Sp" & ChrW(127) & "Sp" & ChrW(128) & "Sp" & ChrW(129) & "Sp" & ChrW(130) & "Sp" & ChrW(131) & "Sp" & ChrW(132) & "Sp" & ChrW(133) & "Sp" & ChrW(134) & "Sp" & ChrW(135) & "Sp" & ChrW(136) & "Sp" & ChrW(137) & "Sp" & ChrW(138) & "Sp" & ChrW(139) & "Sp" & ChrW(140) & "Sp" & ChrW(141) & "Sp" & ChrW(142) & "Sp" & ChrW(143) & "Sp" & ChrW(144) & "Sp" & ChrW(145) & "Sp" & ChrW(146) & "Sp" & ChrW(147) & "Sp" & ChrW(148) & "Sp" & ChrW(149) & "Sp" & ChrW(150) & _
"Sp" & ChrW(151) & "Sp" & ChrW(152) & "Sp" & ChrW(153) & "Sp" & ChrW(154) & "Sp" & ChrW(155) & "Sp" & ChrW(156) & "Sp" & ChrW(157) & "Sp" & ChrW(158) & "Sp" & ChrW(159) & "Sp" & ChrW(160) & "Sp" & ChrW(161) & "Sp" & ChrW(162) & "Sp" & ChrW(163) & "Sp" & ChrW(164) & "Sp" & ChrW(165) & "Sp" & ChrW(166) & "Sp" & ChrW(167) & "Sp" & ChrW(168) & "Sp" & ChrW(169) & "Sp" & ChrW(170) & "Sp" & ChrW(171) & "Sp" & ChrW(172) & "Sp" & ChrW(173) & "Sp" & ChrW(174) & "Sp" & ChrW(175) & "Sp" & ChrW(176) & "Sp" & ChrW(177) & "Sp" & ChrW(178) & "Sp" & ChrW(179) & "Sp" & ChrW(180) & "Sp" & ChrW(181) & "Sp" & ChrW(182) & "Sp" & ChrW(183) & "Sp" & ChrW(184) & "Sp" & ChrW(185) & "Sp" & ChrW(186) & "Sp" & ChrW(187) & "Sp" & ChrW(188) & "Sp" & ChrW(189) & "Sp" & ChrW(190) & "Sp" & ChrW(191) & "Sp" & ChrW(192) & "Sp" & ChrW(193) & "Sp" & ChrW(194) & "Sp" & ChrW(195) & "Sp" & ChrW(196) & "Sp" & ChrW(197) & "Sp" & ChrW(198) & "Sp" & ChrW(199) & "Sp" & ChrW(200), "Sp")
Let Es = Join(Application.Index(Spt(), 1, Evaluate("={89, 97, 115, 115, 101, 114}")), "")
' Or
Let Es = Join(Application.Index(Split(ChrW(1) & "Sp" & ChrW(2) & "Sp" & ChrW(3) & "Sp" & ChrW(4) & "Sp" & ChrW(5) & "Sp" & ChrW(6) & "Sp" & ChrW(7) & "Sp" & ChrW(8) & "Sp" & ChrW(9) & "Sp" & ChrW(10) & "Sp" & ChrW(11) & "Sp" & ChrW(12) & "Sp" & ChrW(13) & "Sp" & ChrW(14) & "Sp" & ChrW(15) & "Sp" & ChrW(16) & "Sp" & ChrW(17) & "Sp" & ChrW(18) & "Sp" & ChrW(19) & "Sp" & ChrW(20) & "Sp" & ChrW(21) & "Sp" & ChrW(22) & "Sp" & ChrW(23) & "Sp" & ChrW(24) & "Sp" & ChrW(25) & "Sp" & ChrW(26) & "Sp" & ChrW(27) & "Sp" & ChrW(28) & "Sp" & ChrW(29) & "Sp" & ChrW(30) & "Sp" & ChrW(31) & "Sp" & ChrW(32) & "Sp" & ChrW(33) & "Sp" & ChrW(34) & "Sp" & ChrW(35) & "Sp" & ChrW(36) & "Sp" & ChrW(37) & "Sp" & ChrW(38) & "Sp" & ChrW(39) & "Sp" & ChrW(40) & "Sp" & ChrW(41) & "Sp" & ChrW(42) & "Sp" & ChrW(43) & "Sp" & ChrW(44) & "Sp" & ChrW(45) & "Sp" & ChrW(46) & "Sp" & ChrW(47) & "Sp" & ChrW(48) & "Sp" & ChrW(49) & "Sp" & ChrW(50) & _
"Sp" & ChrW(51) & "Sp" & ChrW(52) & "Sp" & ChrW(53) & "Sp" & ChrW(54) & "Sp" & ChrW(55) & "Sp" & ChrW(56) & "Sp" & ChrW(57) & "Sp" & ChrW(58) & "Sp" & ChrW(59) & "Sp" & ChrW(60) & "Sp" & ChrW(61) & "Sp" & ChrW(62) & "Sp" & ChrW(63) & "Sp" & ChrW(64) & "Sp" & ChrW(65) & "Sp" & ChrW(66) & "Sp" & ChrW(67) & "Sp" & ChrW(68) & "Sp" & ChrW(69) & "Sp" & ChrW(70) & "Sp" & ChrW(71) & "Sp" & ChrW(72) & "Sp" & ChrW(73) & "Sp" & ChrW(74) & "Sp" & ChrW(75) & "Sp" & ChrW(76) & "Sp" & ChrW(77) & "Sp" & ChrW(78) & "Sp" & ChrW(79) & "Sp" & ChrW(80) & "Sp" & ChrW(81) & "Sp" & ChrW(82) & "Sp" & ChrW(83) & "Sp" & ChrW(84) & "Sp" & ChrW(85) & "Sp" & ChrW(86) & "Sp" & ChrW(87) & "Sp" & ChrW(88) & "Sp" & ChrW(89) & "Sp" & ChrW(90) & "Sp" & ChrW(91) & "Sp" & ChrW(92) & "Sp" & ChrW(93) & "Sp" & ChrW(94) & "Sp" & ChrW(95) & "Sp" & ChrW(96) & "Sp" & ChrW(97) & "Sp" & ChrW(98) & "Sp" & ChrW(99) & "Sp" & ChrW(100) & _
"Sp" & ChrW(101) & "Sp" & ChrW(102) & "Sp" & ChrW(103) & "Sp" & ChrW(104) & "Sp" & ChrW(105) & "Sp" & ChrW(106) & "Sp" & ChrW(107) & "Sp" & ChrW(108) & "Sp" & ChrW(109) & "Sp" & ChrW(110) & "Sp" & ChrW(111) & "Sp" & ChrW(112) & "Sp" & ChrW(113) & "Sp" & ChrW(114) & "Sp" & ChrW(115) & "Sp" & ChrW(116) & "Sp" & ChrW(117) & "Sp" & ChrW(118) & "Sp" & ChrW(119) & "Sp" & ChrW(120) & "Sp" & ChrW(121) & "Sp" & ChrW(122) & "Sp" & ChrW(123) & "Sp" & ChrW(124) & "Sp" & ChrW(125) & "Sp" & ChrW(126) & "Sp" & ChrW(127) & "Sp" & ChrW(128) & "Sp" & ChrW(129) & "Sp" & ChrW(130) & "Sp" & ChrW(131) & "Sp" & ChrW(132) & "Sp" & ChrW(133) & "Sp" & ChrW(134) & "Sp" & ChrW(135) & "Sp" & ChrW(136) & "Sp" & ChrW(137) & "Sp" & ChrW(138) & "Sp" & ChrW(139) & "Sp" & ChrW(140) & "Sp" & ChrW(141) & "Sp" & ChrW(142) & "Sp" & ChrW(143) & "Sp" & ChrW(144) & "Sp" & ChrW(145) & "Sp" & ChrW(146) & "Sp" & ChrW(147) & "Sp" & ChrW(148) & "Sp" & ChrW(149) & "Sp" & ChrW(150) & _
"Sp" & ChrW(151) & "Sp" & ChrW(152) & "Sp" & ChrW(153) & "Sp" & ChrW(154) & "Sp" & ChrW(155) & "Sp" & ChrW(156) & "Sp" & ChrW(157) & "Sp" & ChrW(158) & "Sp" & ChrW(159) & "Sp" & ChrW(160) & "Sp" & ChrW(161) & "Sp" & ChrW(162) & "Sp" & ChrW(163) & "Sp" & ChrW(164) & "Sp" & ChrW(165) & "Sp" & ChrW(166) & "Sp" & ChrW(167) & "Sp" & ChrW(168) & "Sp" & ChrW(169) & "Sp" & ChrW(170) & "Sp" & ChrW(171) & "Sp" & ChrW(172) & "Sp" & ChrW(173) & "Sp" & ChrW(174) & "Sp" & ChrW(175) & "Sp" & ChrW(176) & "Sp" & ChrW(177) & "Sp" & ChrW(178) & "Sp" & ChrW(179) & "Sp" & ChrW(180) & "Sp" & ChrW(181) & "Sp" & ChrW(182) & "Sp" & ChrW(183) & "Sp" & ChrW(184) & "Sp" & ChrW(185) & "Sp" & ChrW(186) & "Sp" & ChrW(187) & "Sp" & ChrW(188) & "Sp" & ChrW(189) & "Sp" & ChrW(190) & "Sp" & ChrW(191) & "Sp" & ChrW(192) & "Sp" & ChrW(193) & "Sp" & ChrW(194) & "Sp" & ChrW(195) & "Sp" & ChrW(196) & "Sp" & ChrW(197) & "Sp" & ChrW(198) & "Sp" & ChrW(199) & "Sp" & ChrW(200), "Sp"), 1, Evaluate("={89, 97, 115, 115, 101, 114}")), "")
End Sub
-
In support of this Thread
http://www.eileenslounge.com/viewtopic.php?f=30&t=35600
Code:
' If I don't need all characters, then I can simplify a bit
Sub ConvertBytesToString3()
Dim Es As String
Dim Spt() As String
Let Spt() = Split(ChrW(65) & "Sp" & ChrW(66) & "Sp" & ChrW(67) & "Sp" & ChrW(68) & "Sp" & ChrW(69) & "Sp" & ChrW(70) & "Sp" & ChrW(71) & "Sp" & ChrW(72) & "Sp" & ChrW(73) & "Sp" & ChrW(74) & "Sp" & ChrW(75) & "Sp" & ChrW(76) & "Sp" & ChrW(77) & "Sp" & ChrW(78) & "Sp" & ChrW(79) & "Sp" & ChrW(80) & "Sp" & ChrW(81) & "Sp" & ChrW(82) & "Sp" & ChrW(83) & "Sp" & ChrW(84) & "Sp" & ChrW(85) & "Sp" & ChrW(86) & "Sp" & ChrW(87) & "Sp" & ChrW(88) & "Sp" & ChrW(89) & "Sp" & ChrW(90) & "Sp" & ChrW(91) & "Sp" & ChrW(92) & "Sp" & ChrW(93) & "Sp" & ChrW(94) & "Sp" & ChrW(95) & "Sp" & ChrW(96) & "Sp" & ChrW(97) & "Sp" & ChrW(98) & "Sp" & ChrW(99) & "Sp" & ChrW(100) & _
"Sp" & ChrW(101) & "Sp" & ChrW(102) & "Sp" & ChrW(103) & "Sp" & ChrW(104) & "Sp" & ChrW(105) & "Sp" & ChrW(106) & "Sp" & ChrW(107) & "Sp" & ChrW(108) & "Sp" & ChrW(109) & "Sp" & ChrW(110) & "Sp" & ChrW(111) & "Sp" & ChrW(112) & "Sp" & ChrW(113) & "Sp" & ChrW(114) & "Sp" & ChrW(115) & "Sp" & ChrW(116) & "Sp" & ChrW(117) & "Sp" & ChrW(118) & "Sp" & ChrW(119) & "Sp" & ChrW(120) & "Sp" & ChrW(121) & "Sp" & ChrW(122) & "Sp" & ChrW(123) & "Sp" & ChrW(124) & "Sp" & ChrW(125) & "Sp" & ChrW(126), "Sp")
Let Es = Join(Application.Index(Spt(), 1, Evaluate("={89, 97, 115, 115, 101, 114}-64")), "")
' Or
Let Es = Join(Application.Index(Split(ChrW(65) & "Sp" & ChrW(66) & "Sp" & ChrW(67) & "Sp" & ChrW(68) & "Sp" & ChrW(69) & "Sp" & ChrW(70) & "Sp" & ChrW(71) & "Sp" & ChrW(72) & "Sp" & ChrW(73) & "Sp" & ChrW(74) & "Sp" & ChrW(75) & "Sp" & ChrW(76) & "Sp" & ChrW(77) & "Sp" & ChrW(78) & "Sp" & ChrW(79) & "Sp" & ChrW(80) & "Sp" & ChrW(81) & "Sp" & ChrW(82) & "Sp" & ChrW(83) & "Sp" & ChrW(84) & "Sp" & ChrW(85) & "Sp" & ChrW(86) & "Sp" & ChrW(87) & "Sp" & ChrW(88) & "Sp" & ChrW(89) & "Sp" & ChrW(90) & "Sp" & ChrW(91) & "Sp" & ChrW(92) & "Sp" & ChrW(93) & "Sp" & ChrW(94) & "Sp" & ChrW(95) & "Sp" & ChrW(96) & "Sp" & ChrW(97) & "Sp" & ChrW(98) & "Sp" & ChrW(99) & "Sp" & ChrW(100) & _
"Sp" & ChrW(101) & "Sp" & ChrW(102) & "Sp" & ChrW(103) & "Sp" & ChrW(104) & "Sp" & ChrW(105) & "Sp" & ChrW(106) & "Sp" & ChrW(107) & "Sp" & ChrW(108) & "Sp" & ChrW(109) & "Sp" & ChrW(110) & "Sp" & ChrW(111) & "Sp" & ChrW(112) & "Sp" & ChrW(113) & "Sp" & ChrW(114) & "Sp" & ChrW(115) & "Sp" & ChrW(116) & "Sp" & ChrW(117) & "Sp" & ChrW(118) & "Sp" & ChrW(119) & "Sp" & ChrW(120) & "Sp" & ChrW(121) & "Sp" & ChrW(122) & "Sp" & ChrW(123) & "Sp" & ChrW(124) & "Sp" & ChrW(125) & "Sp" & ChrW(126), "Sp"), 1, Evaluate("={89, 97, 115, 115, 101, 114}-64")), "")
End Sub
Sub ConvertBytesToString4()
Dim Es As String
Dim Splat() As Variant
Let Splat() = Array(ChrW(65), ChrW(66), ChrW(67), ChrW(68), ChrW(69), ChrW(70), ChrW(71), ChrW(72), ChrW(73), ChrW(74), ChrW(75), ChrW(76), ChrW(77), ChrW(78), ChrW(79), ChrW(80), ChrW(81), ChrW(82), ChrW(83), ChrW(84), ChrW(85), ChrW(86), ChrW(87), ChrW(88), ChrW(89), ChrW(90), ChrW(91), ChrW(92), ChrW(93), ChrW(94), ChrW(95), ChrW(96), ChrW(97), ChrW(98), ChrW(99), ChrW(100), ChrW(101), ChrW(102), ChrW(103), ChrW(104), ChrW(105), ChrW(106), ChrW(107), ChrW(108), ChrW(109), ChrW(110), ChrW(111), ChrW(112), ChrW(113), ChrW(114), ChrW(115), ChrW(116), ChrW(117), ChrW(118), ChrW(119), ChrW(120), ChrW(121), ChrW(122), ChrW(123), ChrW(124), ChrW(125), ChrW(126))
Let Es = Join(Application.Index(Splat(), 1, Evaluate("={89, 97, 115, 115, 101, 114}-64")), "")
' Or
Let Es = Join(Application.Index(Array(ChrW(65), ChrW(66), ChrW(67), ChrW(68), ChrW(69), ChrW(70), ChrW(71), ChrW(72), ChrW(73), ChrW(74), ChrW(75), ChrW(76), ChrW(77), ChrW(78), ChrW(79), ChrW(80), ChrW(81), ChrW(82), ChrW(83), ChrW(84), ChrW(85), ChrW(86), ChrW(87), ChrW(88), ChrW(89), ChrW(90), ChrW(91), ChrW(92), ChrW(93), ChrW(94), ChrW(95), ChrW(96), ChrW(97), ChrW(98), ChrW(99), ChrW(100), ChrW(101), ChrW(102), ChrW(103), ChrW(104), ChrW(105), ChrW(106), ChrW(107), ChrW(108), ChrW(109), ChrW(110), ChrW(111), ChrW(112), ChrW(113), ChrW(114), ChrW(115), ChrW(116), ChrW(117), ChrW(118), ChrW(119), ChrW(120), ChrW(121), ChrW(122), ChrW(123), ChrW(124), ChrW(125), ChrW(126)), 1, Evaluate("={89, 97, 115, 115, 101, 114}-64")), "")
End Sub
-
In support of this Thread
http://www.eileenslounge.com/viewtopic.php?f=30&t=35600
Code:
Sub MakeSomeStringsToCopyAndPasteIntoACode()
Dim CodeText As String
Dim Cnt As Long
Rem 1 For an ASCII array from Split
'1a) Spaces : Note: PROBLEM* If you use the Split way, then best is to avoid using a single character as the separator. Otherwise you may have problems if you want that character in your Horizontal Array of ASCII characters because it will be seen as a separator for Split. This means that you will not get that character in your list. Instead you will have 2 extra empty elements in your array, and all characters after where the character ( here the space) should have been will appear offset by one place to the right in the horizontal array
For Cnt = 1 To 200
Let CodeText = CodeText & " & "" "" & ChrW(" & Cnt & ")"
Next Cnt
Let CodeText = Mid(CodeText, 10) ' take of first 9 bits of Space&Space"Space"Space&Space
Debug.Print CodeText
Debug.Print
Let CodeText = "" ' Empty so that i can use the varable again below
'1b) Use any 2 characters as the seperator to avoid PROBLEM*
For Cnt = 1 To 200
Let CodeText = CodeText & " & ""Sp"" & ChrW(" & Cnt & ")"
Next Cnt
Let CodeText = Mid(CodeText, 11) ' take of first 10 bits of Space&Space"Sp"Space&Space
Debug.Print CodeText
Debug.Print
Rem 2 For ASCII array from VBA Array( ) function
Let CodeText = "" ' Empty so that i can use the varable again below
For Cnt = 1 To 200
Let CodeText = CodeText & ", ChrW(" & Cnt & ")"
Next Cnt
Let CodeText = Mid(CodeText, 3) ' take off the first two characters " ,"
Debug.Print CodeText
Debug.Print
End Sub
' http://www.eileenslounge.com/viewtopic.php?f=30&t=35600
-
-
Some extra notes for a few Posts
http://www.eileenslounge.com/viewtop...278892#p278892
https://www.myonlinetraininghub.com/...andling-in-vba
https://excelfox.com/forum/showthrea...GoRoT-N0Nula-1
https://excelfox.com/forum/showthrea...0559#post10559
On Error GoTo -1 is not equivalent of using Err.Clear. It does ( also) clear the error object, (equivalent of using Err.Clear ).
On Error GoTo -1 takes Excel out of the so called “exception state”. It also does clear the Err object registers, (equivalent of using Err.Clear ). But the later is secondary to its main function of “clearing the exception”.
The next macro has 3 identical erroring code lines . Just before each error we have an error handler, which we might expect would trap the error following it . All three error handlers are similar and are of the type On Error GoTo [LABEL] But we find that only the first two error handlers work….
In this macro the first and the second error handlers, of the type On Error GoTo [LABEL] are enabled, and so when an error occurs the coding jumps to the appropriate Label
The second Error handler would not have worked, that is to say the second error would not have been trapped without the code line of On Error GoTo -1 . On Error GoTo -1 has cleared the exception state.
The third error handler, also of the type On Error GoTo [LABEL], does not work. It does not work, that is to say the error is not trapped , because we are in the exception state. One of the characteristics of the exception state is that any attempt to enable an error handler will be ignored. Another characteristic of the exception state is that any enabled error handler, ( in this case the second one ) , will also be ignored.
It is also sometimes said in this situation that the second error handler is active and is still handling the second error. It cannot handle another error , and any further errors will be handled by the VBA default error handler
Code:
Sub OnErrorGoToMinus1_takes_Excel_out_of_the_so_called_exception_state() ' It also does clear the Err object registers, (equivalent of using Err.Clear ). But the later is secondary to its main function of "clearing the exception"
Dim Rslt As Double
On Error GoTo ErrHndlr1
Let Rslt = 1 / 0 ' This error gets trapped by ErrHndlr1
MsgBox Prompt:="You will never see this", Title:="You will never see this"
Exit Sub ' You will never come here in this demo macro, but its good practice to get in the habit of always doing this exit sub
ErrHndlr1:
Debug.Print Err.Number & vbCr & vbLf & Err.Description ' 11 Division durch Null
On Error GoTo -1 ' the next line will give us no error infomation because the On Error GoTo -1 has cleared the Err object registers
Debug.Print Err.Number & vbCr & vbLf & Err.Description ' 0
On Error GoTo ErrHndlr2 ' the main function of On Error GoTo -1 is to "clear the exception" which means this second error hanhler will work
Let Rslt = 1 / 0 ' This error gets trapped by ErrHndlr2
MsgBox Prompt:="You will never see this", Title:="You will never see this"
Exit Sub ' You will never come here in this demo macro, but its good practice to get in the habit of always doing this exit sub
ErrHndlr2:
Debug.Print Err.Number & vbCr & vbLf & Err.Description ' 11 Division durch Null
' I will not do On Error GoTo -1 and see what happens...
On Error GoTo ErrHndlr3
Let Rslt = 1 / 0 ' This will be handled by the VBA default error handler: The error will not be trapped by the second error handler , ErrHndlr2
MsgBox Prompt:="You will never see this", Title:="You will never see this"
Exit Sub ' You will never come here in this demo macro, but its good practice to get in the habit of always doing this exit sub
ErrHndlr3:
' You will never come here. The third error is not trapped: It will be handled by the VBA default error handler
End Sub
The following other error things also , in addition to their main function, clear the Err object registers –
_ On Error GoTo 0 ,
_ changing the error handler
_ Resume, ( Resume; Resume Next; Resume [label] )
-.....see next post
-
Some extra notes for a few Posts
http://www.eileenslounge.com/viewtop...278892#p278892
https://www.myonlinetraininghub.com/...andling-in-vba
https://excelfox.com/forum/showthrea...GoRoT-N0Nula-1
https://excelfox.com/forum/showthrea...0559#post10559
_.....continued from last post
The following other error things also , in addition to their main function, clear the Err object registers –
_ On Error GoTo 0 ,
_ changing the error handler
_ Resume, ( Resume; Resume Next; Resume [label] ) ,
Here are 5 demos
1x On Error GoTo 0
1x changing the error handler
3x Resume, ( 1xResume; 1xResume Next; 1xResume [label] )
Code:
Sub OnErrorGoTo0ClearsErr() ' _ On Error GoTo 0
Dim Rslt As Double
On Error Resume Next ' In simple terms this allows the code to contiunue as if no error had occured. It is not quite that simple, for example, the Err and Error are filled
Debug.Print Err & vbCr & vbLf & Error ' gives 0 The default Err property is the error number, so in this situation Err is taken as Err.number The exact working of Error is unclear, but if an error occurs it seems to do a few things, and one of them is that it returns the error description
Let Rslt = 1 / 0
Debug.Print Err & vbCr & vbLf & Error ' gives 11 Division durch Null
On Error GoTo 0 ' The main purpose of this is to disable our error handler and return to the default VBA error handler. As a secodary function it seems to clear the Err registers
Debug.Print Err & vbCr & vbLf & Error ' gives 0
End Sub
Sub ChangingTheErrorHandlerClearsErr() ' _ changing the error handler
Dim Rslt As Double
On Error Resume Next ' In simple terms this allows the code to contiunue as if no error had occured. It is not quite that simple, for example, the Err and Error are filled
Debug.Print Err & vbCr & vbLf & Error ' gives 0 The default Err property is the error number, so in this situation Err is taken as Err.number The exact working of Error is unclear, but if an error occurs it seems to do a few things, and one of them is that it returns the error description
Let Rslt = 1 / 0
Debug.Print Err & vbCr & vbLf & Error ' gives 11 Division durch Null
On Error Resume Next
Debug.Print Err & vbCr & vbLf & Error ' gives 0 because I have changed the error handler , ( admitedly in this case changed it to the same type )
Let Rslt = 1 / 0
Debug.Print Err & vbCr & vbLf & Error ' gives 11 Division durch Null
On Error GoTo Bed
Debug.Print Err & vbCr & vbLf & Error ' gives 0 because I have changed the error handler
Let Rslt = 1 / 0
Exit Sub ' I don't need this since i never come here, but its good practice to get in the habit of having this above a typical Error handling code section.
Bed:
Debug.Print Err & vbCr & vbLf & Error ' gives 11 Division durch Null
End Sub
Sub Resume_ClearsErr() ' _ Resume, ( Resume )
Dim Rslt As Double, Demonostrator As Long
On Error GoTo ErrHndler ' In simple terms this tells VBA to go the the label, ErrHndler Note however that if an error causes me to go there, then I will then be in the exception state.
Debug.Print Err & vbCr & vbLf & Error ' gives 0 The default Err property is the error number, so in this situation Err is taken as Err.number The exact working of Error is unclear, but if an error occurs it seems to do a few things, and one of them is that it returns the error description
Let Rslt = 1 / Demonostrator ' Initially this causes me to go to ErrHndler but then the Resume brings me back to re try this code line
Debug.Print Err & vbCr & vbLf & Error ' gives 0 The Resume cleared the Err registers
Exit Sub
ErrHndler: ' Start of a what is commonly called an "error handling code section"
Debug.Print Err & vbCr & vbLf & Error ' gives 11 Division durch Null The Err register is always filled in all situatiuons when an error occurs, regardless of what error handler is or isn't in place.
Let Demonostrator = 1 ' It is important to cure the problem causing the error here, or otherwise the next code line will cause an infinite loop because the next code line instructs VBA to go back and try the erroring code line again. Note also that the Resume in the next code line also clears the error exception and clears the Err registers
Resume ' This clears the exception, clears the Err registers, and instructs VBA to go back to the code line that errored and try again. Because it instructs VBA to go back and try the erroring code line again, It is important to cure the problem causing the error before this code line, or else we will have an infinite loop
End Sub
Sub Resume_Next_ClearsErr() ' 'Resume, ( Resume Next )
Dim Rslt As Double
On Error GoTo ErrHndler ' In simple terms this tells VBA to go the the label, ErrHndler Note however that if an error causes me to go there, then I will then be in the exception state.
Debug.Print Err & vbCr & vbLf & Error ' gives 0 The default Err property is the error number, so in this situation Err is taken as Err.number The exact working of Error is unclear, but if an error occurs it seems to do a few things, and one of them is that it returns the error description
Let Rslt = 1 / 0 ' This causes me to go to ErrHndler The Resume Next brings me back to just after this code line
Debug.Print Err & vbCr & vbLf & Error ' gives 0 The Resume Next cleared the Err registers
Exit Sub
ErrHndler: ' Start of a what is commonly called an "error handling code section"
Debug.Print Err & vbCr & vbLf & Error ' gives 11 Division durch Null The Err register is always filled in all situatiuons when an error occurs, regardless of what error handler is or isn't in place.
Resume Next ' This clears the exception, clears the Err registers, and instructs VBA to go back to the code line just after that code line that errored
End Sub
Sub Resume_LABEL_ClearsErr() ' 'Resume, ( Resume [label] )
Dim Rslt As Double
On Error GoTo ErrHndler ' In simple terms this tells VBA to go the the label, ErrHndler Note however that if an error causes me to go there, then I will then be in the exception state.
Debug.Print Err & vbCr & vbLf & Error ' gives 0 The default Err property is the error number, so in this situation Err is taken as Err.number The exact working of Error is unclear, but if an error occurs it seems to do a few things, and one of them is that it returns the error description
Let Rslt = 1 / 0 ' This causes me to go to ErrHndler The Resume Lbl brings me back to just after the label, Lbl:
Lbl:
Debug.Print Err & vbCr & vbLf & Error ' gives 0 The Resume Lbl cleared the Err registers
Exit Sub
ErrHndler: ' Start of what is commonly called an "error handling code section"
Debug.Print Err & vbCr & vbLf & Error ' gives 11 Division durch Null The Err register is always filled in all situatiuons when an error occurs, regardless of what error handler is or isn't in place.
Resume Lbl ' This clears the exception, clears the Err registers, and instructs VBA to go to the code line just after the label Lbl:
End Sub
If we use On Error GoTo [LABEL] and then use Resume ( Resume; Resume Next; Resume [label] ) then we appear to be having something done very similar, or possibly exactly the same as On Error GoTo -1 , since the exception state is cleared and the Err object is cleared.
To demonstrate this we can do the last three routines again, and simply add another error handler , for example On Error Resume Next , after the return point, and follow this by an error. If the error is handled, that is to say we get no default VBA error message, then we know that the exception had been cleared previously. If it had not been cleared then the new error handler, On Error Resume Next , would have been ignored and we would have seen the default VBA error handler warning pop up message.
_..... continued in next post
-
Some extra notes for a few Posts
http://www.eileenslounge.com/viewtop...278892#p278892
https://www.myonlinetraininghub.com/...andling-in-vba
https://excelfox.com/forum/showthrea...GoRoT-N0Nula-1
https://excelfox.com/forum/showthrea...0559#post10559
_.... from last post
If we use On Error GoTo [LABEL] and then use Resume ( Resume; Resume Next; Resume [label] ) then we appear to be having something done very similar, or possibly exactly the same as On Error GoTo -1 , since the exception state is cleared and the Err object is cleared.
To demonstrate this we can do the last three routines again, and simply add another error handler , for example On Error Resume Next , after the return point, and follow this by an error. If the error is handled, that is to say we get no default VBA error message, then we know that the exception had been cleared previously. If it had not been cleared then the new error handler, On Error Resume Next , would have been ignored and we would have seen the default VBA error handler warning pop up message.
Code:
' If we use On Error GoTo [LABEL] and then use Resume ( Resume; Resume Next; Resume [label] ) then we appear to be having something done very similar, or possibly exactly the same as On Error GoTo -1 , since the exception state is cleared and the Err object is cleared.
Sub Resume_ClearsErr_() ' _ Resume, ( Resume )
Dim Rslt As Double, Demonostrator As Long
On Error GoTo ErrHndler
Debug.Print Err & vbCr & vbLf & Error
Let Rslt = 1 / Demonostrator
Debug.Print Err & vbCr & vbLf & Error
On Error Resume Next ' This would be ignored if I was in exception state.
Let Rslt = 1 / 0 ' This does not give us an error , so the last code line worked, indicating that we were not in exception state
On Error GoTo 0 ' I do not need to do this since I am Exiting Sub in next code line. But it is good practice to get in the habit of doing this to return to normnal default VBA error handling if i know i am finished using the Error handler which I enabled
Exit Sub
ErrHndler:
Debug.Print Err & vbCr & vbLf & Error
Let Demonostrator = 1
Resume
End Sub
Sub Resume_Next_ClearsErr_() ' 'Resume, ( Resume Next )
Dim Rslt As Double
On Error GoTo ErrHndler
Debug.Print Err & vbCr & vbLf & Error
Let Rslt = 1 / 0
Debug.Print Err & vbCr & vbLf & Error
On Error Resume Next ' This would be ignored if I was in exception state.
Let Rslt = 1 / 0 ' This does not give us an error , so the last code line worked, indicating that we were not in exception state
On Error GoTo 0 ' I do not need to do this since I am Exiting Sub in next code line. But it is good practice to get in the habit of doing this to return to normnal default VBA error handling if i know i am finished using the Error handler which I enabled
Exit Sub
ErrHndler:
Debug.Print Err & vbCr & vbLf & Error
Resume Next
End Sub
Sub Resume_LABEL_ClearsErr_() ' 'Resume, ( Resume [label] )
Dim Rslt As Double
On Error GoTo ErrHndler
Debug.Print Err & vbCr & vbLf & Error
Let Rslt = 1 / 0
Lbl:
Debug.Print Err & vbCr & vbLf & Error
On Error Resume Next ' This would be ignored if I was in exception state.
Let Rslt = 1 / 0 ' This does not give us an error , so the last code line worked, indicating that we were not in exception state
On Error GoTo 0 ' I do not need to do this since I am Exiting Sub in next code line. But it is good practice to get in the habit of doing this to return to normnal default VBA error handling if i know i am finished using the Error handler which I enabled
Exit Sub
ErrHndler:
Debug.Print Err & vbCr & vbLf & Error
Resume Lbl
End Sub
-
Some extra notes for a few Posts
http://www.eileenslounge.com/viewtop...278892#p278892
https://www.myonlinetraininghub.com/...andling-in-vba
https://excelfox.com/forum/showthrea...GoRoT-N0Nula-1
https://excelfox.com/forum/showthrea...0559#post10559
As far as I can tell , the Err object is always filled with information about the last error that occurred, and it seems to me that its sole purpose is to have information about the last error. It can be cleared with Err.Clear , and , is also cleared as a secondary function of other things, including On Error GoTo -1
( In fact it appears the Err is actually a function or an object, possibly working like something similar to Range(xx) which can be regarded as an object or property or function depending on how you use it. We can probably say that Err is a function which returns the Err object. I think that possibly Error is also a similar function. I am not sure exactly what it does, but one thing it does is return the same as Err.Decription, so it can be used in place of Err.Description )
The main purpose of On Error GoTo -1 is to take Excel out of the exception state. The exception state is generally caused by an error occurring. An exception to this being , possibly, of when On Error Resume Next is used: But this is not clear to anyone, as far as I can tell: Its not clear whether
either:
On Error Resume Next prevents the excepting state occurring
or
On Error Resume Next cause the exception state to be cleared immediately after an error occurs.
If On Error Resume Next is used and an error occurs, then something similar to doing On Error GoTo -1 happens. But it is not exactly the same, since the Err object is not cleared, as it is by On Error GoTo -1
Code:
Sub OnErrorResumeNext() ' If On Error Resume Next is used and an error occurs, then something similar to doing On Error GoTo -1 happens. But it is not exactly the same, since the Err object is not cleared, as it is by On Error GoTo -1
Dim Rslt As Double
On Error Resume Next
Let Rslt = 1 / 0 ' It is generally thought that we are not in the exception state, but the next line does tell us what error occured, so the On Error Resume Next has not simply done a On Error GoTo -1 , since On Error GoTo -1 would have resulted inj the Err object being cleared which would mean that the next code line retuned us 0
Debug.Print Err & vbCr & vbLf & Error ' gives 11 Division durch Null The Err register is always filled in all situatiuons when an error occurs, regardless of what error handler is or isn't in place.
On Error GoTo Bed
Let Rslt = 1 / 0 ' We do not get a VBA default error here. We go to Bed: So the error handler worked indicating that we were not in the exception state
On Error GoTo 0 ' I do not need this or the next code line, but its good to get into the habit of turning off any used error handler and having an Exit Sub above a typiucal endind error handling code section
Exit Sub
Bed:
Debug.Print Err & vbCr & vbLf & Error ' gives 11 Division durch Null The Err register is always filled in all situatiuons when an error occurs, regardless of what error handler is or isn't in place.
End Sub
-
Some extra notes for a few Posts
http://www.eileenslounge.com/viewtop...278892#p278892
https://www.myonlinetraininghub.com/...andling-in-vba
https://excelfox.com/forum/showthrea...GoRoT-N0Nula-1
https://excelfox.com/forum/showthrea...0559#post10559
The Exceptions State
The concept of the exception state is rarely understood.
The most noticeable effect of the error state is that further errors are dealt with by the default VBA error handling. It’s not relevant whether we are in the so called “error handling block” or not.
This frequently catches people out, in particular in a loop situation when error handling only works once, when they had been expecting it to trap all errors occurring: In the exception state, any enabled error handler won’t work again, and any attempt to use / enable another will be ignored.
( In this exception state, the On Error statement , On Error GoTo 0 , would do its main job of disabling any enabled error handler, but it won’t have any effect directly on anything, because it doesn’t clear the exception state. Its effect would only be noticed if the exception was cleared).
See here:
http://www.eileenslounge.com/viewtop...278892#p278892
http://www.eileenslounge.com/viewtop...278909#p278909
http://www.eileenslounge.com/viewtop...278922#p278922
