New to VBA Functions

Rodney Jorgensen

Active Member
Joined
Nov 9, 2007
Messages
411
I have a workbook that is very heavy with formulas. I am hoping that I can turn these formulas into functions and or procedures with VBA. Can I take this simple formula that I have created and make it a function?

=IF(AND(D8=0,E8=0),"",IF(D8=E8,"Ok","Bad"))

If this can be done, how would I call it with the cell references in the spreadhsheet?

Thanks for your help.
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Generally speaking, If your reason for using code instead of formula is for Performance, stick with the formulas...

If a built in function can do it, VBA code can certainly do it, but it won't be any faster...

Just an opinion...
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
But to answer the question, here's a basic example of a UDF

Given your example formula of
=IF(AND(D8=0,E8=0),"",IF(D8=E8,"Ok","Bad"))

Code:
Public Function MyFunction(A as range,B as Range)
If A=B Then 
    MyFunction = "OK"
Else
    MyFunction = "Bad"
End if
If A="" AND B="" Then MyFunction = ""
End Function

Then you would enter a formula in the cell as
=MyFunction(D8,E8)
However, I can't stress enough that this will NOT be faster then the original formula you had. In fact, probably slower...
 
Last edited:

Rodney Jorgensen

Active Member
Joined
Nov 9, 2007
Messages
411
What about a formula like this?

=IF(AND(BB8="360 DAYS/ACTUAL DAY MOS (2/29)",BC8="ACTUAL/360"),"Ok",IF(AND(BB8="360 DAYS/30 DAY MOS",BC8="30/360"),"Ok",IF(AND(BB8="",BC8=""),"","Bad")))

What I have is a blank template with all these formulas that I copy paste special information from 2 separate database to look for differences between the 2 DB's. The spreadhseet size is quite large.

All this is done at set up when the data is pasted into the workbook. After that the formulas are not used.

Basically all I am looking for with the 2 DB's is an Ok or Bad for each data point. The ones that return Bad get reconciled. Also, very quickly, there is going to be a 3rd DB added.

I end up through the setup process remove all the formulas and have the cells retain their values.

Currently there are 32 columns that contain formulas that go down to row 2000 and with the 3rd DB comming on line quickly there will be more than 32 columns with formulas.

I am open to suggestions though on how to handle this.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,518
Messages
5,596,630
Members
414,082
Latest member
sasmita

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top