User-defined function to get shape properties

[ehouse]

Hello,

I have adapted the map by Jordan Goldmeier to my own work, but I cannot figure out how to adapt and use the user-defined function to get shape's properties. I had to use a less convenient macro for this.

Could someone please explain in more detail how this user-defined function works and how to adapt it?
Specifically, I don't quite follow where the strState comes from. How does excel know where that comes from? I didn't see it defined elsewhere.

The VBA code is as follows:

Public Function GetTop(strState As String)

GetTop = Sheet2.Shapes("S_" & strState).Top

End Function

Public Function GetLeft(strState As String)

GetLeft = Sheet2.Shapes("S_" & strState).Left

End Function

Public Function GetHeight(strState As String)

GetHeight = Sheet2.Shapes("S_" & strState).Height

End Function

Public Function GetWidth(strState As String)

GetWidth = Sheet2.Shapes("S_" & strState).Width

End Function

The excel document can be downloaded from here:
http://optionexplicitvba.blogspot.ca/2012/08/interactive-map-in-excel-using-rollovers.html

Here is the file:
https://drive.google.com/file/d/0B1OBNnu3ZbL0VXFvZFFCa1J3UTA/view

Thank you for your help,

Emily

 

[ehouse]

I think I may have figured it out.

This code seemed to work for me:

Public Function GetTop(strMunicipality As String)
Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet
Set ws = wb.Sheets("Dashboard")
GetTop = ws.Shapes(strMunicipality).Top
End Function

There were two problems I had:
1. I had to make sure that the cell I was referring to had the exact same name as the shapes. No extra spaces.
For example, if you type =GetTop(C14) C14 must have the exact same name as the shape.

2. I think I needed to have the sheet specifically defined. I renamed my sheet and typing in the new name where Sheet2 didn't help. So defining it first then having the generic ws before Shapes helped.

I don't think the name of strState or strMunicipality matters. I think it is just a name to let the VBA know that the shape name in your cell is going to be a string. Hopefully that's correct.
If I'm mistaken, please let me know.