Excel 2003 > I am developing a comprehensive VBA function that needs to read the value of many cells in a worksheet. I don't want to Name all those cells because then I will need to pass too many variables to the function. I can use the following set of objects and properties ...... but that's not desirable either because the cell locations are then hard coded. Can someone suggest another solution?
Please Login or Register to view this content.
Last edited by Cecil; 02-25-2009 at 10:04 PM.
There's probably 29 answers to that, depending on what you're trying to do, and you haven't given us a hint.
A little detail?
Entia non sunt multiplicanda sine necessitate
Yes, I will try. Here is a code sample. These are just some of the variables I need in this VBA function. I don't want to use Defined Cell Names ( too many variables to pass ) and the sample below shows too many hard coded value locations.
' Retrieve Baseline, depending on Cat and Dog
Select Case Cat
Case Is = 1
Select Case Dog
Case Is = "BARK"
Baseline = ThisWorkbook.Worksheets("WS_Name").Range("B19").Value
Case Is = "SCRATCH"
Baseline = ThisWorkbook.Worksheets("WS_Name").Range("B20").Value
Case Is = "RUN"
Baseline = ThisWorkbook.Worksheets("WS_Name").Range("B21").Value
Case Is = "JUMP"
Baseline = ThisWorkbook.Worksheets("WS_Name").Range("B22").Value
Case Else
MsgBox "Dog Invalid " & Dog, vbOKOnly
End Select
Case Is = 2
Select Case Dog
Case Is = "BARK"
Baseline = ThisWorkbook.Worksheets("WS_Name").Range("C19").Value
Case Is = "SCRATCH"
Baseline = ThisWorkbook.Worksheets("WS_Name").Range("C20").Value
Case Is = "RUN"
Baseline = ThisWorkbook.Worksheets("WS_Name").Range("C21").Value
Case Is = "JUMP"
Baseline = ThisWorkbook.Worksheets("WS_Name").Range("C22").Value
Case Else
MsgBox "Dog Invalid " & Dog, vbOKOnly
End Select
Case Is = 3
Select Case Dog
Case Is = "BARK"
Baseline = ThisWorkbook.Worksheets("WS_Name").Range("D19").Value
Case Is = "SCRATCH"
Baseline = ThisWorkbook.Worksheets("WS_Name").Range("D20").Value
Case Is = "RUN"
Baseline = ThisWorkbook.Worksheets("WS_Name").Range("D21").Value
Case Is = "JUMP"
Baseline = ThisWorkbook.Worksheets("WS_Name").Range("D22").Value
Case Else
MsgBox "Dog Invalid " & Dog, vbOKOnly
End Select
Case Else
MsgBox "Cat Invalid " & Cat, vbOKOnly
End Select
Case Is you need to read the forum rules and add code tags to your code, please.
I still don't know what you want -- just how to use a range variable from which you can read the contents of various offsets?
Last edited by shg; 02-25-2009 at 07:10 PM.
Sorry, this is the first time I have inserted a considerable amount of code in one of my threads. I will study the rules to see how it should be done. Next time better.
================
Two things >
1) Yes, I would like to know how to use a range variable from which I can read the contents of various offsets.
2) Also, I will re-state the challenge to see if that clarifies things. I am writing a VBA function that needs to input about 20 variables that are stored in cells in a worksheet. Is there an optimum way of passing those variables to the function, other than Naming them all and passing them as individual parameters to the function?
You haven't whittled the possibilities much:
Please Login or Register to view this content.
Last edited by shg; 02-25-2009 at 07:45 PM.
Yes, that is good. Thank you.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks