I am glad to hear that you like my function and that is a good question about ListObjects. It is certainly possible and I have seen it mentioned in other website. The trickiest part is finding a way to either update the UDF to take into account ListObjects or being able to reference the table / ListObject directly into the 2nd argument of the sql function. When I tried referencing the table name directly, I got an error message from the function so there is probably a different syntax involved. I have been spending a couple of hours trying to figure out how I can update the sql function to work with both ranges and listobjects without making the function be clunky and actually be usable for both situations. If you want to try this yourself, try using the links below to see how you can update the function to include listobjects: Link 1: stackoverflow.com/questions/69504405/excel-listobject-format-as-table-in-sql Link 2: stackoverflow.com/questions/47029764/use-table-name-in-sql-query-in-vba-excel In the meantime, I am still figuring out how to include listobjects without making the function look or go bad. Hope that helps!
I have been playing with this for some time now. The trick appears to be getting the tables address and converting it into a range name, which can be used directly in a SQL statement. I have gotten close to being able to do that, but in a clunky manner.
@s.triyambakpatro7341 thank you! Yes, you can use this use defined function on an add-in file. I created the Excel Add-In file beforehand. You can save an excel file as XLAM (If you have not done so already) and just paste the code (from my description) directly into the VB Editor. If you enable the add-in file, you should be able to use the user defined function in any workbook. Please note that this should work for windows, but not sure about MAC as you would need still need to be able to reference the active x data object library in VBA. If you like this video, then please show this to others who might be interested and let me know if there are any other videos you want to see! Hope that helps!
I like your function. Is it possible for you to modify it so that it works on Excel Tables (ListObjects)?
I am glad to hear that you like my function and that is a good question about ListObjects.
It is certainly possible and I have seen it mentioned in other website. The trickiest part is finding a way to either update the UDF to take into account ListObjects or being able to reference the table / ListObject directly into the 2nd argument of the sql function.
When I tried referencing the table name directly, I got an error message from the function so there is probably a different syntax involved.
I have been spending a couple of hours trying to figure out how I can update the sql function to work with both ranges and listobjects without making the function be clunky and actually be usable for both situations.
If you want to try this yourself, try using the links below to see how you can update the function to include listobjects:
Link 1: stackoverflow.com/questions/69504405/excel-listobject-format-as-table-in-sql
Link 2:
stackoverflow.com/questions/47029764/use-table-name-in-sql-query-in-vba-excel
In the meantime, I am still figuring out how to include listobjects without making the function look or go bad.
Hope that helps!
I have been playing with this for some time now. The trick appears to be getting the tables address and converting it into a range name, which can be used directly in a SQL statement. I have gotten close to being able to do that, but in a clunky manner.
Excellent. Cant we use this as an Add-in?
@s.triyambakpatro7341 thank you!
Yes, you can use this use defined function on an add-in file. I created the Excel Add-In file beforehand. You can save an excel file as XLAM (If you have not done so already) and just paste the code (from my description) directly into the VB Editor.
If you enable the add-in file, you should be able to use the user defined function in any workbook.
Please note that this should work for windows, but not sure about MAC as you would need still need to be able to reference the active x data object library in VBA.
If you like this video, then please show this to others who might be interested and let me know if there are any other videos you want to see!
Hope that helps!