Option Explicit - Not sure how to dim this

Question

I have an existing macro that I use to format columns. I've been using this without problems. Now, I'm looking to learn how to use Option Explicit and I am running into a problem with defining my variable.

What should I be dim'ing Level as? I tried Dim Level As String but that didn't work. I'm trying to get a better understanding so any feedback would be appreciated.

Option Explicit Sub adviseformat() Dim Form As Worksheet Set Form = Sheets("Formatting") With Form Level = WorksheetFunction.Match("Level", .Rows("1:1"), 0) .Columns(Level).Delete .Columns("D:E").Delete .Range("U:U").Value = Range("E:E").Value .Columns("E").EntireColumn.Delete .Columns("F:I").Delete .Columns("I").Delete .Columns("L").Delete .Columns("M").Delete Form.Range("A:B").EntireColumn.Insert Form.Range("A1").Value = "Owner" Form.Range("B1").Value = "Comment" Form.Range("A1").Interior.Color = 65535 Form.Range("B1").Interior.Color = 65535 Form.Range("O1").Interior.Color = 65535 End With End Sub

IIRC, its a Range, ... Though then you should be using Set on it. Try a Variant, that should work. — RBarryYoung
– RBarryYoung, Commented Jun 10, 2015 at 13:49
@RBarryYoung : It isn't a range, it is an array : msdn.microsoft.com/library/office/ff835873.aspx — R3uK
– R3uK, Commented Jun 10, 2015 at 14:10
I tried Dim Level As Variant but I get "Run-time error '1004': Application-defined or object-defined error" — xslyx
– xslyx, Commented Jun 10, 2015 at 14:13
@bonCodigo - I'm erroring out Level = WorksheetFunction.Match("Level", .Rows("1:1"), 0) — xslyx
– xslyx, Commented Jun 10, 2015 at 14:39
@R3uK You are correct, it is not a Range, however, the doc you link to also says that it is not an array either, but rather a Double. — RBarryYoung
– RBarryYoung, Commented Jun 10, 2015 at 14:48

KevenDenen · Accepted Answer · 2015-06-10 14:21:24Z

2

As you type the WorksheetFunction.Match part, the VBA editor should pop up and give you a clue to the return type. It should say something like:

Match(Arg1, Arg2, [Arg3]) as Double

The "As Double" part tells you the return type of the Match function. This is the type you should use to declare your Level variable.

answered Jun 10, 2015 at 14:21

KevenDenen

1,72213 silver badges26 bronze badges

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

4 Comments

xslyx Over a year ago

Hi Keven, thanks for responding. I just tried setting Level As Double but I am still getting the "Run-time error '1004' "

KevenDenen Over a year ago

Double is the correct data type here. Error 1004 is totally unrelated to the data type you are setting. Check support.microsoft.com/en-us/kb/818808 for documentation on error 1004.

xslyx Over a year ago

Keven, bonCodigo, R3uk I found the issue. Keven was right, Double is the correct variable to use here. The 1004 error I'm receiving is due to an incorrect header in my dataset. Thank you!

bonCodigo Over a year ago

@xslyx, post that as the answer as per site rules here. So it helps future visitors.

Community · Accepted Answer · 2020-06-20 09:12:55Z

Looking on MSDN, I found this :

MATCH returns the position of the matched value within lookup_array, not the value itself.

For example, MATCH("b",{"a","b","c"},0) returns 2, the relative position of "b" within the array {"a","b","c"}.

So my guess is that you should use Dim Level As Variant

Hey R3uk, I tried Dim Level As Variant but I get Run-time error '1004'

Collectives™ on Stack Overflow

Option Explicit - Not sure how to dim this

2 Answers 2

4 Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Related