Monday, July 22, 2019

Excel VBA : General Purpose Linear Equation Solver

So simple, I'm surprised this one's not there online someplace already. If you read my older post, you might wonder why this one doesn't need the ".Transpose" thingie. Answer - coz you're feeding it a column vector and Excel is smart enough - for a change :)

Give these a quick browse first for the why - unless you know why already :)
https://stackoverflow.com/questions/23443361/matrix-math-with-vba-system-of-linear-equations
https://www.ozgrid.com/forum/forum/help-forums/excel-general/12863-solve-linear-equations

Point : if you know what you're doing, you have your coefficients in one matrix and the right-hand-side in a column vector and you want to fire a weapon..


You have to select a bunch of cells, enter the formula and hit CTRL-SHIFT-ENTER (see older post)

But, this function could be used within VBA itself - these pictures are just to show what you get.. You don't *have* to use it this way putting the inputs and outputs in cells..

Function solve_lin(coeff As Variant, RHS As Variant) As Variant
    Dim retVal As Variant
   
    retVal = Application.WorksheetFunction.MMult(Application.WorksheetFunction.MInverse(coeff), RHS)
   
    solve_lin = retVal

End Function

Or, since you're most likely to send it a row vector :)
Function solve_lin(coeff As Variant, RHS As Variant) As Variant
    Dim retVal As Variant
   
    retVal = Application.WorksheetFunction.MMult(Application.WorksheetFunction.MInverse(coeff), Application.WorksheetFunction.Transpose(RHS))
   
    solve_lin = retVal
End Function

Excel VBA : Returning a Row Vector and a Column Vector

I want to select four cells in a column and send an array from a function to fill them. How?

See example code below
  1. Select four cells
  2. Start typing "= retArray()" without the quotes of course.
  3. Now, instead of pressing ENTER, press CTRL-SHIFT-ENTER
The code :

Option Explicit
Function retArray() As Variant
    Dim retVal(4) As Integer
   
    retVal(0) = 1
    retVal(1) = 3
    retVal(2) = 4
    retVal(3) = 0
   
    retArray = Application.Transpose(retVal)

End Function

How are you supposed to know to use transpose? Good q.

If you want to write to four cells that are all in the same row, i.e., send a row vector, then you don't need the transpose. Think of it this way - you pick up a book on programming and they get to arrays. Are they going to talk about matrices - in which case the concept of a column vector arises? Nope - they put the entire [a b c.. z] on one line. Moral : the default is a row vector :)

Same thing for the row … and here's the code :

Option Explicit
Function retArray() As Variant
    Dim retVal(4) As Integer
   
    retVal(0) = 1
    retVal(1) = 3
    retVal(2) = 4
    retVal(3) = 0
   
    retArray = retVal

End Function

Kudos :
https://stackoverflow.com/questions/46915012/excel-vba-function-return-array-and-paste-in-worksheet-formula?rq=1
https://www.dummies.com/software/microsoft-office/excel/working-with-vba-functions-that-return-an-array-in-excel-2016/

Thursday, July 04, 2019

The Genius of Art Markman

Okay, maybe or maybe not :) But, this is a gem :

Archimedes … did you know what and then ran in the streets screaming Eureka!..

As this example reflects, solving a difficult problem feels good. In general, there is solid evidence that thinking quickly and successfully creates a positive mood. You probably feel good after having a great conversation with friends. One reason fast music tends to lift your mood is that you have to think quickly to follow along with it.

Zandman and Perski (Peres) - On the DNA of Success

At the age of 93, Shimon Peres was visited at his home by Yaakov Katz, author of "The Weapon Wizards" :

"There is something in our DNA that makes us Jews never feel satisfied. Give a Jew something and he will add to it or fix it. Give the air force a plane and they will add to it and change it.. We believe that anything is possible."



When Felix Zandman (Vishay Intertechnologies) started his first plant in Israel, he found :

"Israel may have been my spiritual homeland, but conducting business there was truly an experience with a different culture. As I found out almost immediately, Israelis are never content to leave well enough alone. They are an independent lot, inveterate tinkerers and innovators. They want to improve everything. No matter what it is, no matter how good it might be, they insist on making it better. There is no place in their heads for the truism that sometimes the enemy of good is better. I told them, 'Look, this is the resistor we produce in the US today. To be successful, we have to produce exactly the same thing here. I don't want you to make something better than this. I do not want you to improve it. Make it neither worse nor better. I want you to produce the same thing tomorrow as today. Once you know how to produce exactly what we stupid guys over there are producing, then we'll talk about producing better. But don't do it at the same time. Okay?'"

Earlier, during Hashoa, holed up in a hideout in a Polish home :

"... the great prodigy violinist from Grodno whom I had gone to hear just before the war. The smell and heat of the hole would fade away, and the Mendelsohn violin concerto with all its glorious details would fill my mind. Whatever I knew I listened to in my private concert hall. I kicked myself that I hadn't been a more serious student, that I hadn't learned ten times as much."

"Discussions of the holidays inevitably became discussions of morality. It began to dawn on me that our rules, Sender's rules, were enabling us to survive. It was no wonder that the Ten Commandments had been created; they gave people a way to live together. There was nothing abstract about moral rules. They were hard and simple precepts for survival. If we had started to have sex, or if we had started to cheat each other with food, or if we began to quarrel with each other, the results would be fatal. Janova would have to throw us out, or we would have killed each other. Jealousies and resentment and anger - we had to stamp all that down. The lesson was stark: You have the rules, you abide by them, you survive; otherwise you don't."


General Israel Tal:

"We as an army, as Israelis, and as Jews, cannot have tanks that make our soldiers expendable. We must protect our most precious resource. That is the backbone of our military commitment."

So when he started to design the new Merkava, his first priority had been protection. To assist with that he put the engine up front, rather than in back, where it is on the rest of the world's tanks. That switch put extra shielding forward and also opened up rear space for infantry soldiers. As a result, the Merkava was more than a tank: it could transport soldiers into battle or serve as an armored headquarters for a commander and his staff. With its unusual rear-hatch, the Merkava also could evacuate wounded from the field. Tal described for me his fire protection inventions, ammunition, storage departures, and his novel approach to armor design. These were startling innovations; it was obvious that a tremendous amount of new technology was going into this.

When he explained these things to me I said, "My God, you must have a tremendous scientific staff - just to come up with the concepts, let alone implement them." But no, it was a staff of one: Talik himself. As a career tankman and tank commander, he had had fighting experience with most of the world's leading tanks: American, British, French, even captured Soviet models. He had an insider's feel for what was wrong with all of them.

And this from a man who studied philosophy in college and taught himself engineering!


One of my favorite parts of the book :

The Haganah would make arrangements to get us to Israel. I packed my backpack and took the train to Paris. Before I joined the group, I stopped at Sender's to say goodbye. Sender wasn't there, but our close friend (and Sender's business associate) Josef Weiss was. Sender was off on a buying trip, he said. He wouldn't be back for a couple of days.

When I told Weiss why I was in Paris, he almost had a heart attack. He grabbed me and started talking and pleading. He didn't stop for the next thirty-six hours. "Felix, you can't do it. You absolutely cannot do it. You can't do it to Sender. You can't do it to yourself. You will break Sender's will if you do this, you will destroy him. If you go and get killed there, he will never recover. This is like a death sentence for him. You must think of your family, Felix!"

Weiss was hysterical. I couldn't get him to stop. "Josef," I said, "I have to go. Who else is going to defend the country if we don't? This is my duty. I have to do it."

"Felix, it's not just Sender. It's the memory of your family. You're the last one, Felix. You're the seed. Everyone is dead. All those children, everyone! You are the only survivor. You can't do it to the memory of your family. The seed of Zandman has to create a family. YOU CANNOT GO TO ISRAEL AND BE KILLED!"

Weiss kept talking and talking. The rendezvous time came and went. Then the time for my train to leave passed. There must be other groups going, I thought. I'll just have to hook up with one of those. I was exhausted from arguing, but Weiss's voice never stopped cajoling and wheedling and begging and ordering. I dropped off to sleep, but he shook me awake. Finally, I promised to stay until Sender got back.

Sender told me to finish my studies. "Look," I said, "the war won't last forever. I'll be there for a year, then I'll come back."

"No. If you drop out of your program, they won't take you back (which was true). Your engineering profession will be finished. You have to understand that."

"So I won't be an engineer. I'll work on a kibbutz."

"Well, maybe. But you know you'll be much better off being an engineer. You'll also be much more useful to Israel as an engineer than giving your life in this way. They're sending so many youngsters who don't have your capability, your possibilities to really help the country."

"Sender, these are all bubbe meises. I have to go."

In the end, I did not go. Instead, I went back to school. I was in love with school. I was in love with finishing my studies. But it felt hollow. I wanted to be in Israel. I had betrayed myself, and I knew it. I followed the news from Israel day by day.