Using VB variables with Excel cell formulas. 2

[chrishooper]

Hi there,

I am trying to create Excel cell formulas that can use variables created during the execution of code in VB. In particular I would like to know if it would be possible to specify the sheet selection portion of the INDEX function based on a VB variable. For example,

If my basic INDEX function looked like this:

INDEX('Sheet Name'!D3:K6,1,1)

Could I replace the sheet name with a string variable defined in a VB subroutine like this

VB CODE:::
dim sheetname as string
sheetname = "Sheet no. 1"

EXCEL FORMULA:::
INDEX(sheetname!D3:K6,1,1) **this doesn't work yet**

I have thought about writing the string to an excel cell first in VB then picking it up with the excel formula but still without any joy. Can anyone tell me if such a procedure is possible? What syntax must i use to make it work?

Thanks in advance,

Chris.

 

[VulcanJedi]

you can use Application.WorksheetFunction.Index(arg1, arg2...etc) Tranpkp
************************************
- Let me know if this helped/worked!
Please remember to give helpful posts the stars they deserve!
This facilitates others navigating through the threads / posts!

 

[chrishooper]

To write the formula's to the cells?? I can't do this for reasons that would be too complicated to easily explain.

Is there no syntax for calling a string in place of the sheet name portion of the function?

 

[chrishooper]

sorry yeah, i kind of see how it might work though i'm not sure how to use it at all. do you have to then write the new formula's to excel cells? how is this done?

 

[RobBroekhuis]

Chris,
I don't think what you're trying to do is possible in a straightforward manner (the way I understand it, you're trying to enter into a cell's formula a reference to a cell on another worksheet where the worksheet is variable).
If you tell us more about WHY you are trying to do this, maybe the forum will come up with a different elegant approach that would suit your purposes.
Rob

 

[chrishooper]

Right well i will have a block of cells on one sheet which will contain versatile formulas such as the index functions. It will be kind of a black box calculator. This must be copiable to any number of other sheets. At the same time it must retain the ability to access data from a number of source data sheets, based on user input. In other words the user will be able to select which source sheet it reads from based on combobox listindex outputs.

I have already implemented the functions described for a single data source sheet, but found it necessary to divide the data into multiple sheets. Now I cannot find a way to use the copied index functions to access any sheet.

Gawd, i hope that makes sense....

Chris

 

[RobBroekhuis]

Can EACH of these index calculations be based on a DIFFERENT source data sheet, or is the source data sheet for all calculations the same (at least until the user changes it to a different one, which would again apply to ALL calculations?
If the latter case, it's fairly easy - if the former, it's more difficult...
Rob

 

[chrishooper]

each calc within a single copied black box with use the same data sheet. However there will be multiple instances of the black box which must each be able to utilised a different source sheets.

 

[RobBroekhuis]

In that case, you'll probably need to write an event handler that catches the change of the source worksheet cell by the user, and goes around changing the formulas when that happens. A bit cumbersome, but perfectly doable.
Rob

 

[chrishooper]

Aye, I thought as much. In that case could you have a quick look at this:

Dim range As Variant
range = D1: D25
Formula1 =
Application.WorksheetFunction.Index(typeselectst, range, attvelrow, 1)

Worksheets("sheet16&quot.Activate
ActiveSheet.[d10].Select
ActiveCell.Formula = Formula1

my problem is now getting a defined range of the source data into the second argument of the function which does not accept the form cell1:cell2 like when using the function in excel itself.

Also, have i got the syntax right for putting the formula into the excel cell?

 

[RobBroekhuis]

Again, I'm not quite sure what you're trying to accomplish. Don't use "range" as a variable name - that will confuse Excel. The following code complies with Excel VBA syntax - but I don't know what you're trying to do with it.

Dim r As range
set r = range("D1: D25&quot
Formula1 =
Application.WorksheetFunction.Index(typeselectst, r, attvelrow, 1)

Worksheets("sheet16&quot.Activate
ActiveSheet.range("d10&quot.Select
ActiveCell.Formula = Formula1

The index function will not (as far as I know) give a reference including a worksheet name, so it's not too useful in your situation. I think you'll need VBA code with statements such as:

s=range("MyCalc&quot.formula
p=InStr(s,"!&quot
range("MyCalc&quot.formula = "'" + NewSheetName + "'" + mid(s,p)

where MyCalc refers to a cell containing one of your calculations, and NewSheetName is the source sheet name selected by your user.
Does that make sense?
Rob

 

[chrishooper]

Hmmm i'm nearly there i think. I'm still having problems with the syntax for putting the formula into the cell (see **)

Dim formula1 As String
**formula1 = "=INDEX("" & typeselectst & ""!D525," & attvelrow & ",1)"

Worksheets("sheet16&quot.Activate
ActiveSheet.range("d10&quot.Select
ActiveCell.Formula = formula1

**should produce eg.
=index("sheet 1"!D525,3,1) but i get an application defined error.

 

[VulcanJedi]

formula1 = "=INDEX(" & CHR(34) & typeselectst & CHR(34) & "!D525," & attvelrow & ",1)"
Tranpkp
************************************
- Let me know if this helped/worked!
Please remember to give helpful posts the stars they deserve!
This facilitates others navigating through the threads / posts!

 

[chrishooper]

No joy there Tranpkp

 

[RobBroekhuis]

or:

formula1 = "=INDEX(""" & typeselectst & """!D525," & attvelrow & ",1)"

 

[tmktech]

chris,

I think one not-well-known, yet powerful command will make this much easier for you.

The INDIRECT excel function lets you dynamically string functions variables together (same as the Concatenate function). So, in your case, write the variable worksheet name to a cell as you planned (say A1).

Your INDEX command will now look like this:

INDEX(indirect(A2&"'!D3:K6,1,1&quot)

The only downfall is that the rest of the formula must also be conastants and/or variables.

Let mw know how it goes.

TMKTECH

 

[tmktech]

Chris,

That indirect command was supposed to reference A1 not A2.

TMK

 

[RobBroekhuis]

Yowza, TMK! I'd never heard of that one. I think that will make things much easier for Chris!
Rob

 

[chrishooper]

Oooh so close:

INDEX(indirect(A2&"'!D3:K6,1,1&quot)

returns a error "you have entered too few arguments", so i tried various permutations:

=INDEX((INDIRECT($A$2)&"!D5:K25&quot,1,1)

=INDEX((INDIRECT($A$2&"!D5:K25&quot,1,1)

with no joy. The index function has three separate arguments and i only need the indirect function to concencate the first argument.

 

[xlbo]

=INDEX((INDIRECT($A$2&"!D5:K25&quot,1,1) should work syntax wise - what is in A2 ??
Rgds
~Geoff~