VBA to … export VBA modules for archiving or other

Neat. http://www.cpearson.com/Excel/VBE.aspx  Now I can export the modules for text version control  (so we can see the changes). AND use it to standardize all the code between little small projects to ensure that all the good ideas from one period in time can be compared to other projects done in an earlier time.

For instance, lets say we made a great “look for header” routine 4 months ago. Today, the header row is not fixed to row 1 any longer. If we make a new routine that is a little more generic – it needs to have a different name but if we backdate all the other routines, then the idea is better for all projects. The tools in the above link give us all we need to get this completed. … and the code above works – with few changes.

Finding most likely Excel A1 cell to place text blocks in

So, I had some manually entered non-tables in an AutoCAD document that I needed to get to an Excel format. Non tables literally refers to tables made by placing text in a rectangular x,y fashion. So these were Bentley ISO’s that the machine makes a material take off in the upper right corner. Not knowing that the future held in terms of the number of columns – this turned out to be a “get all x values and make a histogram of buckets x millimeters wide”. This is basically how communications determines wireless signals in quadrature (x/y plane). Since humans can enter text boxes alongside the machine – what is the best guess for the grid?

We could use some col math like the Verterbi algorithm – but that is a little too complicated. Basically making a histogram and determininig the best row and column was the best approach. Unlike wireless communication theory – the bucket widths are completely known w.r.t. to each drawing that we process AND the number of columns are unknown. What is known is that within a drawing – there are a consistent number of rows and columns.

It worked by the way.

Some links.

  • http://stackoverflow.com/questions/8979752/how-to-count-number-of-peaks-in-graph-graph-analysis
  • Verterbi algorithm: http://en.wikipedia.org/wiki/Viterbi_algorithm

Autocad- get to the field edit value of a block attribute

Well, this was like looking for a needle in a haystack. I tried to use google to know if a block attribute referred to text or to a formula that made the text. The key is looking at the extension dictionary. If there is one, it has dictionaries inside of them and if one of the items in named “ACAD_FIELD” then… yes this attribute is de-referenced or aliased or abstracted by using a formula. I was usinging the attributes .Text property which gives you the RESULT of the field formula underneath. I was doing search and replaces and blowing away the formulas – which would have been best left alone. Now I can skip them.

The guy here about 8 posts down AFTER reading the code was my hero on this one. It was after about 20 google searches.

Continue reading

Getting the user to pick an Excel cell range via a dialog …

I have made a couple tools with setup variables that I wish to keep in Excel and not really accessible to the end user per-se. So I use named ranges. However, rather than type up elaborate setup documents for a spreadsheet – the end user said “can I get a helper routine to fill all these in?”. Yes was the answer ONLY IF we could have the user pick selection ranges. So – using userforms and the Application.InputBox … I think we can! Here’s how… Continue reading