You are here: Home / Visual Basic

How to make your macros and functions available to all workbooks

July 1, 2008 By

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 it in Excel startup folder.

How to create the file:

1. Open a new workbook.
2. Save As Personal.xls in folder:
C:\Program Files\Microsoft Office\OFFICE11\XLSTART
3. Copy your macros from their original workbooks to personal.xls.
4. Click Window –> Hide to hide the file.

The path may differ slightly depending on which version of office you are using.

How to use the file:

1. Whenever you start Excel personal.xls will be opened automatically, and all your macros will be available for use in any workbook.

2. Keep personal.xls hidden unless you want to add macros to it.

3. When you want to add new macros Click Window –> Unhide and select “personal.xls”.

4. To add a new macro click Tools –> Macro –> Record new macro. You will get the following dialogue box.

savemacro

5. Under “Store macro in:” select “Personla.xls”.

6. When you are done hide the file again.

If you are interested in more details please see the following Microsoft office link:

Deploy your Excel macros from a central file

Applies to: Excel 2003

Filed Under: Functions, General, Visual Basic Tagged With: , , ,

A macro to generate a list of random numbers

June 5, 2008 By

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 if you want a small list, but what if you have to generate say a list of 1000 random numbers?

The macro shown below will loop through to generate any number of entries you specify. It will also give you an option to specify the number of digits you want in the generated number.


Sub GenNumbers()
Dim i As Integer, Row As Integer, Col As Integer
Dim NoRows As Integer, NoDigits As Integer
Dim MinNum As Double, MaxNum As Double
Dim NumFormula As String
Col = 1 ‘ Write random numbers on column A
‘Get the number of rows and number of digits entered by the user
NoRows = ActiveSheet.Range(“B1″).Value
NoDigits = ActiveSheet.Range(“B2″).Value
‘Calculate minimum and maximum numbers to be generated
MinNum = WorksheetFunction.Power(10, (NoDigits – 1))
MaxNum = WorksheetFunction.Power(10, NoDigits) – 1
‘Construct the formula to be entered in the cells.
NumFormula = “=randbetween(” & MinNum & “,” & MaxNum & “)”
‘ The loop to generate the numbers
i = 0
For Row = 3 To NoRows + 2
i = i + 1
ActiveSheet.Cells(Row, Col).Value = i
ActiveSheet.Cells(Row, Col + 1).Formula = NumFormula
Next Row
End Sub

How to use the macro

1. Switch to Visual Basic Editor and Insert a new module.
2. Copy the code into the new module.
3. Close Visual Basic Editor and return to your worksheet.
4. In cell B1 of your worksheet enter the number entries you want.
5. In cell B2 of your worksheet enter the number digits you want.
6. Go to Tools -> Macro -> Macros, you will see the list of available macros.
7. Select GenRandomNum and run it.
8. You will get an output like this:

genrandnum.gif

Applies to: Excel 2003

Filed Under: Visual Basic Tagged With: , ,

Function to return the day of the week in text (Name of the day)

May 11, 2008 By

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 the name of the day itself i.e. Monday, Tuesday…etc.The following code is an attempt to handle this case.


Function WeekdayText(dDate, iRtype)
‘ get the weekday no. from excel bilt-in Weekday function
iwday = WorksheetFunction.Weekday(dDate)
If iRtype = 2 Then
‘ Return the full name of the day
WeekdayText = WorksheetFunction.Text(iwday, “ddddd”)
Else
‘ Return the short name of the day
WeekdayText = WorksheetFunction.Text(iwday, “ddd”)
End If
End Function

The function accepts two arguments: Date and return type. If the return type is 1, the function will return an abbreviated name of the day i.e. Mon, Tue …. etc. If the return type is 2, the function will return the full name of the day i.e. Monday, Tuesday …. etc.

Use this code to create a user defined function and then you can use it like any other built-in function.

If you are not familiar with visual basic then you can achieve the same result by encapsulating the Weekday function within the Text function, in a cell, as follows:

=TEXT(WEEKDAY(date),”ddd”)

Related Links:

How to create a user-defined function

Applies to: Excel 2003

Filed Under: Date and Time, Formatting, Functions, Visual Basic Tagged With: , , , ,

Function to decide whether a year is a leap year or not

April 13, 2008 By

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) = 0 Then
IsLeap = True
ElseIf (iYear Mod 100) = 0 Then IsLeap = False
ElseIf (iYear Mod 4) = 0 Then IsLeap = True
Else: IsLeap = False
End If
End Function

Related Links:

Blog Post: How to create a user-defined function
External: Wikipedia article on Leap_year

Applies to: Excel 2003

Filed Under: Date and Time, Functions, Visual Basic Tagged With: , ,

How to create a user-defined function

April 12, 2008 By

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.

User defined1

3. You will be switched to Visual Basic Editor Menu.

User defined 2

4. On the Insert menu (Visual Basic Editor) select Module.

5. This will open the code window.

User defined 3

6. Type the Visual Basic code for your function.

7. On the File menu click Close and return to Microsoft Excel.

8. Your new function should now be ready for use like any other Excel function.

Applies to: Excel 2003

 

Filed Under: Functions, Visual Basic Tagged With: , ,
Newer Posts »