Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.WorksheetFunction problem
I am trying to call ISEVEN() from VBA with:
Dim b as Boolean b = Application.WorksheetFunction.ISEVEN(A1) I am raising "Object doesn't support this Property or Method" What is the correct syntax? -- Gary''s Student |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.WorksheetFunction problem
try
If Range("I11") Mod 2 = 0 Then MsgBox "even" -- Don Guillett SalesAid Software "Gary''s Student" wrote in message ... I am trying to call ISEVEN() from VBA with: Dim b as Boolean b = Application.WorksheetFunction.ISEVEN(A1) I am raising "Object doesn't support this Property or Method" What is the correct syntax? -- Gary''s Student |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.WorksheetFunction problem
Dim b as Boolean
b = Application.WorksheetFunction.ISEVEN(Range("A1")) -- Regards, Tom Ogilvy "Gary''s Student" wrote in message ... I am trying to call ISEVEN() from VBA with: Dim b as Boolean b = Application.WorksheetFunction.ISEVEN(A1) I am raising "Object doesn't support this Property or Method" What is the correct syntax? -- Gary''s Student |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.WorksheetFunction problem
if you want to test for a number first
Sub iseven() myrange = Range("I11") If IsNumeric(myrange) Then If myrange Mod 2 = 0 Then MsgBox "even" End If End Sub -- Don Guillett SalesAid Software "Don Guillett" wrote in message ... try If Range("I11") Mod 2 = 0 Then MsgBox "even" -- Don Guillett SalesAid Software "Gary''s Student" wrote in message ... I am trying to call ISEVEN() from VBA with: Dim b as Boolean b = Application.WorksheetFunction.ISEVEN(A1) I am raising "Object doesn't support this Property or Method" What is the correct syntax? -- Gary''s Student |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.WorksheetFunction problem
One option: In the vba editor, go to Tools | References...and add
"atpvbaen.xls" Then... Dim EvenQ As Boolean EvenQ = IsEven(Range("A1")) HTH :) -- Dana DeLouis Win XP & Office 2003 "Gary''s Student" wrote in message ... I am trying to call ISEVEN() from VBA with: Dim b as Boolean b = Application.WorksheetFunction.ISEVEN(A1) I am raising "Object doesn't support this Property or Method" What is the correct syntax? -- Gary''s Student |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.WorksheetFunction problem
Thanks all.
I'll use Don's approach. I still can't get Tm's approach to work. Do I have to enable worksheet functions in VBA like the Analysis ToolPak -- Gary''s Student "Gary''s Student" wrote: I am trying to call ISEVEN() from VBA with: Dim b as Boolean b = Application.WorksheetFunction.ISEVEN(A1) I am raising "Object doesn't support this Property or Method" What is the correct syntax? -- Gary''s Student |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.WorksheetFunction problem
Sorry, I assumed this was a worksheet function since you were using that
qualifier. Based on what Dana has said, I look and see that is is provided by the Analysis toolpak. If you have the analysis toolpak VBA installed you can then do b = Application.run ("ATPVBAEN.XLA!IsEven", Range("A1")) to demo from the immediate window: Range("A1").Value = 4 ? Application.run("ATPVBAEN.xla!IsEven",Range("A1")) [GetMacroRegId] 'ISEVEN' < [GetMacroRegId] 'ISEVEN' - '752746504' True Range("A1").Value = 3 ? Application.run("ATPVBAEN.xla!IsEven",Range("A1")) [GetMacroRegId] 'ISEVEN' < [GetMacroRegId] 'ISEVEN' - '752746504' False the lines with the [] are debug statements that the developers apparently forgot to turn off before shipping. You only see them in the immediate window. They would not affect you code. You do see it correctly returns true or false based on the value of Range("A1") Using xl2003 -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... Dim b as Boolean b = Application.WorksheetFunction.ISEVEN(Range("A1")) -- Regards, Tom Ogilvy "Gary''s Student" wrote in message ... I am trying to call ISEVEN() from VBA with: Dim b as Boolean b = Application.WorksheetFunction.ISEVEN(A1) I am raising "Object doesn't support this Property or Method" What is the correct syntax? -- Gary''s Student |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.WorksheetFunction problem
See the lastest post.
but, it would be better to use built in VBA functionality. -- Regards, Tom Ogilvy "Gary''s Student" wrote in message ... Thanks all. I'll use Don's approach. I still can't get Tm's approach to work. Do I have to enable worksheet functions in VBA like the Analysis ToolPak -- Gary''s Student "Gary''s Student" wrote: I am trying to call ISEVEN() from VBA with: Dim b as Boolean b = Application.WorksheetFunction.ISEVEN(A1) I am raising "Object doesn't support this Property or Method" What is the correct syntax? -- Gary''s Student |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.WorksheetFunction problem
Thanks again to all of you. The latest suggestion works perfectly!
-- Gary''s Student "Tom Ogilvy" wrote: See the lastest post. but, it would be better to use built in VBA functionality. -- Regards, Tom Ogilvy "Gary''s Student" wrote in message ... Thanks all. I'll use Don's approach. I still can't get Tm's approach to work. Do I have to enable worksheet functions in VBA like the Analysis ToolPak -- Gary''s Student "Gary''s Student" wrote: I am trying to call ISEVEN() from VBA with: Dim b as Boolean b = Application.WorksheetFunction.ISEVEN(A1) I am raising "Object doesn't support this Property or Method" What is the correct syntax? -- Gary''s Student |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.WorksheetFunction problem
As Tom said,
but, it would be better to use built in VBA functionality -- Don Guillett SalesAid Software "Gary''s Student" wrote in message ... Thanks again to all of you. The latest suggestion works perfectly! -- Gary''s Student "Tom Ogilvy" wrote: See the lastest post. but, it would be better to use built in VBA functionality. -- Regards, Tom Ogilvy "Gary''s Student" wrote in message ... Thanks all. I'll use Don's approach. I still can't get Tm's approach to work. Do I have to enable worksheet functions in VBA like the Analysis ToolPak -- Gary''s Student "Gary''s Student" wrote: I am trying to call ISEVEN() from VBA with: Dim b as Boolean b = Application.WorksheetFunction.ISEVEN(A1) I am raising "Object doesn't support this Property or Method" What is the correct syntax? -- Gary''s Student |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.WorksheetFunction problem
... debug statements that the developers apparently
forgot to turn off before shipping. You only see them in the immediate window. They would not affect you code. For those not familiar with how bad this Bug is, here is a small demo. I run a few applications that use Fourier Analysis along with a worksheet with many Engineering functions. Performance is so bad, that it's best to use your own Fourier function and by-pass the ATP functions. I've tried and tried to pass it on to Microsoft, but they don't seem to see it as a problem. Ahhh! Open the vba editor, and have the immediate window available to watch. This is fast, and is not an issue. Nothing is written to the immediate window: Sub VeryFast() [A:B].Clear [A:A] = 3 [B:B].FormulaR1C1 = "=MOD(RC[-1],2)=0" End Sub This is Terrible, and is a major problem. Sub VeryBad() [A:B].Clear [A:A] = 3 [B:B].FormulaR1C1 = "=IsEven(RC[-1])" End Sub -- Dana DeLouis Win XP & Office 2003 "Tom Ogilvy" wrote in message ... Sorry, I assumed this was a worksheet function since you were using that qualifier. Based on what Dana has said, I look and see that is is provided by the Analysis toolpak. If you have the analysis toolpak VBA installed you can then do b = Application.run ("ATPVBAEN.XLA!IsEven", Range("A1")) to demo from the immediate window: Range("A1").Value = 4 ? Application.run("ATPVBAEN.xla!IsEven",Range("A1")) [GetMacroRegId] 'ISEVEN' < [GetMacroRegId] 'ISEVEN' - '752746504' True Range("A1").Value = 3 ? Application.run("ATPVBAEN.xla!IsEven",Range("A1")) [GetMacroRegId] 'ISEVEN' < [GetMacroRegId] 'ISEVEN' - '752746504' False the lines with the [] are debug statements that the developers apparently forgot to turn off before shipping. You only see them in the immediate window. They would not affect you code. You do see it correctly returns true or false based on the value of Range("A1") Using xl2003 -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... Dim b as Boolean b = Application.WorksheetFunction.ISEVEN(Range("A1")) -- Regards, Tom Ogilvy "Gary''s Student" wrote in message ... I am trying to call ISEVEN() from VBA with: Dim b as Boolean b = Application.WorksheetFunction.ISEVEN(A1) I am raising "Object doesn't support this Property or Method" What is the correct syntax? -- Gary''s Student |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.WorksheetFunction problem
I'll use Don's approach.
It's an excellent idea, but just be aware of some bugs, I mean limitations, err... features of Excel... Sub Demo() [A1] = 2147483648# If Range("A1") Mod 2 = 0 Then MsgBox "Even" End Sub Note that the number 2147483647 in binary is 31 1's. -- Dana DeLouis Win XP & Office 2003 "Gary''s Student" wrote in message ... Thanks all. I'll use Don's approach. I still can't get Tm's approach to work. Do I have to enable worksheet functions in VBA like the Analysis ToolPak -- Gary''s Student "Gary''s Student" wrote: I am trying to call ISEVEN() from VBA with: Dim b as Boolean b = Application.WorksheetFunction.ISEVEN(A1) I am raising "Object doesn't support this Property or Method" What is the correct syntax? -- Gary''s Student |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.WorksheetFunction problem
Thank you Dana:
For my simple applications, either method works. I learned 3 things: 1. just adding-in the Analysis ToolPak is not enough, VBA needs its own reference 2. some worksheet functions like ROMAN() will work with Application.WorksheetFunction 3. other worksheet functions like ISBLANK() are only for the worksheet -- Gary's Student "Dana DeLouis" wrote: I'll use Don's approach. It's an excellent idea, but just be aware of some bugs, I mean limitations, err... features of Excel... Sub Demo() [A1] = 2147483648# If Range("A1") Mod 2 = 0 Then MsgBox "Even" End Sub Note that the number 2147483647 in binary is 31 1's. -- Dana DeLouis Win XP & Office 2003 "Gary''s Student" wrote in message ... Thanks all. I'll use Don's approach. I still can't get Tm's approach to work. Do I have to enable worksheet functions in VBA like the Analysis ToolPak -- Gary''s Student "Gary''s Student" wrote: I am trying to call ISEVEN() from VBA with: Dim b as Boolean b = Application.WorksheetFunction.ISEVEN(A1) I am raising "Object doesn't support this Property or Method" What is the correct syntax? -- Gary''s Student |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.WorksheetFunction problem
You can find which ones work by using the object browser in the VBE. Select
"worksheetfunction" in the left window and you will see the supported functions in the right. -- Regards, Tom Ogilvy "Gary''s Student" wrote in message ... Thank you Dana: For my simple applications, either method works. I learned 3 things: 1. just adding-in the Analysis ToolPak is not enough, VBA needs its own reference 2. some worksheet functions like ROMAN() will work with Application.WorksheetFunction 3. other worksheet functions like ISBLANK() are only for the worksheet -- Gary's Student "Dana DeLouis" wrote: I'll use Don's approach. It's an excellent idea, but just be aware of some bugs, I mean limitations, err... features of Excel... Sub Demo() [A1] = 2147483648# If Range("A1") Mod 2 = 0 Then MsgBox "Even" End Sub Note that the number 2147483647 in binary is 31 1's. -- Dana DeLouis Win XP & Office 2003 "Gary''s Student" wrote in message ... Thanks all. I'll use Don's approach. I still can't get Tm's approach to work. Do I have to enable worksheet functions in VBA like the Analysis ToolPak -- Gary''s Student "Gary''s Student" wrote: I am trying to call ISEVEN() from VBA with: Dim b as Boolean b = Application.WorksheetFunction.ISEVEN(A1) I am raising "Object doesn't support this Property or Method" What is the correct syntax? -- Gary''s Student |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help with Application.WorksheetFunction | Excel Discussion (Misc queries) | |||
Application.WorksheetFunction.Match problem | Excel Worksheet Functions | |||
Application.worksheetfunction | Excel Programming | |||
application.worksheetfunction.mmult help | Excel Programming | |||
Using Application.WorksheetFunction.Ln(...) in VBA | Excel Programming |