Menu
New to VBA. I'm attempting to build a value of Dimensions (pulling from two different cells in an excel spreadsheet in which one might be larger than the other, and I always want the lower number first) in which the output (a string which will be concatenated with strings from other functions) might be one of the following:
4868 (no x separating the integer values) 48x60.5 (with x separating an integer and real number) 36.5x60 (with x separating a real number and an integer) 24.75x72.125 (with x separating a real number and an integer)
Variable types are defined in VBA as Single (not Double). Here's my code:
Function getDimDisplay(h As Single, w As Single) As String
Dim strResult As String
Dim iH As Integer
Dim iW As Integer
Dim strH As Variant
Dim strW As Variant
iH = CInt(h)
iW = CInt(w)
Select Case h
Case (h >= w And iH = h And iW = w)
strH = CStr(iH)
strW = CStr(iW)
strResult = strW & strH
Case (h >= w And iH <> h And iW = w)
strH = CStr(h)
strW = CStr(iW)
strResult = strW & "x" & strH
Case (w >= h And iH = h And iW <> w)
strH = CStr(iH)
strW = CStr(w)
strResult = strH & "x" & strW
Case (w >= h And iH <> h And iW <> w)
strH = CStr(h)
strW = CStr(w)
strResult = strH & "x" & strW
End Select
getDimDisplay = strResult
End Function
It will compile, but it won't return any output. What gives?
249
Example 9 – Nested Select Case StatementsYou can also nest one Select Case statement within other.
Select one or more rows and columns Or click on any cell in the column and then press Ctrl + Space. Select the row number to select the entire row. Or click on any cell in the row and then press Shift + Space. To select non-adjacent rows or columns, hold Ctrl and select the row or column numbers.
The Select Case VBA statement compares an expression with multiple Case statements containing conditions. If a match is found in any of the Case statements, the condition is said to be true. Further, if a condition is true, its corresponding code is executed and no more Case statements are tested.
your variable 'h' is not a boolean. However, you're calling it in select case to match conditions which are either true or false.
Change your "select case h" to "select case true". all else will work ok.
Select Case True
Case (h >= w And iH = h And iW = w)
strH = CStr(iH)
strW = CStr(iW)
strResult = strW & strH
Case (h >= w And iH <> h And iW = w)
strH = CStr(h)
strW = CStr(iW)
strResult = strW & "x" & strH
Case (w >= h And iH = h And iW <> w)
strH = CStr(iH)
strW = CStr(w)
strResult = strH & "x" & strW
Case (w >= h And iH <> h And iW <> w)
strH = CStr(h)
strW = CStr(w)
strResult = strH & "x" & strW
End Select
Select Case doesn't work like this. It compares the item presented (h) to the values calculated for the individual case statements.
The case statements you have all evaluate to a bool, true or fasle. Whatever h equals, it's not that! For this bit of code, you nedd an if then else if structure.
41
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
