What are the default values of VBA Method Parameters?

Question

I'm quite new to vba, and somehow I can't figure this one out. I'm trying to find out what the default values of vba method parameters are, because I would like to shorten my script (and not having to define it all the time).

As an example

Sheets(ComboBox1.Value).Columns(2).Find(What:="Example", _ LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False)

I'm using these same parameters quite a lot in a subroutine, and would like to spare myself the hassle of writing them out every time. If they were the default values anyway I could just drop them. But I can't find what the default values for this method are anywhere.

So I've come up with defining my own

Public Function FindVal(What As Variant, Optional After As Variant, _ Optional LookIn As Variant = xlValues, Optional LookAt As Variant = _ xlPart, Optional SearchOrder As Variant, Optional SearchDirection As _ Variant = xlNext, Optional MatchCase As Boolean = False, Optional _ MatchByte As Boolean, Optional SearchFormat As Variant) FindVal = Find(What=What, After=After, LookIn=LookIn, LookAt=LookAt, _ SearchOrder=SearchOrder, SearchDirection=SearchDirection, _ MatchCase=MatchCase, MatchByte=MatchByte, SearchFormat=SearchFormat) End Function

But I'm sure there's an easier way, right?

Much obliged for any help!

This may also give you some ideas for further script improvements: excelitems.com/2010/12/optimize-vba-code-for-faster-macros.html — Solar Mike
– Solar Mike, Commented Feb 15, 2017 at 11:39

Jbjstam · Accepted Answer · 2017-02-15 11:25:29Z

4

MSDN is your friend. Just search for "Range.Find" on Google.

For example: https://msdn.microsoft.com/en-us/library/office/ff839746.aspx

answered Feb 15, 2017 at 11:25

Jbjstam

8846 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Nathan_Sav Over a year ago

also, as you type them, wait for the arguments to show, the defaults will have = and the default

BenShelton Over a year ago

Exactly this. Note that for methods such as find there are a lot of optional arguments but only a few have defaults. That's because VBA will either try to guess what you want or will use the last parameter you specified for these optional arguments so be careful if you start to omit them and strange things start to happen.

hannof Over a year ago

Thanks! It's true that msdn shows you the parameters, but not always the default values. For Range.Find, it is mentioned that MatchCases has a default, but for SearchDirection it isn't (although xlNext is the default). An extra Column with the default values in case there are any would in my humble opinion be quite helpful.

Vityata · Accepted Answer · 2017-02-15 11:36:19Z

Ctrl+I is the shortcut, that you need, if you want to avoid going to MSDN every time. This is what you get, when you select Replace and press Ctrl+I:

Collectives™ on Stack Overflow

What are the default values of VBA Method Parameters?

2 Answers 2

3 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Related