05 Nov
If you know Visual Basic programming then you can create your own VBA macro and run from within Excel.
Follow the steps below:
1. On the Tools menu select Macros.
2. On the Macro submenu select Visual Basic Editor.
3. You will be switched [...]
Posted in Visual Basic by: admin
No Comments
29 Oct
I have added to the downloads page a yearly calendar template. This is a dynamic template that will allow you to generate a calendar for any year between 2001 and 2016. There are two combo boxes for the user to select the year and the start date of the week. Each selection will trigger a [...]
Posted in Dates, General, Visual Basic by: admin
No Comments
23 Sep
If you look at many of Excel lookup functions you will find out that they either return a value or a position of an item. There is no function that returns the address where a lookup value is found.
Below is a code for a user defined function that takes two arguments: a lookup value and [...]
Posted in Formulas, Functions, Visual Basic by: admin
No Comments
18 Jul
I have added to the downloads page a monthly calendar template. There are three combo boxes for the user to select the month, year and the start date of the week. Each selection will trigger a macro that will generate the calendar. Here are a few notes to consider:
To Enable Macros:
Select : Tools —> Macro [...]
Posted in General, Visual Basic by: admin
No Comments
01 Jul
When you create a macro or a user defined function in a workbook, by default that macro will only be visible for that specific workbook.
If you have a code that you use repeatedly and you want it to be available for all workbooks then you have to create a global workbook called Personal.xls and save [...]
Posted in Functions, General, Visual Basic by: admin
Comments Off
05 Jun
Excel has a function to generate random numbers: RAND (), and if you have the Analysis ToolPak add-in installed you will have another function available which is RANDBETWEEN().
However both functions will generate one random number. What if you want to generate a list of random numbers? The immediate answer will be: use Autofill.
This is OK [...]
Posted in Visual Basic by: admin
No Comments
11 May
Excel has a built in function which returns the day of the week corresponding to a date. The day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday), by default, or 1 (Monday) to 7 (Sunday) if you use a return type of 2. But there is no direct function to return [...]
Posted in Dates, Formatting, Functions, Visual Basic by: admin
No Comments
13 Apr
The following Visual Basic code is for a function to determine whether a certain year is leap year or not. It will receive the year as an argument and return TRUE for a leap year and FALSE for others.Copy this code and paste it in a new visual basic module.
Function IsLeap(iYear)
If (iYear Mod 400) = [...]
Posted in Dates, Functions, Visual Basic by: admin
2 Comments
12 Apr
If you know Visual Basic programming then you can create your own user-defined functions in Excel. Follow the steps below:
1. On the Tools menu select Macro.
2. On the Macro submenu select Visual Basic Editor.
3. You will be switched to Visual [...]
Posted in Functions, Visual Basic by: admin
No Comments