Writing Excel User Defined Functions in VBA – part #2 – time stamping, concatenating date and text while maintaining the date format


Hi Folks, there are many available built-in spreadsheet functions in Excel. Ocasionaly one might need to write a special custom function. This second short tutorial is an introduction to that.  Creating a “Time Stamp” function is presented as a first example after which a concatenation function between a custom formatted date and a string (text) is introduced. This last function came about from a question I was asked by a finance person at…


Writing Excel User Defined Functions in VBA – part #1 – the distance function


There are many available built-in spreadsheet functions in Excel. Ocasionaly one might need a very special custom function. This short tutorial is an introduction about how to write a user defined function which calculate the distance between two points in a cartesian plane. The topic will be continued and custom functions will occasionally be used in future models. We’d love to hear from you. [sociallocker][/sociallocker]


Time in VBA Part#1 – Japanese Clocks


Hi guys, here is a simple file with a bunch of clocks. The code is very basic and the whole workbook is fully open so you can see how it’s done. All the spread sheet calculations are contained in the upper left corner and the VBA code is just few lines long. Click the “Run-Pause Clock” button to start or pause the VBA clocks. All the of them will display your local…


Dynamic Macros #1


Hi everybody, Today we’ll talk about dynamic macros very useful in engineering modeling, scientific modeling, games etc. Dynamic macros are created using two different type of loops: “For” and “Do”. – Two different but equivalent conditional “Do” loops are explored. – It is shown that macros can be assigned to drawing shapes, pictures, graph, etc. – A highly valuable Run/Pause macro is described and implemented. The reader must pay close attention…


Resizing, Translating and Rotating Shapes in Excel


Five different shape alterations are presented: 1. Resizing – which can be done independently on each axis by multiplying the coordinates by a certain factor while the shape is centered in the origin. 2. Translation – which can be done independently on each axis by adding or subtracting a term to each coordinate. 3. Simple rotation – formulas were derived for rotation of a shape centered in origin by a…


Static VBA Macros


Most parameters used in animations or physical models are changed by buttons called “spin buttons”. Excel is a spreadsheet but behind the table there is a engine used to automate certain spreadsheet functions. This engine can be programmed in a language with a very easy syntax called Visual Basic for Applications, in short VBA. VBA is a very convenient feature in Excel and it can be used almost without any…


Creating Shapes on a 2D Scatter Chart


This post shows you how to draw a closed polygon (in this case a triangle for simplicity) on a 2D scatter plot in Excel 2003. 1. For this you need to sketch what you want to draw on a paper or just create a mental visualization. 2. In this sketch you need to define the vertices (corners) of the shape and figure out the approximate coordinates. 3. Create a table…