2

I'm having some trouble with a VBA module in Excel. I have a script that references cells in my spreadsheet and writes the values in comma separated lines to a text file.

The way I do it boils down to:

Dim line_data(0 to 1) As String
line_data(0) = SheetNameVar.Cells(7, "C").Value
line_data(1) = SheetNameVar.Cells(8, "C").Value

Dim line As String
line = Join(line_data, ",")

TextStreamNameVar.WriteLine line

Everything works as expected, but I keep getting values written to my text file in scientific notation if they have zeroes after the decimal. For example,

3.77265350125136E-03,0.169769407556311

I have the sheet displayed as decimals with six digits after the decimal, but that doesn't seem to affect the sheet writing.

I can't seem to find any information talking about Excel VBA converting the value notation when writing to a text stream. Is there a way to prevent this? Ideally it wouldn't rely on the sheet formatting so I can display less precision there for readability. But I need the values to appear like this in my text file:

.00377265350125136,0.169769407556311

Thanks in advance for any help!

  • 1
    try Format(SheetNameVar.Cells(7, "C").Value,"General") – Máté Juhász Dec 15 '17 at 14:17
  • That resulted in "Ge5eral" being written to the text file. I switch to "General Number" but that gives me scientific notation again. – Thomas Kirkpatrick Dec 15 '17 at 14:37
0

Changing the format on the Excel sheet itself won't affect anything here because you're pulling the data only and putting it into variables.

You can force the values to display without scientific notation by using a custom format string, like this:

line_data(0) = Format(Sheets(1).Cells(7, "C").Value, "0.##############################")
line_data(1) = Format(Sheets(1).Cells(8, "C").Value, "0.##############################")

You need at least as many # as you want to display characters for. Formatted like this will allow shorter numbers to display as their actual length, but if you put less # than you have in the number, the remaining places will be cut off. If you want to force all the numbers to the same length, you can use 0 in place of # and it will pad out shorter numbers with zeroes. Likewise, if you don't want a leading 0 before the decimal place, change that one to a #.

  • Seems like this might be the only solution. I was hoping there would be a single setting to change, rather formatting each line. I have lots of lines. I suppose I could write a function to return formatted values. – Thomas Kirkpatrick Dec 15 '17 at 15:35
  • It doesn't have to be done on the lines I chose; that was just based on what code you showed. You can do it to any variable holding a number, so it would be real easy to put your array in a loop and apply the formatting to all numbers at once. – techturtle Dec 15 '17 at 15:37
  • The array has a mixture of required data formats. There are probably ~50 lines in the whole script that need to be formatted this way, but that's spread across many arrays. – Thomas Kirkpatrick Dec 15 '17 at 16:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.