PDA

View Full Version : [SOLVED] What Does This VBA Syntax Mean?



Cyberdude
04-01-2005, 07:57 PM
In my post regarding a FIND command for VBA macro lists, JKwan posted some code that works nicely, but it contains some statements I can't figure out:


With NewSheet
.[A1].Resize(, 3).Value = Array("Workbook", "Module Name", "Procedure Name")
.[A2].Resize(UBound(aList, 2), UBound(aList, 1)).Value = _
Application.Transpose(aList)
.Columns("A:C").Columns.AutoFit
End With


What does ".[A1].Resize(,3).Value + ..." mean? :doh: Specifically the ".[A1]" construct. I've never seen anything like that before.

Zack Barresse
04-01-2005, 08:06 PM
Well, it's kind of a shortcut. It should be ...


.Range("A1").Resize..

The square brackets actually stands for Evaluate. It will evaluate what is in between them as a range. It's a shortcut (basically). It's not a very efficient way of doing things because it is an extra step to take, as it will be evaluated from [A1] to Range("A1"), thus adding an extra step - an unecessary step. But we're talking semantics here, nothing noticable really.

The Resize command is basically what it says; it resizes the range in question. For the purposes of the code you posted, it resizes it for the array being entered into it. The first syntax of the Resize is omitted (nothing prior to the comma, it's left blank) which assumes that it is a null value. It would be the same thing if you put zero in front of the comma.


HTH

Cyberdude
04-01-2005, 08:24 PM
Thanx, firefytr. I understand the Resize, it's the brackets that threw me. Interesting.

BlueCactus
04-02-2005, 12:56 AM
The first syntax of the Resize is omitted (nothing prior to the comma, it's left blank) which assumes that it is a null value. It would be the same thing if you put zero in front of the comma.
Almost. You cannot use a zero in a case like this because you need a row height of at least one. Omitting it means you leave the row height value unchanged from the original range (A1).

BTW, I didn't know you could specify a range this way either. So many ways to do the same thing....

johnske
04-02-2005, 02:03 AM
BTW, I didn't know you could specify a range this way either. So many ways to do the same thing....


Hi Blue,

It's in the VBA Help files under Shortcut notation in the section "How to reference cells and ranges"... :)

John

Zack Barresse
04-02-2005, 08:21 PM
Yes Blue, sorry, I should've mentioned that. With the Resize method, it's 1. It's whatever the default number is for the method being employed. :yes