Как сделать приложение в эксель
Автоматизация рутины в Microsoft Excel при помощи VBA
В этом посте я расскажу, что такое VBA и как с ним работать в Microsoft Excel 2007/2010 (для более старых версий изменяется лишь интерфейс — код, скорее всего, будет таким же) для автоматизации различной рутины.
VBA (Visual Basic for Applications) — это упрощенная версия Visual Basic, встроенная в множество продуктов линейки Microsoft Office. Она позволяет писать программы прямо в файле конкретного документа. Вам не требуется устанавливать различные IDE — всё, включая отладчик, уже есть в Excel.
Еще при помощи Visual Studio Tools for Office можно писать макросы на C# и также встраивать их. Спасибо, FireStorm.
Сразу скажу — писать на других языках (C++/Delphi/PHP) также возможно, но требуется научится читать, изменять и писать файлы офиса — встраивать в документы не получится. А интерфейсы Microsoft работают через COM. Чтобы вы поняли весь ужас, вот Hello World с использованием COM.
Поэтому, увы, будем учить Visual Basic.
Чуть-чуть подготовки и постановка задачи
Итак, поехали. Открываем Excel.
Для начала давайте добавим в Ribbon панель «Разработчик». В ней находятся кнопки, текстовые поля и пр. элементы для конструирования форм.
Теперь давайте подумаем, на каком примере мы будем изучать VBA. Недавно мне потребовалось красиво оформить прайс-лист, выглядевший, как таблица. Идём в гугл, набираем «прайс-лист» и качаем любой, который оформлен примерно так (не сочтите за рекламу, пожалуйста):
То есть требуется, чтобы было как минимум две группы, по которым можно объединить товары (в нашем случае это будут Тип и Производитель — в таком порядке). Для того, чтобы предложенный мною алгоритм работал корректно, отсортируйте товары так, чтобы товары из одной группы стояли подряд (сначала по Типу, потом по Производителю).
Результат, которого хотим добиться, выглядит примерно так:
Разумеется, если смотреть прайс только на компьютере, то можно добавить фильтры и будет гораздо удобнее искать нужный товар. Однако мы хотим научится кодить и задача вполне подходящая, не так ли?
Кодим
Для начала требуется создать кнопку, при нажатии на которую будет вызываться наша програма. Кнопки находятся в панели «Разработчик» и появляются по кнопке «Вставить». Вам нужен компонент формы «Кнопка». Нажали, поставили на любое место в листе. Далее, если не появилось окно назначения макроса, надо нажать правой кнопкой и выбрать пункт «Назначить макрос». Назовём его FormatPrice. Важно, чтобы перед именем макроса ничего не было — иначе он создастся в отдельном модуле, а не в пространстве имен книги. В этому случае вам будет недоступно быстрое обращение к выделенному листу. Нажимаем кнопку «Новый».
И вот мы в среде разработки VB. Также её можно вызвать из контекстного меню командой «Исходный текст»/«View code».
Перед вами окно с заглушкой процедуры. Можете его развернуть. Код должен выглядеть примерно так:
Напишем Hello World:
Sub FormatPrice()
MsgBox «Hello World!»
End Sub
И запустим либо щелкнув по кнопке (предварительно сняв с неё выделение), либо клавишей F5 прямо из редактора.
Тут, пожалуй, следует отвлечься на небольшой ликбез по поводу синтаксиса VB. Кто его знает — может смело пропустить этот раздел до конца. Основное отличие Visual Basic от Pascal/C/Java в том, что команды разделяются не ;, а переносом строки или двоеточием (:), если очень хочется написать несколько команд в одну строку. Чтобы понять основные правила синтаксиса, приведу абстрактный код.
Примеры синтаксиса
Dim res As sTRING ‘ Регистр в VB не важен. Впрочем, редактор Вас поправит
Dim i As Integer
‘ Цикл всегда состоит из нескольких строк
For i = 1 To 10
res = res + CStr(i) ‘ Конвертация чего угодно в String
If i = 5 Then Exit For
Next i
Dim x As Double
x = Val( «1.234» ) ‘ Парсинг чисел
x = x + 10
MsgBox x
On Error GoTo Err ‘ При ошибке перейти к метке Err
x = 5 / 0
MsgBox «OK!»
GoTo ne
ne:
On Error GoTo 0 ‘ Отключаем обработку ошибок
‘ Циклы бывает, какие захотите
Do While True
Exit Do
Loop ‘While True
Do ‘Until False
Exit Do
Loop Until False
‘ А вот при вызове функций, от которых хотим получить значение, скобки нужны.
‘ Val также умеет возвращать Integer
Select Case LengthSqr(Len( «abc» ), Val( «4» ))
Case 24
MsgBox «0»
Case 25
MsgBox «1»
Case 26
MsgBox «2»
End Select
Грабли-1. При копировании кода из IDE (в английском Excel) есь текст конвертируется в 1252 Latin-1. Поэтому, если хотите сохранить русские комментарии — надо сохранить крокозябры как Latin-1, а потом открыть в 1251.
Грабли-2. Т.к. VB позволяет использовать необъявленные переменные, я всегда в начале кода (перед всеми процедурами) ставлю строчку Option Explicit. Эта директива запрещает интерпретатору заводить переменные самостоятельно.
Грабли-3. Глобальные переменные можно объявлять только до первой функции/процедуры. Локальные — в любом месте процедуры/функции.
Еще немного дополнительных функций, которые могут пригодится: InPos, Mid, Trim, LBound, UBound. Также ответы на все вопросы по поводу работы функций/их параметров можно получить в MSDN.
Надеюсь, что этого Вам хватит, чтобы не пугаться кода и самостоятельно написать какое-нибудь домашнее задание по информатике. По ходу поста я буду ненавязчиво знакомить Вас с новыми конструкциями.
Кодим много и под Excel
В этой части мы уже начнём кодить нечто, что умеет работать с нашими листами в Excel. Для начала создадим отдельный лист с именем result (лист с данными назовём data). Теперь, наверное, нужно этот лист очистить от того, что на нём есть. Также мы «выделим» лист с данными, чтобы каждый раз не писать длинное обращение к массиву с листами.
Sub FormatPrice()
Sheets( «result» ).Cells.Clear
Sheets( «data» ).Activate
End Sub
Работа с диапазонами ячеек
Вся работа в Excel VBA производится с диапазонами ячеек. Они создаются функцией Range и возвращают объект типа Range. У него есть всё необходимое для работы с данными и/или оформлением. Кстати сказать, свойство Cells листа — это тоже Range.
Примеры работы с Range
Sheets( «result» ).Activate
Dim r As Range
Set r = Range( «A1» )
r.Value = «123»
Set r = Range( «A3,A5» )
r.Font.Color = vbRed
r.Value = «456»
Set r = Range( «A6:A7» )
r.Value = «=A1+A3»
Теперь давайте поймем алгоритм работы нашего кода. Итак, у каждой строчки листа data, начиная со второй, есть некоторые данные, которые нас не интересуют (ID, название и цена) и есть две вложенные группы, к которым она принадлежит (тип и производитель). Более того, эти строки отсортированы. Пока мы забудем про пропуски перед началом новой группы — так будет проще. Я предлагаю такой алгоритм:
Для упрощения работы рекомендую определить следующие функции-сокращения:
Function GetCol(Col As Integer ) As String
GetCol = Chr(Asc( «A» ) + Col)
End Function
Далее определим глобальную переменную «текущая строчка»: Dim CurRow As Integer. В начале процедуры её следует сделать равной единице. Еще нам потребуется переменная-«текущая строка в data», массив с именами групп текущей предыдущей строк. Потом можно написать цикл «пока первая ячейка в строке непуста».
Глобальные переменные
Option Explicit ‘ про эту строчку я уже рассказывал
Dim CurRow As Integer
Const GroupsCount As Integer = 2
Const DataCount As Integer = 3
FormatPrice
Sub FormatPrice()
Dim I As Integer ‘ строка в data
CurRow = 1
Dim Groups(1 To GroupsCount) As String
Dim PrGroups(1 To GroupsCount) As String
Теперь надо заполнить массив Groups:
На месте многоточия
И создать заголовки:
На месте многоточия в предыдущем куске
For I2 = 1 To GroupsCount
If Groups(I2) <> PrGroups(I2) Then
Dim I3 As Integer
For I3 = I2 To GroupsCount
AddHeader I3, Groups(I3)
Next I3
Exit For
End If
Next I2
Не забудем про процедуру AddHeader:
Перед FormatPrice
Теперь надо перенести всякую информацию в result
Подогнать столбцы по ширине и выбрать лист result для показа результата
После цикла в конце FormatPrice
Sheets( «Result» ).Activate
Columns.AutoFit
Всё. Можно любоваться первой версией.
Некрасиво, но похоже. Давайте разбираться с форматированием. Сначала изменим процедуру AddHeader:
Осталось только сделать границы. Тут уже нам требуется работать со всеми объединёнными ячейками, иначе бордюр будет только у одной:
Поэтому чуть-чуть меняем код с добавлением стиля границ:
Select Case Ty
Case 1 ‘ Тип
.Font.Bold = True
.Font.Size = 16
.Borders(xlTop).Weight = xlThick
Case 2 ‘ Производитель
.Font.Size = 12
.Borders(xlTop).Weight = xlMedium
End Select
.Borders(xlBottom).Weight = xlMedium ‘ По убыванию: xlThick, xlMedium, xlThin, xlHairline
End With
CurRow = CurRow + 1
End Sub
Осталось лишь добится пропусков перед началом новой группы. Это легко:
В начале FormatPrice
Dim I As Integer ‘ строка в data
CurRow = 0 ‘ чтобы не было пропуска в самом начале
Dim Groups(1 To GroupsCount) As String
В цикле расстановки заголовков
If Groups(I2) <> PrGroups(I2) Then
CurRow = CurRow + 1
Dim I3 As Integer
В точности то, что и хотели.
Надеюсь, что эта статья помогла вам немного освоится с программированием для Excel на VBA. Домашнее задание — добавить заголовки «ID, Название, Цена» в результат. Подсказка: CurRow = 0 CurRow = 1.
Файл можно скачать тут (min.us) или тут (Dropbox). Не забудьте разрешить исполнение макросов. Если кто-нибудь подскажет человеческих файлохостинг, залью туда.
Спасибо за внимание.
Буду рад конструктивной критике в комментариях.
UPD: Перезалил пример на Dropbox и min.us.
UPD2: На самом деле, при вызове процедуры с одним параметром скобки можно поставить. Либо использовать конструкцию Call Foo(«bar», 1, 2, 3) — тут скобки нужны постоянно.
Как из файла EXCEL сделать программу по типу 1С или иных похожих?
Как можно оператор ^ применить к типу double или как по другому сделать задачу
Помогите пожалуйста, в моем коде пишет, что «оператор ^ невозможно применить к типу double». Как.
Нужно сделать программу типа Excel, нужен исходники или какой нибудь материал по этому
Нужно сделать программу типа Excel, нужен исходники или какой нибудь материал по этому.:-[
Выражение должно относиться к целочисленному типу или типу перечисления без области видимости
Добрый день,столкнулся с проблемой «Выражение должно относиться к целочисленному типу или типу.
Нет, их нужно будет самому прописывать в запросах.
Выражение должно относиться к целочисленному типу или типу перечисления без области видимости
Необходимо реализовать класс Factory, который представляет из себя строку символов и предусмотреть.
Выражение должно относиться к целочисленному типу или типу перечисления без области видимости
#include #include using namespace std; int main() < int N; double Y = 0;.
Как объединить одинаковые dataGridView1 и dataGridView2 или как сделать один excel документ
Привет форумчане! на форме есть dataGridView1 и dataGridView2 у них одинаковое количество.
Table of Contents:
кредит: Сири Стаффорд / Digital Vision / Getty Images
Шаг 1
Загрузите и установите компилятор XCell, используя ссылку в разделе «Ресурсы».
Шаг 2
Запустите Microsoft Excel и откройте электронную таблицу, которую вы хотите преобразовать в отдельный EXE-файл.
Шаг 3
Нажмите на жемчужину Office в верхнем левом углу окна. Нажмите «Параметры Excel».
Шаг 4
Нажмите ссылку «Центр управления безопасностью» в левой части окна, затем нажмите кнопку «Настройки Центра управления безопасностью».
Шаг 5
Нажмите ссылку «Параметры макроса» в левой части окна, затем установите флажок «Доверять доступ к объектной модели проекта VBA». Нажмите «ОК».
Шаг 6
Нажмите на вкладку «Надстройки» в верхней части окна Excel.
Шаг 7
Откройте меню «DoneEx», затем нажмите «Компилятор XCell» и «Компиляция». Если компилятор XCell еще не зарегистрирован, появится окно. Нажмите «Закрыть».
Шаг 8
Нажмите кнопку «…» рядом с «Целевым путем» и перейдите к папке, в которой вы хотите сохранить преобразованный файл. Нажмите кнопку «Компиляция».
Шаг 9
Нажмите «ОК» и закройте Excel. Найдите преобразованный EXE-файл в том месте, которое вы указали на предыдущем шаге. Программа откроется при двойном щелчке, как стандартное приложение.
Apple TV получит снижение цен, скоро появится отдельное приложение hbo
На сегодняшнем мероприятии Apple Тим Кук объявил, что Apple TV упал с 99 до 69 долларов, что составляет 30 долларов. Также объявлено о выходе отдельного приложения HBO Now на апрель.
Facebook выпускает отдельное приложение для Windows Messenger
Поскольку у вас недостаточно способов доступа к Facebook, компания любезно выпустила отдельное приложение для обмена сообщениями. Это официально доступно
Itunes U доступен как отдельное приложение
Как и приложение «Подкасты» для iOS, iTunes U вышла из iTunes как отдельное приложение. Это дает вам доступ к тысячам лекций из колледжей и университетов и других школ со всей страны.
Трюк Excel 30. Форма для заполнения таблицы Excel (Декабрь 2021).
Создание приложения на основе холста с данными из файла Excel
В этом разделе вы автоматически создадите свое первое приложение на основе холста в Power Apps с помощью данных из таблицы Excel. Вы выберете файл Excel, создадите приложение, а затем запустите созданное приложение. Каждое создаваемое приложение имеет экраны для просмотра списка записей и подробных сведений об отдельных записях, а также для создания и изменения записей. Вы можете быстро создать приложение, использующее данные Excel, а затем настроить его так, как вам требуется.
Файл Excel должен находиться в облачной учетной записи хранения, например OneDrive, Dropbox или Google Диск. В этой статье используется OneDrive для бизнеса.
При отсутствии лицензии на Power Apps можно зарегистрироваться бесплатно.
Посмотрите это видео, чтобы узнать, как создать приложение на основе холста с данными из файла Excel:
Предварительные условия
Для точного выполнения инструкций в этой статье скачайте файл Оценки напольных покрытий в Excel и сохраните его в облачной учетной записи хранения.
Создание приложения
В Начать с данных выберите Другие источники данных.
Выберите свое подключение или выберите Создать соединение, чтобы создать, а затем выберите Создать.
В разделе Выбор файла Excel найдите файл FlooringEstimates.xlsx и выберите его.
В разделе Выбор таблицы щелкните FlooringEstimates, а затем нажмите кнопку Подключить.
Выполнить приложение
Откройте режим предварительного просмотра, нажав клавишу F5 (либо нажав значок воспроизведения в правом верхнем углу).
Измените порядок сортировки, нажав значок сортировки в правом верхнем углу.
Отфильтруйте список, введя или вставив один или несколько символов в поле поиска.
Например, введите или вставьте Honey, чтобы показать единственную запись, для которой эта строка появляется в названии продукта, категории или обзоре.
Щелкните значок «плюс».
Добавьте любые данные, которые вы хотите, затем выберите значок галочки, чтобы сохранить изменения.
Выберите стрелку для записи, которую требуется изменить.
Выберите значок карандаша.
Обновите одно или несколько полей, затем выберите значок галочки, чтобы сохранить изменения.
В качестве альтернативы выберите значок отмены, чтобы отменить изменения.
Удалите какую-то запись:
Выберите стрелку «Далее» для записи, которую требуется удалить.
Выберите значок корзины.
Дальнейшие действия
Настройте экрана обзора по умолчанию в соответствии со своими потребностями. Например, можно отсортировать и отфильтровать список только по названию продукта, а не по категории или обзору.
Как создать программу в «Excel»
Один из наиболее часто задаваемых вопросов задаваемых начинающими пользователями звучит так: «Как создать программу в «Excel» и возможно ли это сделать в принципе?»
Ответ на него не так прост.
Создать полноценную программу или продвинутую игру инструментами «Эксель» практически невозможно.
С другой стороны «Excel» обладает достаточным набором инструментов (активные элементы Activx, ViBA и т.д.), позволяющими создавать достаточно функциональные приложения внутри самих экселевских документах — макросы.
Опытные программисты при помощи макросов написанных на языке VBA даже создают примитивные игры популярные в начале 90-х прошлого столетия: тетрис, змейка, пинг-понг и т.д.
Рассмотрим азы создания программ в VBA Excel.
Самый первый этап создания программы — это написание алгоритма ее работы.
Необходимо определить для себя какие данные будут вноситься пользователем, какие данные будут константой, какой результат должна выдавать программа.
Когда определились с основными принципами работы программы, следует составить блок-схему ее работы, используя условные обозначения:
Второй этап — подготовка программы «Excel» к написанию макросов: включить макросы, отобразить панель разработчика и т.д.
Подробно подготовка к написанию простого макроса описана в статье:
Как написать простой макрос в программе Excel
Третий этап — при помощи языка программирования VBA «оцифровываем» блок схему. Делаем ее понятной для компьютера. То есть пишем сам код на языке программирования VBA.
Некоторые варианты кодов макросов опубликованы на нашем сайте в разделе Макросы и VBA.