Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11,058
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,337
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,337
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 690
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11,058
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11,058
Default 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




  #11   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 690
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 690
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11,058
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need help with Application.WorksheetFunction Ayo Excel Discussion (Misc queries) 4 May 14th 08 11:13 PM
Application.WorksheetFunction.Match problem Carl Brehm Excel Worksheet Functions 1 January 9th 05 02:08 PM
Application.worksheetfunction Terry V Excel Programming 7 October 12th 04 05:48 AM
application.worksheetfunction.mmult help Alex[_13_] Excel Programming 2 October 29th 03 10:13 PM
Using Application.WorksheetFunction.Ln(...) in VBA doco Excel Programming 4 August 25th 03 01:08 PM


All times are GMT +1. The time now is 02:36 AM.

Powered by vBulletin® Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"