this equation: a+(13*b/c)+d+(12*e)-f+(g*h/i)=87 appears when trying solve the maths puzzle vietnamese eight-year-olds became viral on internet. in mathematics, such equation called underdetermined system. of course has more 1 solution , brute force method seems easiest way find of solutions.
i'm interested in knowing how solve equation using vba , present solutions in ms excel worksheet, since can't find way make such program due lack of vba programming knowledge.
i'm aware of similar posts on stack overflow this , this answers there not me much.
here attempt:
sub vietnam_problem() dim starttime double starttime = timer j = 2 'initial value number of rows = 1 9 b = 1 9 c = 1 9 d = 1 9 e = 1 9 f = 1 9 g = 1 9 h = 1 9 = 1 9 if <> b , <> c , <> d , <> e , <> f , <> g , <> h , <> , b <> c , b <> d , b <> e , b <> f , b <> g , b <> h , b <> , c <> d , c <> e , c <> f , c <> g , c <> h , c <> , d <> e , d <> f , d <> g , d <> h , d <> , e <> f , e <> g , e <> h , e <> , f <> g , f <> h , f <> , g <> h , g <> , h <> , + (13 * b / c) + d + (12 * e) - f + (g * h / i) = 87 cells(j, 1) = cells(j, 2) = b cells(j, 3) = c cells(j, 4) = d cells(j, 5) = e cells(j, 6) = f cells(j, 7) = g cells(j, 8) = h cells(j, 9) = j = j + 1 end if next next h next g next f next e next d next c next b next cells(2, 11) = j - 2 'number of solutions cells(2, 12) = round(timer - starttime, 2) 'running time of vba code end sub it seems work it's not nice , slow.
anastasiya-romanova 秀, since not declaring variables (a through j), code running variables defaulting variant type. while variants can enormously useful, should not used here.
i ran code unaltered , on machine, took 851 seconds complete.
since vba optimized longs, adding 1 line code declare variables (a through j) longs, brought running time on machine down 120 seconds. that's 7 times faster using appropriate variable type!
my stab @ solving puzzle in vba runs considerably faster. in fact, it's faster (and shorter) posted far on page. on same machine, returns 136 correct combinations in less 1 second.
there lot of nonsense out there (the world, net, here on page!) vba being slow. don't believe it. sure, compiled languages can faster, of time comes down how know how handle language. i've been programming in basic language since 1970s.
here solution vietnam puzzle crafted question. please place in new code module:
option explicit private z long, v variant public sub vietnam() dim s string s = "123456789" redim v(1 200, 1 9) call filterpermutations("", s) [a1:i200] = v end end sub private sub filterpermutations(s1 string, s2 string) dim long, b long, c long, d long, e long, f long, _ g long, h long, long, j long, m long, n long n = len(s2) if n < 2 = mid$(s1, 1, 1): b = mid$(s1, 2, 1): c = mid$(s1, 3, 1) d = mid$(s1, 4, 1): e = mid$(s1, 5, 1): f = mid$(s1, 6, 1) g = mid$(s1, 7, 1): h = mid$(s1, 8, 1): = s2 if + (13 * b / c) + d + (12 * e) - f + (g * h / i) = 87 z = z + 1 v(z, 1) = a: v(z, 2) = b: v(z, 3) = c v(z, 4) = d: v(z, 5) = e: v(z, 6) = f v(z, 7) = g: v(z, 8) = h: v(z, 9) = end if else m = 1 n filterpermutations s1 + mid$(s2, m, 1), left$(s2, m - 1) + right$(s2, n - m) next end if end sub method #2:
anastasiya, try explain later today, when have more time. in meantime, please examine next stab @ this. shorter , completes in 1/10th of second. using heap's permutation algorithm:
option explicit private z long, v variant public sub vietnamheap() dim a(0 8) long a(0) = 1: a(1) = 2: a(2) = 3: a(3) = 4: a(4) = 5: a(5) = 6: a(6) = 7: a(7) = 8: a(8) = 9 redim v(1 200, 1 9) generate 9, [a1:i200] = v end end sub sub generate(n long, a() long) dim t long, long if n = 1 if a(0) + (13 * a(1) / a(2)) + a(3) + (12 * a(4)) - a(5) + (a(6) * a(7) / a(8)) = 87 z = z + 1 = 1 9: v(z, i) = a(i - 1): next end if else = 0 n - 2 generate n - 1, if n mod 2 = 1 t = a(0): a(0) = a(n - 1): a(n - 1) = t else t = a(i): a(i) = a(n - 1): a(n - 1) = t end if next generate n - 1, end if end sub method #3
and here shorter version. can come either shorter version or quicker version?
const q = 9 dim z long, v(1 999, 1 q) public sub vietnamheap() dim a(1 q) long z = 1 q: a(z) = z: next: z = 0 gen q, [a1].resize(ubound(v), q) = v: end end sub sub gen(n long, a() long) dim long, k long, t long if n > 1 = 1 n - 1 gen n - 1, if n mod 2 = 1 k = 1 else k = t = a(k): a(k) = a(n): a(n) = t next gen n - 1, else if 87 = a(1) + 13 * a(2) / a(3) + a(4) + 12 * a(5) - a(6) + a(7) * a(8) / a(9) z = z + 1: = 1 q: v(z, i) = a(i): next end if end sub 
Comments
Post a Comment