# Complicated Macros VS. Formulas



## Superstar31 (Nov 22, 2005)

I'm trying to understand why people use complicated macros when a simple formula will generate the same response? I've seen it twice so far 

http://www.mrexcel.com/board2/viewtopic.php?t=180792

and

http://www.mrexcel.com/board2/viewtopic.php?t=180664&highlight=

I'm assuming my lack of knowledge about macros is limiting my vision here, so please help me understand.

Thanks.


----------



## nbrcrunch (Nov 22, 2005)

I'm with you when it comes to using formulas, I prefer that method if it exists.

The reason some people prefer macros is that they want to permanently affect the data without creating another column or intermediate step.

Yes, it seems more work than it is worth, but I've just resigned myself to the fact that most of the time the posters have not fully explained themselves and I shouldn't expect that they need to.

This point really came home to me recently when I had a true need for a macro that I knew a formula could handle, but the specifics of the project demanded a macro.

---
addendum:
I just remember the particulars: I had two dates fields (columns) that I had imported from a sequel database.  For some reason, when the db was setup, the creator didn't specify the fields were actually dates, so they came in as padded text.  I needed to strip the blanks. I knew that TRIM() would do the job but since I do this every week, I decided to create a macro to affect the actual data.


----------



## Joe4 (Nov 22, 2005)

I have asked this question on a number of circumstances myself, and most of the I find that they are asking for a VBA solution because what they are asking is really just a small part of a much larger macro, and/or they are looking to "automate" a process.


----------



## Richard Schollar (Nov 23, 2005)

I also think sometimes that all of us (myself included!) enjoy over-complicating various tasks because we then feel like we have achieved something particularly special by proposing/effecting a solution.

Best regards

Richard


----------



## Superstar31 (Nov 23, 2005)

Those are some great answers, at least now I know I'm not crazy


----------



## NateO (Nov 23, 2005)

Hello, 

Superstar31, in the 2nd thread you originally linked to, what was the function you had in mind? The content of your post makes me wonder why you would even post there? If you had a function in mind, then so be it, but why not share it at that point? Incidentally, I have yet to see a worksheet function generate a MsgBox...  

There are lots of reasons to automate processes and craft user-defined functions, etc... with VBA in Excel. UserForms and ActiveX controls make for nice GUIs, interfacing other Apps can streamline processes and augmenting native functionality is often called for.

I'll give you a UDF example:

http://www.mrexcel.com/board2/viewtopic.php?p=849596#849596

A little hairy, perhaps. But, it can be used as a Worksheet Function, or in VBA, and it appears to be very efficient per my tests... Even if you came up with a W.F. combination that could match the functionality, a single, pseudo-random, return-String the length you want, you'd be hard-pressed to match the efficiency of that call...

Look at the name of sub in question: _Sub Example_of_Vlookup._ It appears that this person is doing what they I think they should be doing; testing smaller aspects to a process in smaller pieces before combining it all into a larger process.

My opinion: Use both; I do.


----------



## Superstar31 (Nov 28, 2005)

Hey Nato,

Neither of those post where mine, but listening to others, I do see a reason to use VB/Macros over Functions/Formulas. Since I haven't learned Macro's yet, their use will be very limited and knowledge would be far less. Either way one day i'll figure out how to use them and be able to explain to people like myselfs why they are easier done and used like you guys/girls have down for me.


----------



## nbrcrunch (Nov 28, 2005)

Here is a free online tutorial for VBA:
http://www.mvps.org/dmcritchie/excel/excel.htm#vbatutorials

and another for Excel formulas
http://www.mvps.org/dmcritchie/excel/excel.htm#tutorials


----------



## NateO (Nov 28, 2005)

Hello again,


			
				Superstar31 said:
			
		

> Hey Nato,
> 
> Neither of those post where mine, but listening to others, I do see a reason to use VB/Macros over Functions/Formulas.


I was thinking of/speaking to this one:

http://www.mrexcel.com/board2/viewtopic.php?p=868244#868244

In my estimation, VBA and Worksheet Functions are both tools that ship with Excel.

Often times, VB[A] procedures utilize shipped, native-code functions as well, e.g., _Right$()_ being part of VBA's Strings Class. And, then there are times when VBA uses Worksheet Functions...

My objective is to get Excel to do the work for me, sometimes it makes sense [to me] to do this with Worksheet Functions, and other times, VBA.

When you say complicated, keep in mind that this is in the eye of the beholder, I've seen some (what I would deem to be) complicated Worksheet Function constructs as well...


----------



## Greg Truby (Nov 29, 2005)

Hi Superstar,

Since I was one involved in the second thread, (and I'm waiting for my Access+Outlook macro to run,) I'll just take a moment to respond...  My interpretation of what I saw was pretty much as Nate described.  His sub is named Example_of_Vlookup(). And he is simply outputting a message based on the call to _app.wsf.vlookup_ so he's testing something here.  Whether it's part of a larger block of code, or whether he's trying to create an example to teach co-workers or even if he's trying to complete a homework assignment (if he is, at least he's giving the impression of having done some work and then gotten stuck).  Whatever his situation, my take on what I saw was that he would know how to write a vlookup formula in a cell.  That's why in that particular case, I didn't pursue that idea.

Now, your question is still very valid.  There are many times when someone gets locked into a "this has to be done with a macro" solution simply because he does not know what formulas are capable of doing.  Aladin has (literally) tens of thousands of posts.  He, Fairwinds and some other wunderkinder around here routinely amaze me at what they can pull off with just formulae.  I'd say if it can be done by either method, but the OP is trying to use VBA, probably ½ of the time it's because he doesn't know how to do it with a formula.  I'd guesstimate about ½ of the time he would know how to do it with a formula and has chosen VBA for reasons along the lines of what Nate or Joe cite above.


----------



## Superstar31 (Nov 22, 2005)

I'm trying to understand why people use complicated macros when a simple formula will generate the same response? I've seen it twice so far 

http://www.mrexcel.com/board2/viewtopic.php?t=180792

and

http://www.mrexcel.com/board2/viewtopic.php?t=180664&highlight=

I'm assuming my lack of knowledge about macros is limiting my vision here, so please help me understand.

Thanks.


----------



## SydneyGeek (Dec 8, 2005)

I agree with the comments here -- you use what you need to get the job done. The beauty of this Board is that you often see several ways to get the job done -- some code and some formula, in the same post. 

I sometimes find that if I use summary calcs like SUMPRODUCT, they can slow the workbook down to a crawl. So, particularly if the range sizes keep changing, I tend to create the formulas in code and then convert them to values. 

Denis


----------

