Run-VBA-macro-another-spreadsheet

A simple example of how to run a VBA macro in another spreadsheetTo run a VBA macro in another spreadsheet, we use the function Application.Run(NomePlanilha!NomeMacro)

Some examples:

Application.Run ("PlotaVal.xlsb" & "!plotahora") 'Runs macro plotahora in the spreadsheet PlotaVal.xlsb
 
Application.Run ("PlotaVal.xlsb" & "!plotavalor(10)") 'Runs macro plotavalor with numeric parameter (10), in the spreadsheet PlotaVal.xlsb
 
Application.Run (strName & "!plotavalor(" & """" & "abc" & """" & ")") 'Runs macro plotavalor with string parameter "abc", in the spreadsheet PlotaVal.xlsb

A complete example: the macro in spreadsheet RodaMacroOutraPlanilha.xlsb runs a macro in the spreadsheet PlotaVal.xlsb.

Files are in github: https://github.com/asgunzi/Run-VBA-macro-another-spreadsheet

Sub rodaMacroOutraPlan()
 
Dim strName As String
 
Application.DisplayAlerts = False
  
strName = "PlotaVal.xlsb" 'Name of the spreadsheet
   
Workbooks.Open "C:\Testes\" & strName 'Open spreadsheet

'Runs it
'Application.Run (strName & "!plotahora") 'Runs without parameters
 
'Application.Run (strName & "!plotavalor(10)") 'Runs macro with numeric parameter (10)
Application.Run (strName & "!plotavalor(" & """" & "abc" & """" & ")") 'Runs macro with texto parameter (“abc”)
 

Workbooks(strName).Save 'Save
  
Workbooks(strName).Close 'Closes
 
End Sub

Macro in spreadsheet PlotaVal.xlsb

Sub plotaValor(val As String)
 
Range("a2") = val
 
End Sub
 
Sub plotaHora()
 
Range("a1") = DateTime.Now
 
End Sub
Advertisements

One must have chaos to be able to give birth to a dancing star

“I am a disciple of the prophet Dionysus ” — Friedrich Nietzsche

Information Technology recommendations on data structures: the more structured, the more clean and standardized, the better. It increases productivity, makes everything more organized. Standards diminish rework and confusion with data. Excel spreadsheets are the worst thing in the world, because the user will create columns, insert empty lines, change the header: it will be a mess.

This discourse seems perfect.

But, also as Dionysus’ disciple, I say NO.

  • The more chaotic the database, the better.
  • The dirtier, the better.
  • The more the user messes up, the better.

Apollo x Dionysus

The German philosopher Friedrich Nietzsche had the remarkable ability to combine philosophical ideas with a poetic style. One of his most famous metaphors is the contrast between the Greek gods Apollo and Dionysus.

Apollo is the god of the Sun, Harmony, Medicine. He represents the Order: the beautiful god, tall, strong, symmetrical, organized. He is the god of the arts, giving precise shapes to the sculptures, creating order in chaos.

Dionysus is the god of wine. He represents the Chaos. Fat, short, ugly, drunk, crooked, everything in him is bad. He is the ecstasy, the drunkenness. Born of hunger and pain, he reborns each spring and spreads joy wherever he goes.


Order x Chaos

The order seems better than the chaos. But the fact is that we can bring order only to a very small part of the world. The universe, infinitely greater, will never be known by humans.

I imagine the two gods as two arms: chaos and order, complementing each other. We should have the humility to recognize that there are premises that will always be out of any model .

I have a very strong Apollonian side, by my background in engineering, math Olympiads, etc. But I also have a very strong Dionysian side, which makes me very skeptical of everything that wants to organize too much, optimize too much, giving little room to the unforeseen.


Why I like chaotic data structures?

I work extensively with innovation, creating new tools, new processes, new ideas.

When working with a new project, the client don’t have the slightest idea of ​​what he wants. He knows only the symptom (say, analysts lose too much time to generate the report), and assumes that he knows the solution (automatically generate a report).

But is this the real problem? No one knows, this must be discovered.

Sometimes, he even does not need the report he thought he needed. Anyway, in 100% of cases, it’s useless to structure databases to try to solve efficiently the wrong problem.

As Peter Drucker says:

There is no greater waste than to solve with great efficiency a problem that didn’t needed to be solved.

The recommendation of structured databases is only for mature processes, those already established and that will change little.

For innovations, the more prototypes, the better. The more quick-wins, simple, fast, flexible and therefore chaotic, the better. The dirtier the excel, the better. The more the client messes up, the better.

No new music arises from the order. No new picture emerges of order. No new idea arises from the order. Only from chaos.

One must have chaos to be able to give birth to a dancing star” — Friedrich Nietzsche

More forgotten lore at: https://medium.com/@arnaldogunzi

Portuguese version: https://ideiasesquecidas.com

An abnormal man looking for an interesting number

After taking the picture below, from the building written “normal”, the abnormal guy of the photo — me — remembered an “interesting” paradox.

Suppose I list the natural numbers in order:

  • 1
  • 2
  • 3
  • 4

Now let’s say something interesting about each of these numbers:

  • 1 is the first number of all, it is divisor of all the others
  • 2 is the first and only prime number
  • 3 is the first odd cousin
  • 4 is the first perfect square

Let’s say the numbers with the an interesting property are called “interesting” numbers.
And the numbers that are not interesting are the “normal” numbers.

Using this definition, a list would look like this:

  • 1 is an interesting number
  • 2 is an interesting number
  • 3 is an interesting number
  • 4 is an interesting number

Now suppose the number x is the first “normal” number in the list.

  • 1 is an interesting number
  • 2 is an interesting number
  • 3 is an interesting number
  • 4 is an interesting number
  • x is a normal number

But if x is the first “normal” number, it is an “interesting” number because it has an interesting property: to be the first “normal” number.

On the other hand, if we consider x an “interesting” number by having the property of being the first “normal” number, it is no longer a “normal” number and now it is an “interesting” number, this way losing the property of being the first “Normal” and then ceasing to be “interesting” …

What a mess! It’s not “interesting”?

To tell you something interesting, this problem is the “Paradox of Richard’s Numbers,” described by the mathematician Jules Richard in 1905.


This link (https://en.wikipedia.org/wiki/Richard%27s_paradox) tells more details about Richard’s paradox, but in a less interesting way than here.

Another similar paradox is the “Liar Paradox”. A man who only tells lies says “I’m lying.” But as he only lies, he will be telling the truth in this statement. But if he speaks the truth, he is not the one who only tells lies.

These paradoxes “bugs” not only the minds of ordinary human beings, but also the minds of the greatest mathematicians in history.

The Austrian mathematician Kurt Godel demolished the foundations of all mathematics in 1931, with its Incompleteness Theorems, by proving that mathematics can not at the same time be Complete and Consistent. That is, mathematics has limits. Godel found a “bug” in the foundations of mathematics — it can not at the same time get rid of these bizarre paradoxes and answer True or False to all its propositions. Godel used a sophisticated version of Richard Paradox to prove it.

It’s a long story, which involves mind giants like David Hilbert and Bertrand Russell, and it’s for another day.

By the way, I think the author of the building light wrote “normal” in an “interesting” way only for the building not to be “normal” anymore, and thus to confuse our head …

 

Knapsack Problem – Dynamic Programming

Dynamic Programming

Dynamic Programming is one very useful technique for solving problems like the Knapsack problem.

The basic idea of Dynamic programming is to break down a problem in recursive subproblems. Each subproblem must be easy to solve, and its solution stored in some data structure to be used again.

In a schematic drawing, it’s something like this:

 

DynamicScheme.jpg

Dynamic programming is not a general procedure to be applied in any problem. The problem has to allow it. Richard Bellman, the creator of Dynamic Programming, called it  “Principle of optimallity”. It is like saying “this method works in the problems it works, and doesn’t works in the problems it doesn’t works”. But mathematicians love this kind of concept, because they can ask: which problems has the principle of optimality? Is the Knapsack one of them?

Since this method is not general, the programmer has to design a new method for every different problem and also figure out if the problem has the principle of optimality.

But, despite all these observations, it is a useful way of thinking.


 

The Algorithm

The detailed algorithm can be found in Wikipedia  (https://en.wikipedia.org/wiki/Dynamic_programming). I’ll sketch here the main ideias.

Suppose the problem is the same of previous post.
Max Weight = 14.
List of itens:
ValueWeight

1) It is easy to compute the solution with 1 item for a range of weights.

Array of values
Dyn01.JPG
Item 1, with weight 8 and value 10,000. If we vary the max weight of the knapsack from 0 to MaxW, the value array will be Zero before we have max weight = 8, and 10,000 after it.

 Array of picked itens:

Dyn02.JPG
“i1” refers to item 1

 


2) We increase the number of itens, and compare current solution versus solution with new item.
Step2_details
With items 1 and 2: if the max weight is 6, we pick item 2. When max weight is 8, we compare solution with weight 8 from item 1 (10,000) against solution with item 1 and weight 2 plus item 2 (0) and weight 6 (3,000). It is better to keep the 10,000 value solution.
Step2_details2.JPG
When maxWeight is 14, we compare current solution with item 1 (10,000) versus solution with item 1 and weight 8 (10,000) plus item 2: 13,000
Complete array with 2 items.
Step2.JPG
Evaluation arrays with itens 1 and 2
Proceeding this way, we get the full evaluation matrix.
Step5.JPG
Evaluation array with 5 items.
The optimum is to pick itens 1, 3 and 4, and the value of those items is 14,000.
Note that, in order to get the optimum, I had to calculate every solution for 1 to number of itens and 1 to number weigths. Thus, the computational effort to calculate it is proportional to N*maxW.
The problem happens when N and W are large. It needs a lot of computation processing.
Knapsack is a pseudo polynomial time algorithm. Polynomial because it is proportional to N. But pseudo since it is not quite true it is proportional to N – it is also proportional to W, and W can be very very large. One day I’ll post a more detailed way of thinking of this.

Divide and conquer
Other question I had when studying dynamic programming for the first time was: what is the difference of this method compared to divide and conquer methods?
I think this discussion deserves a post apart, but just to give a simple explanation, divide and conquer has a “horizontal” structure, with subproblems independent of each other, while dynamic programming has a “nested” structure, with subproblems dependent of other subproblems.

Computation implementation
An Excel- VBA implementation of dynamic programming applied to the knapsack problem can be found at https://github.com/asgunzi/KnapsackDynamicProgrammingVBA.
It uses the Random case generator of previous post and generates a solution for this case.
ScreenDynProg.JPG

Other Applications
Dynamic programming can be applied in several other cases. Cutting stock problems are also a classic application.
One less known application is in the optimization of forest multiproducts. We have a forest of eucalyptus. Each tree has to be cut in several pieces. Each piece has a different diameter (since the tree is larger in the base). A product is a combination of diameter and lenght. Given a demand, how can I minimize the waste and maximize the use of the forest?

 

In the next posts, I’ll show alternative methods for solving the Knapsack Problem.

 

Arnaldo Gunzi.

 

 

Analytics for Small Businesses

Analytics and Combinatorial Optimization are vast and wonderful areas, and also very difficult ones.

As a consultant on these areas, I’ve worked a lot with several tools and techniques. But the applications were always in very big companies, that can afford to pay a consulting company and can buy expensive software licenses.

bigdata.jpg

I believe Small Businesses also have problems that can be solved with powerful analytical tools. But these tools have to be well designed, free of charge and effective.


Mission of the blog:

– Empowerment of the user, with practical, easy to use and powerful analytical tools for small businesses.

– Practical applications, from literature and from the author’s experience.


There will have a range of softwares used here:

Microsoft Excel, because it’s a ubiquitous and powerful software.

AIMMS: the powerful optimization platform for very hard problems.

CBC: the free solver from Coin-operated, excellent too. 
Enjoy!

Arnaldo Satoru Gunzi

Jan 2016


The title of this blog is inspired in the beautiful poem “To See a World…”, by William Blake.

“To See a World…”

To see a World in a Grain of Sand
And a Heaven in a Wild Flower,
Hold Infinity in the palm of your hand
And Eternity in an hour.