Need help to creat a macro that shows the exact format from a cell. For example I created a macro that adds 1 to a number in a cell but the msgbox shows 2 instead of the actual value of the cell which is 20-002. How do I get the msgbox to show the user 20-002 instead of just 2.
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Macro for Msgbox
0
Answers
0
Say, your cell is A2. You can address it as Range("A2") or Cells(1, 1) or - easier to read but harder to program - Cells(2, "A"). I don't recommend to address a cell as a "Range" because ranges have names and cells have coordinates, and it's not only hard to "calculate" a name from coordinates - like Range("A" & RowNumber) - but also senseless since I don't need the name when I have the coordinates. Moreover, Excel, being number driven, will convert the name back to coordinates.
All ranges have a Value property, a Formula property and a Value2 property. In your example, the Cells(2, 1).Value = "20-002", Cells(2, 1).Value2 = "20-002" and Cells(2, 1).Formula = "20-002". However, if Cells(2, 1).Formula =TODAY() then Cells(2, 1).Value2 = 44105 (the numeric value of today's date) and Cells(2, 1).Value = 01/10/2020 (depending upon your regional settings).
And then there is the VAL() function which belongs to VBA. Excel has an equivalent in VALUE(). Either function takes a string as parameter and converts that string to a number. Note that the two functions work differently however. VAL("20-002") will return 20, as you have already found, but =VALUE("20-002") will return a #VALUE error.
Now, as you can see from the above, any of Cells(2, 1).Value, Cells(2, 1).Value2 and Cells(2, 1).Formula will return the "20-002" you are seeking. It's only Val(Cells(2, 1).Value)) that will return 20. The reason is that Val is a function and must not be confused with the cell's Value property.
Of course, a range's Value property is its default property, meaning it can be omitted and this typically Microsoft way of being helpful is the source of no end of confusion. Val(Cells(2, 1)) will return the same result as Val(Cells(2, 1).Value) and that throws doubt on what Cells(2, 1) is. Well, Cells(2, 1) is a rangeand the universal recommendation by all programmers is not to omit the default property even though MS has enabled this. MS may have had newbies in mind when they made this arrangement but, in effect, only newbies take "advantage" of it. For everybody else it's just too time consuming to hunt for the errors that flow from such omission, of which your question here is an example: You wouldn't have had the need to ask it had you realized that you were dealing with Cells(2, 1).Value