Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax needed for With
Hi,
Task at hand is to hide those columns, whose headings are on the Named Range "Elements" list (stored aside) I used to do it via plain looping but I saw recently clever usage of With construct. I would like to learn how to put With into use and start using objects more. TIA Paul |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax needed for With
Hi Paul,
There is no need for With this requirement. There is nothing clever about With, it is just used to remove duplicated object qualifying, thereby making the code more efficient as VBA does not need to look up the reference each time, the With stores that reference in memory, and makes the code easier to read IMO. Here is the code you want Dim col As Range For Each col In ActiveSheet.Range("Elements").Columns col.Hidden = True Next col -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "count" wrote in message ... Hi, Task at hand is to hide those columns, whose headings are on the Named Range "Elements" list (stored aside) I used to do it via plain looping but I saw recently clever usage of With construct. I would like to learn how to put With into use and start using objects more. TIA Paul |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax needed for With
Hi Paul
with is usually used to avoid typing the object reference over and over again. So kind of reduced typing. to give you an example 1. Without using With: sub foo_1() Dim wks as worksheet dim rng as worksheet set wks = activesheet set rng = wks.range("A1:A100") rng.value = 1 rng.offset(0,1).value = 2 rng.offset(0,2).value = 3 wks.printout end sub 2. Using with sub foo_2() Dim wks as worksheet dim rng as worksheet set wks = activesheet with wks set rng = .range("A1:A100") with rng .value = 1 .offset(0,1).value = 2 .offset(0,2).value = 3 end with .printout end with end sub -- Regards Frank Kabel Frankfurt, Germany "count" schrieb im Newsbeitrag ... Hi, Task at hand is to hide those columns, whose headings are on the Named Range "Elements" list (stored aside) I used to do it via plain looping but I saw recently clever usage of With construct. I would like to learn how to put With into use and start using objects more. TIA Paul |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax needed for With
Ooops,
I haven't make myself clear enuf :) NamedRange "Elements" is vertical 1 column list of interesting headings Sheet to treat has oodles more columns - only some will get hidden - those that ... you know. thanks for your sooo quick response Paul Użytkownik "Bob Phillips" napisał w wiadomo¶ci ... Hi Paul, There is no need for With this requirement. There is nothing clever about With, it is just used to remove duplicated object qualifying, thereby making the code more efficient as VBA does not need to look up the reference each time, the With stores that reference in memory, and makes the code easier to read IMO. Here is the code you want Dim col As Range For Each col In ActiveSheet.Range("Elements").Columns col.Hidden = True Next col -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "count" wrote in message ... Hi, Task at hand is to hide those columns, whose headings are on the Named Range "Elements" list (stored aside) I used to do it via plain looping but I saw recently clever usage of With construct. I would like to learn how to put With into use and start using objects more. TIA Paul |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax needed for With
Frank,
thanks - I got it! Your example is clear to follow. I'll start using ranges technique instead of looping through cells. Danke Paul Uzytkownik "Frank Kabel" napisal w wiadomosci ... Hi Paul with is usually used to avoid typing the object reference over and over again. So kind of reduced typing. to give you an example 1. Without using With: sub foo_1() Dim wks as worksheet dim rng as worksheet set wks = activesheet set rng = wks.range("A1:A100") rng.value = 1 rng.offset(0,1).value = 2 rng.offset(0,2).value = 3 wks.printout end sub 2. Using with sub foo_2() Dim wks as worksheet dim rng as worksheet set wks = activesheet with wks set rng = .range("A1:A100") with rng .value = 1 .offset(0,1).value = 2 .offset(0,2).value = 3 end with .printout end with end sub -- Regards Frank Kabel Frankfurt, Germany "count" schrieb im Newsbeitrag ... Hi, Task at hand is to hide those columns, whose headings are on the Named Range "Elements" list (stored aside) I used to do it via plain looping but I saw recently clever usage of With construct. I would like to learn how to put With into use and start using objects more. TIA Paul |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF, THEN SYNTAX | Excel Worksheet Functions | |||
VBA syntax | Excel Discussion (Misc queries) | |||
IRR Syntax | Excel Worksheet Functions | |||
VBA code to sum a row: syntax needed | Excel Discussion (Misc queries) | |||
Help with VBA syntax | Excel Programming |