ANmarS - Search results

Excel/VBA expert since Sep 1997 (20 years 9 months)

We are moving ...

We are moving to another location, better, faster and more resources .... check it out ANmar.Systems

2 Q & A posts tagged with user-defined-function

Mon
Apr 5
2010

How do you run a macro within a formula in microsoft excel 2007?

.... for example If(A2=A3, "Run Macro", "")

thats what i want to do, i just dont know how to do it


Answer

A formula is ACTUALLY a macro
It is FUNCTION macro and not SUB macro

So, to do that, you need to replace the lines of your macro
from
Sub WhatEverName()
....
End Sub

TO

Function WhatEverName()
...
End Function

AND, putting that function in a module (and not a form module) will enables you to run it as formula
However, you need to go to "User-Defined Functions" in the Insert Function dialog and you should see it there

Keep in mind that this is a macro, means that you need to have the macros enabled for that file to make it run

Also, you can run that formula from any opened file, as long as the file that has the function (the function macro) is open

You can see it in action in this free file
http://www.file1.net/project.asp?id=b2vi…


Does that make sense? let me know if not

VBAXLMan http://www.file1.net/project.asp?id=b2vi…

Monday 4/5/2010 4:33:00 PM
Tue
Apr 7
2009

Filtering data in Excel 2007 based on formatting?

I have two columns I need to filter. Some have text and some have text with strike-through (a line going through the text). Does anyone know how I can filter based on the strike-through - or if that's not an option can I filter based on anything that is not formatted like the regular text so it will separate the cells with the strike-through? Thank you.


Answer

There have been a lot of requests to read format through formulas

Since it is nit supported yet, here is a user definec function to do that

Function IsStrickeThrough(CellAddress As Range) As Boolean
If CellAddress.Rows.Count = 1 And CellAddress.Columns.Count = 1 Then
IsStrickeThrough = CellAddress.Font.Strikethrough
End If
End Function

To use it...
- Press ALT + F11
- Insert > Module
- Paste this
- Now go to any cell and use it like this
=IsStrickeThrough( A1)
Assuming the cell you want to check is A1



Enjoy it



VBAXLMan

Tuesday 4/7/2009 9:01:21 AM

Tags

History