Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Excel functions in VBA
I wrote the following code in VBA. This worked fine.
Range("C30").Value = Application.Index(Range("A1:I18"), "13", "6") I compared this code to the one below which is similar in format. Am I correct in assuming in order to codify an Excel function in VBA all you have to do is Add the prefix "Application." to the function name followed by parenthesis and the internal variables? I assumed this when writing the following code that yielded an error. When I ran the code a dialog popped up stating "Object doesn't support this Property or Method". Range("C27").Value = Application.Address(Range("C26").Value, "4", "4") Perhaps the function "Address" was not recognized in VBA. Although the "Application." prefix worked in the first line of code, I thought one had to write "Application.WorksheetFunction." preceding the actual excel function in order to codify the function. Would someone please clarify? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Excel functions in VBA
No all worksheet functions are available in VBA. For a list, look the
Worksheetfunction object in the VBA object brower. The WorksheetFunction Object was introduced in xl97. Before that, you just used application as the prefix. So this is still supported. The behavior of some functions is different when using Worksheetfunction as a qualifier. For example, with Match and the lookup functions, if the function would normally return a #N/A type results, then with the worksheetfunction qualifier, they will raise a trappable error. Using just application, the return the VBA equivalent of the #N/A (cvErr(xlErrNA)) and the results can be checked with the VBA function IsError -- Regards, Tom Ogilvy "alistre" wrote in message ups.com... I wrote the following code in VBA. This worked fine. Range("C30").Value = Application.Index(Range("A1:I18"), "13", "6") I compared this code to the one below which is similar in format. Am I correct in assuming in order to codify an Excel function in VBA all you have to do is Add the prefix "Application." to the function name followed by parenthesis and the internal variables? I assumed this when writing the following code that yielded an error. When I ran the code a dialog popped up stating "Object doesn't support this Property or Method". Range("C27").Value = Application.Address(Range("C26").Value, "4", "4") Perhaps the function "Address" was not recognized in VBA. Although the "Application." prefix worked in the first line of code, I thought one had to write "Application.WorksheetFunction." preceding the actual excel function in order to codify the function. Would someone please clarify? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Excel functions in VBA
You have to use the worksheetfunction. prefix instead of application
HTH _______________________ Naz, London "alistre" wrote: I wrote the following code in VBA. This worked fine. Range("C30").Value = Application.Index(Range("A1:I18"), "13", "6") I compared this code to the one below which is similar in format. Am I correct in assuming in order to codify an Excel function in VBA all you have to do is Add the prefix "Application." to the function name followed by parenthesis and the internal variables? I assumed this when writing the following code that yielded an error. When I ran the code a dialog popped up stating "Object doesn't support this Property or Method". Range("C27").Value = Application.Address(Range("C26").Value, "4", "4") Perhaps the function "Address" was not recognized in VBA. Although the "Application." prefix worked in the first line of code, I thought one had to write "Application.WorksheetFunction." preceding the actual excel function in order to codify the function. Would someone please clarify? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Excel functions in VBA
Address is not a VBA function. It is a property. You can find whether items
are functions by opening the VBA Editor and clicking help, then enter the word you want to check. The search results will show you if it is a function, method, property, etc. and by clicking on the result, you can see an example of the code in most cases. "alistre" wrote: I wrote the following code in VBA. This worked fine. Range("C30").Value = Application.Index(Range("A1:I18"), "13", "6") I compared this code to the one below which is similar in format. Am I correct in assuming in order to codify an Excel function in VBA all you have to do is Add the prefix "Application." to the function name followed by parenthesis and the internal variables? I assumed this when writing the following code that yielded an error. When I ran the code a dialog popped up stating "Object doesn't support this Property or Method". Range("C27").Value = Application.Address(Range("C26").Value, "4", "4") Perhaps the function "Address" was not recognized in VBA. Although the "Application." prefix worked in the first line of code, I thought one had to write "Application.WorksheetFunction." preceding the actual excel function in order to codify the function. Would someone please clarify? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Excel functions in VBA
No all worksheet functions are available in VBA.
should have said Not all worksheet functions are available in VBA. -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... No all worksheet functions are available in VBA. For a list, look the Worksheetfunction object in the VBA object brower. The WorksheetFunction Object was introduced in xl97. Before that, you just used application as the prefix. So this is still supported. The behavior of some functions is different when using Worksheetfunction as a qualifier. For example, with Match and the lookup functions, if the function would normally return a #N/A type results, then with the worksheetfunction qualifier, they will raise a trappable error. Using just application, the return the VBA equivalent of the #N/A (cvErr(xlErrNA)) and the results can be checked with the VBA function IsError -- Regards, Tom Ogilvy "alistre" wrote in message ups.com... I wrote the following code in VBA. This worked fine. Range("C30").Value = Application.Index(Range("A1:I18"), "13", "6") I compared this code to the one below which is similar in format. Am I correct in assuming in order to codify an Excel function in VBA all you have to do is Add the prefix "Application." to the function name followed by parenthesis and the internal variables? I assumed this when writing the following code that yielded an error. When I ran the code a dialog popped up stating "Object doesn't support this Property or Method". Range("C27").Value = Application.Address(Range("C26").Value, "4", "4") Perhaps the function "Address" was not recognized in VBA. Although the "Application." prefix worked in the first line of code, I thought one had to write "Application.WorksheetFunction." preceding the actual excel function in order to codify the function. Would someone please clarify? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Excel functions in VBA
That is incorrect. Even the OP demonstrated that you don't.
-- Regards, Tom Ogilvy "Naz" wrote in message ... You have to use the worksheetfunction. prefix instead of application HTH _______________________ Naz, London "alistre" wrote: I wrote the following code in VBA. This worked fine. Range("C30").Value = Application.Index(Range("A1:I18"), "13", "6") I compared this code to the one below which is similar in format. Am I correct in assuming in order to codify an Excel function in VBA all you have to do is Add the prefix "Application." to the function name followed by parenthesis and the internal variables? I assumed this when writing the following code that yielded an error. When I ran the code a dialog popped up stating "Object doesn't support this Property or Method". Range("C27").Value = Application.Address(Range("C26").Value, "4", "4") Perhaps the function "Address" was not recognized in VBA. Although the "Application." prefix worked in the first line of code, I thought one had to write "Application.WorksheetFunction." preceding the actual excel function in order to codify the function. Would someone please clarify? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Excel functions in VBA
You are correct that Worksheetfunction doesn't work with this particular one
(whoops)...but what does " Even the OP demonstrated that you don't. " what does that mean? -- _______________________ Naz, London "Tom Ogilvy" wrote: That is incorrect. Even the OP demonstrated that you don't. -- Regards, Tom Ogilvy "Naz" wrote in message ... You have to use the worksheetfunction. prefix instead of application HTH _______________________ Naz, London "alistre" wrote: I wrote the following code in VBA. This worked fine. Range("C30").Value = Application.Index(Range("A1:I18"), "13", "6") I compared this code to the one below which is similar in format. Am I correct in assuming in order to codify an Excel function in VBA all you have to do is Add the prefix "Application." to the function name followed by parenthesis and the internal variables? I assumed this when writing the following code that yielded an error. When I ran the code a dialog popped up stating "Object doesn't support this Property or Method". Range("C27").Value = Application.Address(Range("C26").Value, "4", "4") Perhaps the function "Address" was not recognized in VBA. Although the "Application." prefix worked in the first line of code, I thought one had to write "Application.WorksheetFunction." preceding the actual excel function in order to codify the function. Would someone please clarify? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Excel functions in VBA
You don't have to use worksheetfunction as the prefix for any
worksheetfunction that is supported for being called from VBA. You may also use Application instead in just about every case that I can think of. -- Regards, Tom Ogilvy "Naz" wrote in message ... You are correct that Worksheetfunction doesn't work with this particular one (whoops)...but what does " Even the OP demonstrated that you don't. " what does that mean? -- _______________________ Naz, London "Tom Ogilvy" wrote: That is incorrect. Even the OP demonstrated that you don't. -- Regards, Tom Ogilvy "Naz" wrote in message ... You have to use the worksheetfunction. prefix instead of application HTH _______________________ Naz, London "alistre" wrote: I wrote the following code in VBA. This worked fine. Range("C30").Value = Application.Index(Range("A1:I18"), "13", "6") I compared this code to the one below which is similar in format. Am I correct in assuming in order to codify an Excel function in VBA all you have to do is Add the prefix "Application." to the function name followed by parenthesis and the internal variables? I assumed this when writing the following code that yielded an error. When I ran the code a dialog popped up stating "Object doesn't support this Property or Method". Range("C27").Value = Application.Address(Range("C26").Value, "4", "4") Perhaps the function "Address" was not recognized in VBA. Although the "Application." prefix worked in the first line of code, I thought one had to write "Application.WorksheetFunction." preceding the actual excel function in order to codify the function. Would someone please clarify? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Excel functions in VBA
VBA has its own address property. Also, I don't think you need the Index
function. Range("A1:I18") (13, 6) Cells(Range("C26").Value, 4).Address(False, False) "alistre" wrote: I wrote the following code in VBA. This worked fine. Range("C30").Value = Application.Index(Range("A1:I18"), "13", "6") I compared this code to the one below which is similar in format. Am I correct in assuming in order to codify an Excel function in VBA all you have to do is Add the prefix "Application." to the function name followed by parenthesis and the internal variables? I assumed this when writing the following code that yielded an error. When I ran the code a dialog popped up stating "Object doesn't support this Property or Method". Range("C27").Value = Application.Address(Range("C26").Value, "4", "4") Perhaps the function "Address" was not recognized in VBA. Although the "Application." prefix worked in the first line of code, I thought one had to write "Application.WorksheetFunction." preceding the actual excel function in order to codify the function. Would someone please clarify? |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Excel functions in VBA
Just in case the question was non-technical.
OP = Original Poster. "even the Original Poster demonstrated..." Naz wrote: You are correct that Worksheetfunction doesn't work with this particular one (whoops)...but what does " Even the OP demonstrated that you don't. " what does that mean? -- _______________________ Naz, London "Tom Ogilvy" wrote: That is incorrect. Even the OP demonstrated that you don't. -- Regards, Tom Ogilvy "Naz" wrote in message ... You have to use the worksheetfunction. prefix instead of application HTH _______________________ Naz, London "alistre" wrote: I wrote the following code in VBA. This worked fine. Range("C30").Value = Application.Index(Range("A1:I18"), "13", "6") I compared this code to the one below which is similar in format. Am I correct in assuming in order to codify an Excel function in VBA all you have to do is Add the prefix "Application." to the function name followed by parenthesis and the internal variables? I assumed this when writing the following code that yielded an error. When I ran the code a dialog popped up stating "Object doesn't support this Property or Method". Range("C27").Value = Application.Address(Range("C26").Value, "4", "4") Perhaps the function "Address" was not recognized in VBA. Although the "Application." prefix worked in the first line of code, I thought one had to write "Application.WorksheetFunction." preceding the actual excel function in order to codify the function. Would someone please clarify? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple functions, conditional functions | Excel Worksheet Functions | |||
How to convert cell formula functions to code functions | Excel Discussion (Misc queries) | |||
efficiency: database functions vs. math functions vs. array formula | Excel Discussion (Misc queries) | |||
Conversion from Spreadsheet Toolkit functions (ESSV....) to EssBase API functions | Excel Programming | |||
excel functions and User defined functions | Excel Programming |