Change input arguments to a VBA function as per value in a cell

[feldspath]

So I'm using the VBA function posted here: http://chandoo.org/wp/2013/05/28/excel-risk-map/ and I want to modify the input to the VBA function `=showriskmap' as follows:

The function is

Public Function showRiskMap(inputRange As Range, searchString As String, dataRange As Range, separator As String)

For example I'm using

=showriskmap(tblRHDC_Pace[[#All],[SearchString]],"x6",tblRHDC_Pace[[#All],[Display Name On Risk Matrix]],CHAR(10))

and since I have multiple tables, I have tried putting a dropdown list of my tables in cell E11 so that when the user clicks and selects a particular table, its corresponding risk matrix is displayed.

I tried using the following formula (E12 contains the value "E11") :

=showriskmap(CONCATENATE((INDIRECT(E12)),"[[#All],[SearchString]]"),"x6",CONCATENATE((INDIRECT(E12)),"[[#All],[Display Name On Risk Matrix]]",CHAR(10)))

But excel is giving me a #VALUE! error. What am I doing wrong?

 

[NARAYANK991]

Hi ,

Your function parameter is :

inputRange As Range

which means the first parameter must evaluate to a range reference.

In your initial call :

tblRHDC_Pace[[#All],[SearchString]]

evaluates to a range reference , where the portion in blue is the table name , and the portion in red is the column reference.

Now , if your DV dropdown is in E11 , then E11 is going to contain the table name.

However , your INDIRECT function , which is going to convert a string parameter to a range reference , must have a string parameter , which it can evaluate to a range reference. Thus , the INDIRECT must be outside the CONCATENATE , and not the other way round.

You must build up a complete range reference , by concatenating the table name and the column reference , and then use the INDIRECT function on this. Thus , your formula would be :

=showriskmap(INDIRECT(E11 & "[[#All],[SearchString]]"),"x6",INDIRECT(E11 & "[[#All],[Display Name On Risk Matrix]]"),CHAR(10)))

Narayan

 

[feldspath]

Hi ,

Your function parameter is :

inputRange As Range

which means the first parameter must evaluate to a range reference.

In your initial call :

tblRHDC_Pace[[#All],[SearchString]]

evaluates to a range reference , where the portion in blue is the table name , and the portion in red is the column reference.

Now , if your DV dropdown is in E11 , then E11 is going to contain the table name.

However , your INDIRECT function , which is going to convert a string parameter to a range reference , must have a string parameter , which it can evaluate to a range reference. Thus , the INDIRECT must be outside the CONCATENATE , and not the other way round.

You must build up a complete range reference , by concatenating the table name and the column reference , and then use the INDIRECT function on this. Thus , your formula would be :

=showriskmap(INDIRECT(E11 & "[[#All],[SearchString]]"),"x6",INDIRECT(E11 & "[[#All],[Display Name On Risk Matrix]]"),CHAR(10)))

Narayan

Your solution is working!!! Thanks a lot!!!