how to? custom worksheet function using VBA

hello. new user here.

Is it possible to create "custom" worksheet functions in VBA in Excel 2007?

I used to do this quite easily in Excel 5. I can't find how to do this in 
the new version.

This MS article doesn't seem to work for the 2007: 
http://office.microsoft.com/en-us/excel/HA010548461033.aspx 

0
fred8742 (11)
9/20/2008 2:01:15 AM
excel.newusers 15348 articles. 2 followers. Follow

4 Replies
293 Views

Similar Articles

[PageSpeed] 52

I don't have Excel 2007, but doesn't it work the same way? You just
put the UDF in a standard code module in the workbook where it's going
to be used. For example:

Function MySum(intNum1 As Integer, intNum2 As Integer) As Integer
  MySum =3D intNum1 + intNum2
End Function


Then just put "=3DMySum(1,2)" in your worksheet cell.

??

--JP

On Sep 19, 10:01=A0pm, "Fred Allen" <f...@microsoft.com> wrote:
> hello. new user here.
>
> Is it possible to create "custom" worksheet functions in VBA in Excel 200=
7?
>
> I used to do this quite easily in Excel 5. I can't find how to do this in
> the new version.
>
> This MS article doesn't seem to work for the 2007:http://office.microsoft=
..com/en-us/excel/HA010548461033.aspx

0
jp2112 (204)
9/20/2008 2:45:32 AM
I did just that, and that is what the MS article says. So at least I know I 
am on the right track (with VBA UDF, which operate differently than the old 
XLM UDF). I guess it is some trick in Excel 2007 (or Vista).

Thank you for confirming how I thought it was supposed to work. That does 
help!


"JP" <jp2112@earthlink.net> wrote in message 
news:7026644c-ca20-4262-a643-a91d480faf89@l42g2000hsc.googlegroups.com...
I don't have Excel 2007, but doesn't it work the same way? You just
put the UDF in a standard code module in the workbook where it's going
to be used. For example:

Function MySum(intNum1 As Integer, intNum2 As Integer) As Integer
  MySum = intNum1 + intNum2
End Function


Then just put "=MySum(1,2)" in your worksheet cell.

??

--JP

On Sep 19, 10:01 pm, "Fred Allen" <f...@microsoft.com> wrote:
> hello. new user here.
>
> Is it possible to create "custom" worksheet functions in VBA in Excel 
> 2007?
>
> I used to do this quite easily in Excel 5. I can't find how to do this in
> the new version.
>
> This MS article doesn't seem to work for the 
> 2007:http://office.microsoft.com/en-us/excel/HA010548461033.aspx

0
fred8742 (11)
9/20/2008 3:36:37 PM
Okay, the UDF has to be in a Module, it can't be in the code for a worksheet 
or even ThisWorkbook.


"Fred Allen" <fred@microsoft.com> wrote in message 
news:ORRWuazGJHA.2156@TK2MSFTNGP05.phx.gbl...
>I did just that, and that is what the MS article says. So at least I know I 
>am on the right track (with VBA UDF, which operate differently than the old 
>XLM UDF). I guess it is some trick in Excel 2007 (or Vista).
>
> Thank you for confirming how I thought it was supposed to work. That does 
> help!
>
>
> "JP" <jp2112@earthlink.net> wrote in message 
> news:7026644c-ca20-4262-a643-a91d480faf89@l42g2000hsc.googlegroups.com...
> I don't have Excel 2007, but doesn't it work the same way? You just
> put the UDF in a standard code module in the workbook where it's going
> to be used. For example:
>
> Function MySum(intNum1 As Integer, intNum2 As Integer) As Integer
>  MySum = intNum1 + intNum2
> End Function
>
>
> Then just put "=MySum(1,2)" in your worksheet cell.
>
> ??
>
> --JP
>
> On Sep 19, 10:01 pm, "Fred Allen" <f...@microsoft.com> wrote:
>> hello. new user here.
>>
>> Is it possible to create "custom" worksheet functions in VBA in Excel 
>> 2007?
>>
>> I used to do this quite easily in Excel 5. I can't find how to do this in
>> the new version.
>>
>> This MS article doesn't seem to work for the 
>> 2007:http://office.microsoft.com/en-us/excel/HA010548461033.aspx
> 

0
fred8742 (11)
9/21/2008 12:40:30 AM
Sorry Fred, I just realized your first post mentioned Excel 5, where
the method for creating UDFs might have been radically different. Yes
you put the code in a standard module by pressing Alt-F11 to access
the VBIDE, then going to Insert > Module in the workbook in which you
want to use the function.


On Sep 20, 8:40=A0pm, "Fred Allen" <f...@microsoft.com> wrote:
> Okay, the UDF has to be in a Module, it can't be in the code for a worksh=
eet
> or even ThisWorkbook.
>
> "Fred Allen" <f...@microsoft.com> wrote in message
>
> news:ORRWuazGJHA.2156@TK2MSFTNGP05.phx.gbl...
>
>
>
> >I did just that, and that is what the MS article says. So at least I kno=
w I
> >am on the right track (with VBA UDF, which operate differently than the =
old
> >XLM UDF). I guess it is some trick in Excel 2007 (or Vista).
>
> > Thank you for confirming how I thought it was supposed to work. That do=
es
> > help!
>
0
jp2112 (204)
9/21/2008 11:48:53 AM
Reply:

Similar Artilces:

Mal-Function
We're using the Sum function and the correct formula appears in the cell, but not the number resulting from the formula. We can't figure out why. Bill could be the cell is formatted as text. If that's the case, change the cell format to General and edit the cell. Just press Enter to re-input your formula. Or you could have a space in front of the equals sign; just delete the space and press Enter. Regards Trevor "Bill" <anonymous@discussions.microsoft.com> wrote in message news:DCF20522-A42E-4A6E-ADEA-59D880F74960@microsoft.com... > We're using the ...

Correct the function WEEKNUMBER
The function weeknumber does not work properly. When typing Jan 1 2005 it should display 53 and not 1, when i typ jan 3rd (in colum 1) and =weeknumber(a2) in collum b it should display 1 and not 2. because the weeks in 2004 is 53 and not 52, is this my mistake or is this with excel that only counts up to week 52. I hope you can change this. I think you should read Chip Pearson's notes on =weeknum() http://www.cpearson.com/excel/weeknum.htm As well as excel's help for the =weeknum() function. Eric Wessels wrote: > > The function weeknumber does not work properly. When t...

Adding columns automatically based on worksheets
MVPs In a workbook, I have initially 2 sheets. Sheet1 contains information about sheet2 which is reflected in Column B. How can I automatically add a column (column C, D, etc..) of information if I add another sheet3, Sheet4, etc.. Meaning, I want to be able to freely add sheet3, sheet4 and in sheet1 columns would be automatically be created based on these new sheets. ...

Create Custom Tab
I would like to know how can I create a custom tab on Access2007 and How to create custome group on it -- Nat ...

How do I use the connection string to connect to a table on the database that I am working on.
I have two related tables. and one form of which one of the tables is a subform and i want to write the data on the subform to a textfile. I used this code to connect to the table but because I have already opened the MDB. I get the error that the file is already openned. objConn.CursorLocation = adUseClient objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\Documents and Settings\exds006\Desktop\Electronic LIV;" & "Persist Security Info=False" objConn.Open Is there another way of accessing th...

how to use the search dialog box ?
Hello Everybody, I have a question concerning the opening of dialogs box. I would like to open the Search Dialog Box. My stupid code is like this : Private Sub CommandButtonSearch_Click() 'ouvre la boite de dialogue Rechercher Application.Dialogs(xlDialogFormulaFind).Show End Sub With this code, the problem is that the Search Dialog box is indeed open but whatever the text to find in the worksheet, there is no result. If I stop the macro, open by hand the Search Dialog box and type the same text, it works... Can you help me to solve this problem ? Thanks a lot in advance. ...

Countif using symbols
I have a worksheet that has a column containing icons, from the new options of conditional formatting, in Excel 2007 (Icon sets) I need to count how many of each icon I have. Can I do this using the function COUNTIF(). I couldn't figure out what should I use as criteria here. Thanks in advance. Regards, Emece.- ...

Using a common outlook address book on a home network
How do I configure my system so that all computers on my home office network can use the same list of contacts? Each computer has Outlook 2000. Two use Windows 2000 Professional; one uses Windows XP. Matt Arnett <anonymous@discussions.microsoft.com> wrote: > How do I configure my system so that all computers on my > home office network can use the same list of contacts? > Each computer has Outlook 2000. Two use Windows 2000 > Professional; one uses Windows XP. Create a network share, place your PST in that share, map a drive to it on the other machines and open the P...

Creating and using a Deleted folder
I am using outlook 2002 and when an item is deleted it shows as the item with a line thur it. Is there anyway to have the deleted item moved to a deleted folder. I am running outlook with 3 connections. Two I IMAP and one with MS exchange. The exchange one does have a deleted folder but the others don't Scott Adams <adamsfour@aol.com> wrote: > I am using outlook 2002 and when an item is deleted it > shows as the item with a line thur it. Is there anyway to > have the deleted item moved to a deleted folder. No. That's the way IMAP works. -- Brian Tillman ...

copy of active screen without using the Print Screen button
Hi, I have a Netbook and it does not have a Print Screen button.HOW do i capture the active window to paste it into a Word document?I do that all the time on a different computer and i am travelling at the moment and i am stuck as i cannot seem to find an *alternative button. Anyone know? I have tried the Gadwin print screen software but it does not work very well for me. TIA XP user wrote: > Hi, > > I have a Netbook and it does not have a Print Screen button.HOW do i capture > the active window to paste it into a Word document?I do that all the time on >...

using dmax
I want to find the max temperature for each day of the year over 10 years worth of data. I can do it by using a criteria where the first in the range of the criteria is the label ("Date" in this case) and under that cell is the date I want to look up (eg "Jan,3"). But it seems like I need to have 2 cells per day of the year in order to specify each day. That is, it looks like I have to do it this way: A B 1 Date 2 ="Jan,1" =dmax(H100:I465,2,A1:A2) 3 Date 4 ="Jan,2" =dmax(H100:I465,2,A3:A3) 5 ...

E-mail stuck in Inbox, file in use, backup fails
I have a reoccuring problem that is driving me nuts. I have Small Business Server 2003 with Exchange set up. A user keeps getting a specific e-mail in her inbox in the mail store that kills the Veritas backup. The e-mail never makes it to the Outlook 2003 client. If you use OWA, the e-mail shows up there but it will not open. The page not found error is HTTP 500 server error. If you look at the log in Veritas, it says 'file in use' and 'access denied'. On the weekend I rebooted the server remotely and then permanently deleted the e-mail. The backup completed succes...

Increase default size of worksheet
I am trying to copy a floppy containing data into a Excel worksheet. I keep getting the message 'all file is not loaded'. How do increase the default size of a worksheet to enable the floppy to download all of the data? Westontony wrote: > I am trying to copy a floppy containing data into a Excel > worksheet. I keep getting the message 'all file is not > loaded'. How do increase the default size of a worksheet > to enable the floppy to download all of the data? Not *quite* sure what you are trying to do, but have you tried copying the file from the floppy to y...

Adjustmant To VBA using AdvancedFilter
This is a multi-part message in MIME format. ------=_NextPart_000_0124_01C35208.FC27F940 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Good evening All I am hoping that someone will be able to help with this. I have some small VBA code (shown below), which applies to the following = example of a data set: Worksheet "Invoice Record" A B C =20 1 M100 152.34 03 2 M101 100.02 NP 3 M100 250.65 02 4 M100 565.52 01 5 M102 745.87 NP 6 ...

graph data from multiple worksheets
I have about 10 worksheets with data in them, I would like to put it all on one chart, how do I do it? I cannot fit it all in one worksheet. Produce the chart from the data from your first workshet. Go to your second worksheet, select the relevant data, copy, go to your chart, edit/ paste special, and accept the relevant option to add a new data series or add data to an existing series. Alternatively having produced your original chart, go to Source Data, and either adding data to an existing series or adding a new series will allow you to select the relevant data from whichever work...

Using the Windows Installer Cleanup Utility for Office installation
Windows XP Professional, ver 2002, (9.0.6926) SP2 NTFS Office 2000 Professional SP3 Internet Explorer 7.0.5730.11 128 bit cipher strength I'm using Shenan Stanley's cleanup procedures, prepping for install of Windows XP SP3. Confirm answers regarding instructions for using the Windows Installer CleanupUtility to remove Office 2000, Office XP or Office 2003. (1) Right now, I'm running Office 2000 Professional SP3. I know Microsoft article 290301 says the Installer Cleanup Utility it is not going to remove the actual Office program; it is only going to remove ...

very custom formats
I need help with a custom format If I enter the numbers 123 I want it to display 12-3 in the cell. (i.e simple custom format ##-# But if I also want to be able to enter 123A into the same cell and have it display 12-3A (where A could be any letter A-Z).. What would my custom format need to look like in order to do that Or is there a better approach? Not sure you can do what you want because the alphabetic character means the value is no longer numeric, hence a custom number format won't work Regards Trevor "mrdigithead" <anonymous@discussions.microsoft.com> wrote i...

Word's return to previous edit function
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel The 'previous edit' function no longer works. If I'm in a large file, adding something in the middle of it, then save the file and return to it later, the 'go to your previous edit/cursor insertion point' function doesn't work. <br><br>Help!!! Nothing you can do about it: it's a bug introduced in Word 2008, and they're not going to fix it. When you save, Word cleans up the document, and in doing so, Word 2008 is too aggressive and deletes the bookmarks that "Pre...

Report Name Customer
Hi We have created a custom report in Visual Studio which shows all the service activities per customer in CRM. I now would like to display the customer name which was used in the filter as the heading of the report. If no customer was selected the heading should be blank. Do I need to use CRM_FilterText to achieve this? I have attempted to insert a parameter without success. I would really appreciate some guidance in how to achieve this. Many Thanks Mark no you can easily achieve this with a table group heading "Mark Braithwaite" <MarkBraithwaite@discussions.microsoft....

Moving worksheets into viewing area
I need help finding a worksheet when it has accidentally been moved out of the viewing area. In the past, I've selected all with ctrl-a, copied the selection, and then pasted to a new sheet. I know the sheet is open because I can see the individual cell formulas above and below the viewing area as I move around the sheet with arrows. Is there a keyboard command I can use to try and move the original sheet back into the viewing area? I've had 2 computer-illiterate coworkers do this now, and I can't figure out how they are making these sheets disappear, much less get them ...

OWA can be use and sometimes could not
Hi to proffesionals I have Exchange 2003 on Win 2003 Active Directory Domain. Some users can use OWA sometimes, and other users could not use OWA partly time. Why does this happen? Regards, B.Majidi In news:u2YBFh8NHHA.320@TK2MSFTNGP06.phx.gbl, Babak Majidi <b_majidi@yahoo.co.uk> typed: > Hi to proffesionals > I have Exchange 2003 on Win 2003 Active Directory Domain. > Some users can use OWA sometimes, and other users could not use OWA > partly time. > Why does this happen? > > Regards, > B.Majidi It's impossible to say with so little information - you h...

Deleting Customer
Running HQ and 2 stores. version 1.2.0185 Trying to ensure that all customers (about 20 in total) are global but somehow one of the stores has a global customer that doesn't show up in HQ. I want to delete this odd customer. Store won't let me remove it because it is a global customer and if I try to run an HQ worksheet to delete global customers then their account number doesn't show up in the list. Suggestions? This is a multi-part message in MIME format. ------=_NextPart_000_0028_01C6A07D.E080C750 Content-Type: text/plain; charset="Utf-8" Content-Transfer-Enc...

Problem receiving e-mail using Outlook XP SP 3 on Win XP SP2 Machi
I have many users having issues with not receiving new e-mail until they "tickle" their Outlook by switching views. Additionally they sometimes see items staying in their Outbox even though the item is in fact delivered to the recipient. I tried both of the following KB articles. One did not help, the other one using the regedt32.exe did not have a key for 9.0, only 10.0 and 8.0. Any help would be greatly appreciated, I've been troubleshooting this for 2 weeks now. Thanks, Anne Altman http://support.microsoft.com/default.aspx?scid=kb;en-us;304849 or http://support.mi...

Hyperlink a Visio shape to a worksheet
I have a Visio diagram that represents the computers in ou computer/server room. I want to create hyperlinks from each of th shapes that represents a server (for example) to a correspondin worksheet in a workbook. I can get the hyperlink to open the workbook, but I can't figure ou how to drill down to the specific worksheet for a particular server. Thanks in advance, Jef -- Message posted from http://www.ExcelForum.com not tested but try adding the sheet name with workbookpath+name#sheet1 -- Regards Frank Kabel Frankfurt, Germany > I have a Visio diagram that represents the ...

Coping numbers to next blank cell on another worksheet
I am just learning Excel (need SIMPLE help) but any help would be greatly appreciated. My OS is XL Professional and I have MS Excel 2002 SP2. I need to copy calculated numbers (I got this much done on my own) in specific cells on worksheet # 1 to the next blank cells in existing columns of numbers used in calculations on other worksheets in the same workbook. I other words, I have a formula that calculates numbers and places the results in the same column and row each time it is used. Those calculated numbers need to be copied and used on different worksheets in the same workbook. While...