VBA Column Numbering System Jumbled

[Ed Song]

I'm writing a VBA program that writes the results from mathematical equations into cells. To print the output in the proper cells I write:

'Print output
Sheets(1).Cells(4, 16) = Output_t1
Sheets(1).Cells(4, 17) = CapitalStock_t1
Sheets(1).Cells(4, 18) = Consumption_t1
Sheets(1).Cells(4, 19) = Investment_t1
Sheets(1).Cells(4, 20) = Government_t1
Sheets(1).Cells(4, 21) = TotalDeadWtLoss_t1
Sheets(1).Cells(4, 22) = Energy_Usage_Consumption_t1
Sheets(1).Cells(4, 23) = Energy_Usage_Production_t1
Sheets(1).Cells(4, 24) = Energy_Usage_t1
Sheets(1).Cells(4, 25) = AtmoEmissions_t1

At first, the program was working fine. Then for some unknown reason, VBA started putting the output in the wrong cells. What was suppose to be in one cell was put into another cell. I'm so confused which number was put into what cell, that I'm too confused to tell you. But if I write the output like this, then the numbers are in the right cell.

'Print output
Sheets(1).Cells(4, 16) = Output_t1
Sheets(1).Cells(4, 17) = CapitalStock_t1
Sheets(1).Cells(4, 18) = Consumption_t1
Sheets(1).Cells(4, 19) = Investment_t1
Sheets(1).Cells(4, 20) = Government_t1
Sheets(1).Cells(4, 24) = TotalDeadWtLoss_t1
Sheets(1).Cells(4, 21) = Energy_Usage_Consumption_t1
Sheets(1).Cells(4, 22) = Energy_Usage_Production_t1
Sheets(1).Cells(4, 23) = Energy_Usage_t1
Sheets(1).Cells(4, 25) = AtmoEmissions_t1

The numbers are now in the right cells, but it's very confusing because Cells(4, 24) should correspond to cell X4, but the number is being written now in Cell U4. Somehow, my Excel/VBA program started jumbling the column numbering system. Is there a fix or do I have to use the unusual labeling system, where the numbering system is out of order.

 

[shg]

How about ...

  Sheets(1).Range("P4:Y4").Value = _
  Array(Output_t1, CapitalStock_t1, Consumption_t1, Investment_t1, Government_t1, TotalDeadWtLoss_t1, _
        Energy_Usage_Consumption_t1, Energy_Usage_Production_t1, Energy_Usage_t1, AtmoEmissions_t1)

 

[Ed Song]

Thanks for your response. However, I'm writing results from VBA into the Excel Cells, not assigning values from the Excel Cells to the named variables in VBA.

 

[Ed Song]

Apparently, Excel labled the X column as U, then moved the U, V, and W columns down one column. I wonder if this happened when I started adding columns to the table.

 

[Rick Rothstein]

Thanks for your response. However, I'm writing results from VBA into the Excel Cells, not assigning values from the Excel Cells to the named variables in VBA.

Did you try shg's suggestion or just make a guess at what you thought it was doing?

 

[Ed Song]

Did you try shg's suggestion or just make a guess at what you thought it was doing?

I'm very sorry that I offended you. Please consider that I'm somewhat new to VBA and thought that the suggestion was a format for inputting rather than outputting.

Yes, I have now tried the suggestion. The suggestion failed to solve the problem, as Excel/VBA still believes that column U is Column X and Column U,V and W are Columns V,W, and X, respectively.

 

[Rick Rothstein]

I'm very sorry that I offended you. Please consider that I'm somewhat new to VBA and thought that the suggestion was a format for inputting rather than outputting.

You did not offend me, it just seemed like your comment was based on your not having tried the code, that's all.

Yes, I have now tried the suggestion. The suggestion failed to solve the problem, as Excel/VBA still believes that column U is Column X and Column U,V and W are Columns V,W, and X, respectively.

To be truthful, I do not think shg intended his code to solve your problem; rather, I think he was showing you how to condense your 10 assignment statements down to one... you have to simply list the variables storing the values you want to assign in the correct order. As for your actual problem... I cannot think of a "natural" way for Excel to screw up the assignments like you have described. It almost seems line you have a macro that you run before the code you posted in this thread, of some kind of event code, screwing up the assignments somehow or even shifting data and/or columns around. Do you have any other code in your project that could be doing something like that?

 

[Ed Song]

The outline of the code goes something like this:

Option Explicit

(Declaration of Variables)

Call A1_Calculations(Output_t1, CapitalStock_t1, Consumption_t1, Investment_t1, Government_t1, TotalDeadWtLoss_t1, Energy_Usage_Consumption_t1, _
Energy_Usage_Production_t1, Energy_Usage_t1, AtmoEmissions_t1, CO2_Level, Temp, Damages_U, Damages_K, Damages_Y, _
Damages_Composite, Survivability_Function_t0)

'Print output
Sheets(1).Range("P4:Y4").Value = _
Array(Output_t1, CapitalStock_t1, Consumption_t1, Investment_t1, Government_t1, TotalDeadWtLoss_t1, _
Energy_Usage_Consumption_t1, Energy_Usage_Production_t1, Energy_Usage_t1, AtmoEmissions_t1)

End Sub

Sub A1_Calculations(Output_t1 As Double, CapitalStock_t1 As Double, Consumption_t1 As Double, Investment_t1 As Double, Government_t1 As Double, _
Energy_Usage_t1, TotalDeadWtLoss_t1 As Double, Energy_Usage_Consumption_t1 As Double, Energy_Usage_Production_t1 As Double, _
AtmoEmissions_t1 As Double, CO2_Level As Double, Temp As Double, Damages_U As Double, _
Damages_K As Double, Damages_Y As Double, Damages_Composite As Double, Survivability_Function_t0 As Double)

(Declaration of Variables)

(Calculations)

End Sub

So far, columns P through Y have been used only once, in the printout statement. There is yet no column used after Y. Thus, it's hard to see why any code would mix up the labeling system. The only explanation I can think of is that I added a couple of columns starting at the U column before the problem occurred. Somehow, this seems to be the explanation. But even this seems perplexing because there was no data written into any cells after T when I added the columns. (Columns after T were empty when I added the new columns.) If you want to see my entire program to see if you can find the problem, I would appreciate that. The program has 385 lines, but I probably can reduce it by more than half by simply using more arrays.

 

[Ed Song]

Problem Solved! When I was reformatting my signature in the subroutine, I accidentally switched the Energy_Usage_t1 and TotalDeadWtLoss_t1 in the ordering so they no longer matched the call ordering.