PDA

View Full Version : using cell names: excel => vba



choubix
02-16-2008, 02:28 PM
hello,

In my excel spreadsheet I have defined cells as follows:
history = -5
start_date = (returns a date x years before the end_date)
end_date = today()


I have written this code:

Sub Retrieve()

Dim start_date, end_date As Date
Dim history, nItems As Integer
Dim i As Integer

Call Deactivate
[Date].ClearContents

nItems = DateDiff("yyyy", end_date, start_date)

For i = 1 To nItems

ActiveSheet.Cells(15, i) = "test"

Next

'Call Activate

End Sub
when I click on the button which is linked to the macro nothing happens... I think I am not calling the cells start_date and end_date correctly in vba.

can someone tell me what's wrong please?


by the way: is it ok to use "call deactivate" and "call activate" as I do to speed up the process? (turn on/off automatic calculation)


thanks! :)

ps: sorry for the code, I am just beginning with VBA...

choubix
02-16-2008, 03:18 PM
i tried using 2x quotes (e.g: "start_Date" and "[start_date].Value")
I get an error message.

it works properly if I use normal dates.

let me know if you ahve any idea

thanks :)
+++

Bob Phillips
02-16-2008, 04:53 PM
Wouldn't it be a good idea to put something in the variables called end_date and start_date? DateDiff might do something then.

mdmackillop
02-16-2008, 05:06 PM
If these are range names, you need

nItems = DateDiff("yyyy", Range("end_date"), Range("start_date"))

Turning calculation on and off is not necessary unless you have a large number of associated calculations occurring. The most common way to speed up excecution is to prevent the screen from refreshing.

Application.ScreenUpdating = False/True

choubix
02-16-2008, 06:40 PM
hi,

Thanks XLS, thanks mdmackillop
I thought I could call directly in vba cells that were defined with name in Excel... (or with a [NameOfTheRange].Value)

I'm going to read my "mastering vba programming" this week.
Hopefully I'll have more complex questions for you :)

Thanks again for your help!

I'll check how the screen updating function works and will certainly use it pretty soon!

+++
Alex

Bob Phillips
02-17-2008, 02:43 AM
You can as MD showed. What you cannot do is define variables and then use them without loading them first (well actually you can do it, but it probably does not do what you want).

johnske
02-17-2008, 04:18 AM
You also need to explicitly declare each and every variable type, this...

Dim start_date, end_date As Date
Dim history, nItems As Integer
only declares end_date As Date, and nItems As Integer. Because start_date and history haven't been explicitly declared, Visual Basic automatically declares them as being of type Variable...

choubix
02-17-2008, 10:28 AM
hi!
thanks for all your replies! :bow:

so if I understand correclty, a better syntax would be:

Dim start_date As DATE, end_date As DATE

I saw in an addin that ranges could be declared using brackets.
e.g: [start_date]

I tried using this in my code but it returned errors.
any idea how I can do that correctly please? Is it "clean"?


I am now working on a Vlookup. it works correctly.
the only thing is that, in the spreadsheet I have defined "Tickers"
the cells in this range can either contain a string or can be empty.

the funny thing is: with my code, even when the cells in "Tickers" are empty vba returns a #N/A result. :think:

does anybody have an idea of why this doesnt' work please??


Thanks! :thumb

Norie
02-17-2008, 10:48 AM
Alex

I think you really need to clarify what you have and what you are doing.

If you had a named range called end_date on a worksheet you could refer to it like this in code.

[end_date]

choubix
02-17-2008, 11:23 AM
Hi Norie,

what I have:

worksheets:
- data_retrieval
- ticker_list

I gave a name to Cells and Ranges (at worksheet level)
- StartDate, EndDate,
- History,
- ClearMain
- Tickers
- FinancialCenters
- Dates
- TickerList
- TickersExchange

my understanding is that you can call these cells and ranges in VBA using the brackets.

for instance this doesn't work: (error: invalid qualifier)
[myrange].columns.count

but this works:
range("myrange").columns.count

I found it strange that it doesn't work (especially since you are telling me that, when the cells are defined at the worksheet level I can use the brackets)


the following macro returns a financial center attached to a ticker using a Vlookup.
the thing is: when I run the macro the financial centers are correctly retrieved but the macro also returns a N/A result in all the cells for which there is no ticker. (problem with the "columns.count" ??)



For i = 1 To Range("Tickers").Columns.Count

Application.StatusBar = "Please wait : retrieving Financial Centers"

vTicker = Range("Tickers").Columns(i)
Range("FinancialCenters").Columns(i) = Application.VLookup(vTicker, Range("TickersExchange"), 2, False)

Next

I really appreciate your help since I am green to VBA and that I am trying to get it "right". I hope this post will help you better understand what I have and what I what to do :)

+++

Bob Phillips
02-17-2008, 11:30 AM
hi!
thanks for all your replies! :bow:

so if I understand correclty, a better syntax would be:

Dim start_date As DATE, end_date As DATE

I saw in an addin that ranges could be declared using brackets.
e.g: [start_date]

I tried using this in my code but it returned errors.
any idea how I can do that correctly please? Is it "clean

A better syntax would be to not declare variables that you don't need, don't use that horrible short-cut notation, and access it properly

Range("start_Date"). value

etc.

Norie
02-17-2008, 11:30 AM
Alex

This sort of thing worked for me.

MsgBox [end_date].Columns.Count

But I should have said in my previous post that I wouldn't recommend using this type of syntax in the first place.:)

And what would you expect the code to return if the tickers weren't there.:huh:

choubix
02-17-2008, 12:02 PM
ok, got the message as per the variable declaration.
as for the loop I may have misunderstood how the range("Tickers").Columns.Count woudl work.

I thought I would stop counting when finding an empty cell. instead of what it keeps counting. I think I just need to break the loop when i find an empty cell in the range "Tickers".

Will go back to my Vbe and practice then!

thanks!

Bob Phillips
02-17-2008, 12:07 PM
That was just an example of using that horrible short-cut notation. You want

If Range("Tickers").Value <> "" Then

choubix
02-17-2008, 12:25 PM
I'll try your method in the FOR loop.

In the meantime I have worked it out with this Do Loop

i = 0
Do

Application.StatusBar = "Please wait : retrieving Financial Centers"
i = i + 1
vTicker = Range("Tickers").Columns(i)
Range("FinancialCenters").Columns(i) = Application.VLookup(vTicker, Range("TickersExchange"), 2, False)

Loop Until IsEmpty(Range("tickers").Columns(i + 1))

Bob Phillips
02-17-2008, 12:48 PM
There seems a lot of redundancy in that code, and loading a whole column not a cell.