New answers tagged vba
0
This is an answer based on @PeterT 's answer.
So I kept the main logic of your code :
No merging of cells but center across selection
A 2 dimensional array
Keep Data and formatting separate
Removing magic numbers
And I changed a few things :
removed all .select
I noticed that if I apply "xlCenterAcrossSelection" on the whole row it gives the same result
...
3
There are a few things you can do in your coding to improve both the logic and the organization of your application. Your code does work and kudos for already using a memory-based array to speed up the processing of (partially) filling out your date axis. My suggestion solution below does not use Collections, but creates a two-dimensional array for reasons I'...
2
You are using the Double data type for all variables. I think this is a good thing since it should avoid conversions. But it is probably too large for your purpose.
From the doc:
Double (double-precision floating-point) 8 bytes
-1.79769313486231E308 to -4.94065645841247E-324 for negative values
Source: Data type summary
Perhaps using the single ...
0
I would propose rearranging and simplifying the code down to this:
Sub LaunchTMAP()
'' let user know the workbook is downloading so they dont think Outlook froze
Dim infoBox As New UserForm2
Call infoBox.Show
'' open excel, open the workbook, run the subroutine, then close excel
Dim xl As New Excel.Application
Dim wb As Excel....
2
Use References in place of CreateObject
If you include the reference to the Microsoft Scripting Runtime you can reduce the dim and set of objFSO to
Dim fso As New Scripting.FileSystemObject
to increase readability. Note that i have removed the obj prefix as it is no longer dimmed as an object. Similarly, this can be done with the file as
Dim ...
0
Assuming that you always want the data from a single button press to fall on the same row, you can achieve this functionality with
let ws.[A1].Offset(row-1).Resize(1,10) = _
Array( _
JobID.Text, _
Surveyor.Text, _
DateBox.Text, _
AddressBox.Text, _
CityBox.Text, _
...
3
Whenever you find yourself writing a block of nearly identical code, I recommend creating a common function that simplifies and isolates that functionality. The advantage is that you now have a single location for the logic and can easily make a change that is consistent for each instance you need to use that logic. In your case, you are repeatedly finding ...
Top 50 recent answers are included
